Getting Started with SQL Server CLR

By: Michael Otey


Beginning back with SQL Server® 2005, Microsoft® integrated the .NET runtime into the database engine. This runtime engine is called the SQL Server Common Language Runtime (CLR), and is commonly referred to as SQLCLR. SQL Server CLR enables you to extend the capabilities of the SQL Server database engine with the rich programming environment of .NET Framework. SQL Server CLR provides the ability to perform advanced mathematic, string handling, and other programming capabilities provided by the .NET Framework that go beyond the built-in T-SQL query and data access functions.

The SQL Server CLR also enables you to work with external resources in a more secure and capable way than using xp_cmdshell. The integration of the .NET CLR with SQL Server allows the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages. Microsoft provides some guidelines at Performance of CLR Integration, explaining when the CLR is a good choice for various functions.

In this article, you’ll see how to get started using the SQL Server CLR by working through the creation of a SQL Server CLR stored procedure. For a demonstration of the process, see this video:

Enabling SQL Server Integration

The SQL Server CLR integration feature is off by default, and must be enabled in order to use CLR objects. To enable SQL Server CLR integration, use the sp_configure command with the ‘clr enabled’ option as shown in the following listing:

sp_configure ‘clr enabled’, 1

GO

RECONFIGURE

GO

Creating a CLR Stored Procedure

After you enable the SQL Server CLR integration you can begin creating CLR database objects. To create CLR database objects, you must first create a DLL using Visual Studio®. Then, import that DLL into SQL Server as an assembly. Finally, link that assembly to a database object such as a stored procedure or a trigger. The following listing shows a simple CLR stored procedure that returns a message:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void MySQLCLR ()

{

SqlContext.Pipe.Send(“This text is from the sample SQLCLR procedure MySQLCLR\n”);

}

}

If you’re using Visual Studio, you can simply select the Deploy option, which takes care of both creating the SQL Server Assembly as well as creating the target database object. If you’re not using Visual Studio, or if you want to perform the deployment process manually, you need to copy the .NET DLL to a common storage location of your choice. Then, using SQL Server Management Studio you can execute the CREATE ASSEMBLY statement that references the location of the .NET DLL as shown in the following listing:

CREATE ASSEMBLY MySQLCLR

FROM ‘C:\temp\MYSQLCLR.dll’

WITH PERMISSION_SET = SAFE

GO

When the CREATE ASSEMBLY is executed, the .NET DLL is copied into the master database. By default, Assemblies are created with the SAFE permission, which restricts them from accessing external resources. If you want the code to be able to access the file system or the network, but not spawn other processes or threads, you can set the Assembly to EXTERNAL_ACCESS. If you want the Assembly to share memory across SPIDs or spawn threads, it needs to be granted UNSAFE permissions.

After you create the Assembly you can use the CREATE PROCEDURE statement to create the stored procedure. The following code illustrates creating the usp_MySQLCLR stored procedure that uses the MySQLCLR assembly:

CREATE PROCEDURE usp_MySQLCLR

AS

EXTERNAL NAME MySQLCLR.StoredProcedures.MySQLCLR

GO

EXEC usp_MySQLCLR

EXTERNAL NAME clause specifies that the stored procedure MySQLCLR will be created using a SQL Server Assembly. The EXTERNAL NAME statement uses the following syntax to identify the correct class and method to use from the assembly:AssemblyName.ClassName.MethodName. In the previous example, the registered assembly is named MySQLCLR. The class within the assembly is StoredProcedures, and the method within that class that will be executed is MySQLCLR.

After you create the SQL Server CLR stored procedure, you can execute it just like any other T-SQL stored procedure. Figure 1 illustrates how the usp_MySQLCLR stored procedure is being executed.

MySQL CLR

Figure 1 – Executing the SQL Server CLR stored procedure

Leave a Reply