SQLCLR Integration Examples

The following sections illustrate how to write stored procedures and functions using C#. You need Visual Studio .NET 2005 "in order" to compile and build the code, or you can use the .dll files available with the code download for this book.

The following sections also highlight the use of Performance Monitor, Profiler, catalog views, and DMVs to track and trace SQLCLR activity.

Saving XML Data to a File

SQL Server 2005 allows you to read XML data from a file by using the OPENROWSET T-SQL function, but it does not offer a functionality to save XML type data into a disk file. Here's how you implement that by using a C# managed stored procedure:

Note

The clr enabled sp_configure option should be enabled for this example to work. You can run the sp_configure command or use SQL Server Surface Area Configuration tool to enable the clr enabled option.

1.	Start Visual Studio .NET 2005 and create a new C# SQL Server project by selecting File | New | Project and then selecting the Database Project types under Visual C#. Name the project SQLCLRDemo.
2.	When Visual Studio .NET 2005 prompt you to define a SQL Server 2005 database connection where the managed objects is deployed, do so. This connection information is used for deploying and debugging managed objects. You can click the Add New Reference button and specify the database connection or click Cancel. You can later specify or change this connection by selecting Project | Properties and then using the Database tab.
3.	Right-click the SQLCLRDemo project in the Solution Explorer window, select Add | Stored Procedure, and name the file SaveXMLToFile.cs. Then click Add button.
4.	Type the following C# source code for the stored procedure: using System; using System.Data; using System.Data.Sql; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; using System.IO; public partial class StoredProcedures { [SqlProcedure] public static void SaveXMLToFile(SqlXml XMLData, String DestFile, Boolean Append) { StreamWriter writer = new StreamWriter(DestFile, Append); writer.Write(XMLData.Value); writer.Close(); SqlContext.Pipe.Send(String.Format ("XML text successfully saved to file '{0}'", DestFile)); } }; This C# code uses the StreamWriter class from the System.IO namespace to write input XML text to the specified file.
5.	Build the solution by pressing Ctrl+Shift+B. The SqlProcedure attribute with the SaveXMLToFile function indicates that this function will be mapped to a T-SQL stored procedure. This function is present in a class named StoredProcedures. You can change the classname to anything you would like.
6.	Import the previously created assembly into a SQL Server 2005 database and map a T-SQL stored procedure to the SaveXMLToFile method in the assembly. Developers can then deploy the assembly to a SQL Server 2005 database by using the Build | Deploy Solution menu item in the Visual Studio .NET 2005 IDE. This option runs a script that drops any existing stored procedures, triggers, functions, types, and aggregates in the assembly; drops the assembly; re-imports the assembly; and then re-creates the modules defined in the assembly. In addition to importing the binary assembly, the deployment option in Visual Studio 2005 also imports the source code files and a .pdb file that is used by the Visual Studio .NET 2005 debugger. You can look at the sys.assembly_files catalog view to see all the files imported into the database by the Visual Studio .NET 2005 IDE. The other option is to manually run T-SQL statements by using SQL Server Management Studio or using SQLCMD.exe to import the assembly and create the T-SQL modules, mapping them to methods in the assembly. You should use the second approach here.
7.	Start SQL Server Management Studio and launch Query Editor to connect to a SQL Server 2005 instance by using a Windows NT authenticated login or sa. Run the following T-SQL statements (being sure to update the path to assembly file): USE AdventureWorks; GO CREATE ASSEMBLY SQLCLRDemo FROM 'E:\SQLCLRDemo\SQLCLRDemo\bin\Debug\SQLCLRDemo.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS; GO CREATE SCHEMA SqlClr; GO CREATE PROCEDURE SqlClr.uspSaveXMLToFile @XMLData XML, @DestFile NVARCHAR(255), @Append BIT AS EXTERNAL NAME [SQLCLRDemo].[StoredProcedures].[SaveXMLToFile]; GO
8.	Try to invoke the managed stored procedure from step 7. To do so, launch a new Query Editor window and run the following T-SQL commands to save XML type data into a disk file: USE AdventureWorks; GO DECLARE @XMLData xml; SELECT @XMLData = Demographics FROM Sales.Store WHERE CustomerID = 9 EXEC SqlClr.uspSaveXMLToFile @XMLData, N'C:\1.xml', 0; GO These T-SQL statements extract the XML data from a table into a variable that is then passed to the managed stored procedure. The C# code then saves the XML data into a disk file.
9.	After running the statements in step 8, open the c:\1.xml file to see the XML column value saved into the file. Note that invoking a managed C# stored procedure is no different from invoking a T-SQL stored procedure.

