This post has already been read 18 times!

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.

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:

SQL CLR ENABLED
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:

C# DLL CLR STORED PROCEDURE
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
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
CREATE ASSEMBLY MySQLCLR
FROM 'D:\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
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.

 

MySQLCLR

Leave a Reply

Post Navigation