23.1 Introduction to SQL Server CLR Integration
The database developers write the code in the form of procedures and functions. The T-SQL is used to write all the instructions and code. There is a limitation to T-SQL as it does not support object orientation, arrays, collections, loops, or usage of external resources and classes.
CLR environment is used to execute the code written in any .NET programming language. The integration allows user to create objects in any .NET language supported languages. The objects created are known as managed database object.
The following advantages are provided by the CLR integration.
1) Common Development environment: The application developers use the tool Visual Studio for creating database objects and scripts for the client tier.
2) Ability to define data types: The user defined data types can be created by the user. The storage capabilities are expanded through the user defined data types.
3) Better Programming Model: The .NET programming language provides a set of programming constructs which are unavailable with the T-SQL.
23.2 Identifying the need for managed code
User can use the managed code or the T-SQL for implementing the database logic. It is not necessary for the user to create the managed code every time. The conditions when user needs to create the managed code are explained below.
1) When user wants to implement the complicated programming logic for which the function reusability is provided by the .NET base class libraries.
2) Access the external resources as accessing the file system or call to a web service
3) Implementing the CPU intensive functionality for efficiently managing the code.
23.3 Importing and configuring assemblies
User can import a .NET assembly in the SQL Server by using the CREATE ASSEMBLY statement. The syntax for CREATE ASSEMBLY statement is as shown below:
CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { <client_assembly_specifier>
[ ,…n ] }
[ WITH PERMISSION SET =
{ SAFE | EXTERNAL_ACCESS | UNSAFE } ]
Where,
assembly_name is the name of the assembly creates in the SQL Server
AUTHORIZATION owner_name is the name of the user or role
Client_assembly_specifier is the permissions granted to the assembly. It takes the following parameters.
1) SAFE: It is secure permission as the external source cannot access the file
2) EXTERNAL_ACCESS: It enables the .NET code to access the external resources
3) UNSAFE: It enables the .NET code to access any resource inside or outside SQL Server.
To import the assembly in the SQL Server, the following statement is used.
CREATE ASSEMBLY ValidateData ‘C:\ValidateData.dll’ WITH PERMISSION_SET = SAFE
In the above statement, the database engine imports the ValidateData.dll assembly file from the C drive of the system and creates an assembly object.
User can update the assembly to refer to the recent version of the assembly file or to update the permissions. The ALTER ASSEMBLY statement is used for modifying the statement.
ALTER ASSEMBLY ValidateData WITH PERMISSION_SET = UNSAFE
User can delete the assembly using the DROP ASSEMBLY statement. The following statement is used.
DROP ASSEMBLY ValidateData
23.4 Creating managed database objects
When the user has imported the assemblies, create the managed database objects that use the managed code provided in the assembly. SQL Server does not allow user to run the managed code on the server. To enable CLR, use the following statement.
sp_configure CLR_ENABLED, 1;
After executing the above statement, run the following statement.
RECONFIGURE;
While creating the managed database objects, user needs to use the System.Data.SqlClient, System.Data.SqlTypes and Microsoft.SqlServer.Server namespaces present in the .NET class libraries. There are many classes found in the System.Data.SqlClient namespace used to access the database from the managed code.
1) SqlContext: It represents the context under which the assembly is executing. The properties like TriggerContext, Pipe, and WindowsIdentity are provided.
2) SqlPipe: It allows user to send the results or messages directly to the client application. The following code is used to display the message in the screen.
SqlPipe pipe=SqlContext.Pipe;
pipe.Send( “A text message” );
3) SqlTriggerContext: It allows user to access the information of the event that has fired the trigger. The following code explains the property.
SqlTriggerContext tr = SqlContext.GetTriggerContext();
SqlPipe pipe = SqlContext.Pipe;
if ( tr.TriggerAction == TriggerAction.Insert )
pipe.Send ( “Records inserted” );
4) SqlConnection: It allows user to connect to a database before querying the data. The following code is used to demonstrate the object.
SqlConnection con=new SqlConnection (“context connection =true”);
5) SqlCommand: It allows the user to send the commands to the database server. The following code snippet demonstrates the deletion of row.
SqlCommand cmd = new SqlCommand ();
cmd.commandText = “Delete From Student”;
cmd.connection=con;
int rows=cmd.ExecuteNonQuery();
6) SqlDataReader: It allows user to work with the result of a query. The following code is used to read the data.
SqlCommand cmd=new SqlCommand ( “Select studname from student”, con );
SqlPipe pipe= SqlContext.Pipe;
SqlDataReader dr = cmd.ExecuteReader();
while ( dr.Read())
{
Pipe.Send (dr (“studname”) );
}
dr.Close();
In the above code, the ExecuteReader() method executes the SELECT statement. It returns the SqlDataReader object. The dr object points to the beginning of the data in the result set.
Creating Managed Stored Procedure
The stored procedures are useful database code blocks. To create the store procedure using the managed code, the following steps are performed.
1) Create a .NET class implementing the stored procedure. Compile the class to produce the assembly
2) Register the assembly in SQL Server through the CREATE ASSEMBLY statement.
3) Create a stored procedure and connect the procedure with the methods of the assembly.
The syntax for creating the managed stored procedure is as shown below:
CREATE PROCEDURE <Procedure Name>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>
Where,
ProcedureName is the name of the procedure
Assembly Identifier is the name of the imported assembly
Type Name is the name of the class containing the method
Method Name is the name of the method to be executed
The example to demonstrate the stored procedure is as shown below:
Consider a condition where user needs to create a stored procedure that reads the data from the table and writes the data into the file. User needs to create the assembly using the .NET framework. The following code is used to create an assembly as ConvertXML.dll.
using System; using System.Xml; namespace CLRStoredProcedure { public class XMLProc { [Micorsoft.SqlServer.Server.SqlProcedure] { public static void SaveXML ( Sqlxml XmlData, SqlString FileName) { SqlPiper pipe= SqlContext.Pipe; XmlDocument xmlDoc=new XmlDocument(); xmlDoc.LoadXml ( XmlData.value ); xmlDoc.Save ( FileName.Value ); pipe.Send ( “Data saved in the XML File” ); } } }
Follow the steps for creating the managed stored procedure.
1) Execute the following code in the Query Editor window to create the assembly as ConvertXMLAssembly referring to the ConvertXML.dll file.
CREATE ASSEMBLY ConvertXMLAssembly FROM
‘C:\ConvertXML\ConvertXML\bin\Debug\ConvertXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
2) Create the managed stored procedure by executing the stored procedure.
CREATE PROCEUDRE clproc ( @XmlData as XML, @filename as nvarchar(20) )
AS EXTERNAL NAME
ConvertXMLAssembly.[CLRStoredProcedure.XML.Proc].SaveXML
3) Execute the following statement to run the managed procedure.
DECLARE @part xml
SET @part = ( SELECT StudID, StudName FROM Student
FOR XML AUTO, ELEMENTS, ROOT ( ‘StudInfo’ ) )
EXEC clproc @part, ‘C:\StudInfo.Xml’