Let's look at one more example of executing .NET managed code from within SQL Server. This time, you are going to write two user-defined functions that can be used to encode a string using base64 encoding and decode a base64encoded string to plain text.

Base-64 Encoding and Decoding

In this section you'll continue to use the SQLCLRDemo C# project you created in the previous section. You follow these steps to implement base-64 encoding and decoding functions, using C# and calling them from within a T-SQL batch:

1.	Right-click the SQLCLRDemo project in Visual Studio .NET 2005 and select Add | User Defined Function. Name the class Base64Helper.cs. This class will generate a function named Base64Helper, change that to EncodeToBase64, and add one more function called DecodeFromBase64 in the same class. Write the EncodeToBase64 and DecodeFromBase64 methods, as shown here: using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Text; using System.Security.Cryptography; public partial class UserDefinedFunctions { [SqlFunction] public static SqlString EncodeToBase64(String PlainText) { Byte[] byteData = new Byte[PlainText.Length]; ASCIIEncoding enc = new ASCIIEncoding(); byteData = enc.GetBytes(PlainText); return (SqlString)Convert.ToBase64String(byteData); } [SqlFunction] public static SqlString DecodeFromBase64(String EncodedString) { Byte[] byteData = Convert.FromBase64String(EncodedString); ASCIIEncoding enc = new ASCIIEncoding(); return enc.GetString(byteData); } };
2.	Build the solution by pressing Ctrl+Shift+B and then run the following DDL statement in Management Studio to refresh the assembly in the database: ALTER ASSEMBLY SQLCLRDemo FROM 'E:\SQLCLRDemo\SQLCLRDemo\bin\Debug\SQLCLRDemo.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS; GO
3.	Create the scalar-valued user-defined functions and map them to the CLR methods just created: USE AdventureWorks; GO CREATE FUNCTION SqlClr.ufnEncodeToBase64(@PlainText NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS EXTERNAL NAME [SQLCLRDemo].[UserDefinedFunctions].[EncodeToBase64]; GO CREATE FUNCTION SqlClr.ufnDecodeFromBase64 (@EncodedString NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS EXTERNAL NAME [SQLCLRDemo].[UserDefinedFunctions].[DecodeFromBase64]; GO
4.	Try out the SQLCLR functions from step 3. The following T-SQL statements add a column to the Sales.CreditCard table, use the SqlClr.ufnEncodeToBase64 function to encode the data in the existing column named CardNumber, save the encoded data into the new column, and use the SqlClr.ufnDecodeFromBase64 function to decode the new column data:

ALTER TABLE Sales.CreditCard ADD EncryptedCC VARCHAR(255); GO UPDATE Sales.CreditCard    SET EncryptedCC = SqlClr.ufnEncodeToBase64(CardNumber); GO SELECT * FROM Sales.CreditCard; GO SELECT CardNumber, SqlClr.ufnDecodeFromBase64(EncryptedCC)    FROM Sales.CreditCard; GO ALTER TABLE Sales.CreditCard DROP COLUMN EncryptedCC; GO

SQLCLR Metadata

SQL Server 2005 provides several catalog views that you can use to obtain information about imported CLR assemblies and objects. Table 11.1 shows some of these catalog views.

Table 11.1. SQLCLR Catalog Views

Catalog View	Description
sys.assemblies	This view contains a row for each .NET assembly imported into the database. The columns indicate the assembly permission bucket (safe, external access, or unsafe), the visibility, the CLR name that uniquely identifies the assembly, and the date when the assembly was imported into the database.
sys.assembly_files	This view contains a row for each file that makes up an assembly. At a minimum, it contains one entry per assembly for the .dll file. If you deploy using Visual Studio .NET 2005, you see additional files, including a .pdb debugger file and source code (.cs or .vb) files. The content varbinary column contains the contents of the assembly file.
sys.assembly_modules	This view contains a row for each stored procedure, function, or trigger that is created and associated with a method in an assembly. object_id identifies the stored procedure, trigger, or function; the assembly_class and assembly_method columns identify the exact method in the assembly identified by the assembly_id column that the object maps to.
sys.assembly_references	If an assembly references another assembly, this information is available via the sys.assembly_references catalog view. The view contains only two columns, assembly_id and referenced_assembly_id.
sys.assembly_types	This view contains a row for each user-defined type that is defined by a CLR assembly.

Monitoring SQLCLR Activity

You can use various DMVs, Profiler trace, and Performance Monitor counters to monitor SQLCLR activity. For instance, you can use the sys.dm_os_loaded_modules and sys.dm_os_memory_clerks DMVs as illustrated in the following code to find out about all the .NET DLLs loaded in the SQL Server address space and the amount of memory allocated by the .NET CLR:

SELECT name, description, * FROM sys.dm_os_loaded_modules    WHERE description LIKE '%.NET%'; SELECT * FROM sys.dm_os_memory_clerks    WHERE TYPE LIKE '%CLR%';

Table 11.2 lists some other DMVs that you can access to track SQLCLR activity.

Table 11.2. SQLCLR DMVs

DMV	Description
sys.dm_clr_properties	Provides details such as whether the CLR is initialized, and if it is, indicates the CLR version and the CLR directory.
sys.dm_clr_tasks	Returns a row for every CLR task that is currently running. A T-SQL batch that contains a reference to a CLR routine creates a separate task for execution of all the managed code in that batch. Multiple statements in the batch that require managed code execution use the same CLR task. The CLR task is responsible for maintaining objects and state pertaining to managed code execution, as well as transitions between SQL Server and the CLR.
sys.dm_clr_appdomains	Returns a row for each CLR appdomain in the SQL Server process. As described earlier, appdomain is a .NET Framework CLR construct that is the unit of isolation for an application.
sys.dm_clr_loaded_assemblies	Contains a row for each CLR assembly loaded into memory.

SQL Profiler provides a new event class called CLR that can be used to trace events such as loading a .NET assembly. Here is how you can use Profiler to trace SQLCLR activity:

1.	Start SQL Profiler, connect to a SQL Server 2005 instance, and select "Blank" from the "Use the Template" box.
2.	Select the Event Selections tab, scroll to the bottom of the tab, select "the Assembly Load" event under the CLR event class, and click the Run button to begin tracing.
3.	Execute any CLR procedure or functions, and you should see the Assembly Load event in the Profiler. If this event is not raised in the Profiler, the assembly is already loaded. You can use the sys.dm_clr_loaded_assemblies DMV and the sys.assemblies catalog view to determine whether an assembly is already loaded. You can drop the objects, drop the assembly, re-import the assembly, and run the stored procedure or functions. Alternatively, you can start the Profiler trace and run the SQLCLRDemo.sql script provided with the code download for this book. This script contains the T-SQL code to import an assembly and create and execute CLR procedures and functions. In this case, you should see the assembly load event in the Profiler.

You can use several .NET and SQLCLR Performance Monitor counters to track .NET activity. The SQLServer:CLR or MSSQL$Instance_name:CLR performance objects provide counters such as CLR Execution that you can use to determine total execution time (milliseconds) in the CLR.