A quick walk-through of CLR Integration with SQL Server

This blog post is inspired by a project I've been working on recently. One of the project requirements is writing all the stored procedures and user-defined functions in CLR integration. Although I don't intent to give you the overview of CLR integration in this article, it's not a bad idea to go over the benefits you get from using CLR integration. Stored procedures and user-defined functions/types/aggregates authored in managed code compiles into native code prior to execution, and you can achieve significant performance increases in scenarios where complex computations or business logics are involved. When you create these CLR objects, you can leverage various functions and services CLR provides for the program execution, such as JIT, thread handling, memory allocating, type conversion etc.

The following step-by-step example will show you the common tasks performed in CLR integration projects.

• Enabling CLR integration in SQL Server
• Create a SQL Server Project in Visual Studio 2005/2008
• Create a managed stored procedure
• Deploy the CLR assembly using Visual Studio
• Execute CLR objects
• Deploy assemblies onto production environment

The Northwind database will be used in the example I created. If you do not have the Northwind database, go ahead and download it here and restore it to your SQL Server 2005/2008 instance.

Enabling CLR integration in SQL Server

CLR integration is disabled by default in the SQL Server 2005/2008. To enable it, connect to the database to which you want to deploy the CLR assembly,  execute the SQL statements below and then restart the SQL server instance.

EXEC sp_configure 'clr enabled', '1'

reconfigure

Create a database project in Visual Studio 2005/2008

Creating a CLR project in Visual Studio 2005/2008 is beyond simple. Go to File --> New Project --> Visual C#, and then select "SQL Server Project". Rename the project to NorthwindOrdersCLR.

Click on OK. You will be prompted to add a database reference.  Go ahead and select the Northwind database. If it is not already listed in the dialog, add the connection to the Northwind database.

Click on Ok. The SQL Server project should be created by Visual Studio.

Create a managed stored procedure

Before we go any further, let's spend a few minutes going over the task we try to implement using the CLR integration. Suppose a business partner of Northwind is placing orders in raw XML messages and the order data needs to be parsed from the XML messages and then be populate into the Northwind..Orders table .

Let's go back to the SQL Server project we just created. Right click the project file NorthwindOrdersCLR, select Add New Item, choose Stored Procedure from the "Add new item" dialog, rename the stored procedure to ParseOrders.cs and then hit OK.

Visual Studio generates the stub for the stored procedure for you automatically.

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 ParseOrders()

    {

        // Put your code here

    }

};

In CLR, stored procedures are implemented as public static methods of a .NET class. The static methods can either be declared as void, or return an integer value, and the static methods must be marked with the "SqlProcedure" attribute, which is under the Microsoft.SqlServer.Server namespace.

In order to parse the XML message, we need to reference the System.Xml namesapce. Also, we are going to pass a string parameter to to our stored procedure.  The string will be the raw message of orders from the Northwind's business partner. The stored procedure stub should look like this after the tweaks.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Xml;

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]