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]
    public static void ParseOrders(string orderXML)
    ...
}

So far so good. Now we don’t need to look at the problem from a database developer’s point of view any more. We are back to what we are good at – programming in .NET. We can write this stored procedure in the way we write .NET clients to access the SQL database: create a database connection, create a SqlCommand with the SQL statement for inserting data, add parameters to the command, parse the XML message, and then call ExecuteNonQuery. In addition, we will benefit from the power of try-catch blocks so we can handle the exceptions in the .NET way.

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]
  public static void ParseOrders(string orderXML) {
    XmlDocument xd = new XmlDocument();
    using (SqlConnection sc = new SqlConnection("context connection=true")) {
      using (
          SqlCommand cmdInsert = new SqlCommand(
              "INSERT INTO Orders (CustomerID, EmployeeID, OrderDate) VALUES (@OrderID, @EmployeeID, @OrderDate)",
              sc)) {
        try {
          xd.LoadXml(orderXML);
          sc.Open();
          XmlNode orderNode = xd.DocumentElement;
          cmdInsert.Parameters.Add("@CustomerID", SqlDbType.NChar, 5).Value =
              xd["CustomerID"].InnerText;
          cmdInsert.Parameters.Add("@EmployeeID", SqlDbType.Int, 4).Value =
              Convert.ToInt32(xd["EmployeeID"].InnerText);
          cmdInsert.Parameters.Add("@OrderDate", SqlDbType.DateTime, 8).Value =
              Convert.ToDateTime(xd["OrderDate"].InnerText);
          cmdInsert.ExecuteNonQuery();
        } catch (XmlException xe) {
          SqlContext.Pipe.Send("Error parsing XML message: " + xe.Message);
        } catch (SqlException ex) {
          SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        } finally {
          sc.Close();
        }
      }
    }
  }
};

Although the code is pretty self-descriptive and you get the idea of exactly what this stored procedure does, I do need to mention a couple of things here.

First, the connection string I used to create the SqlConnection looks different from what we usually do in a traditional .NET application. The problem of internal data access is a fairly common scenario. That is, you wish to access the same server on which your CLR stored procedure or function is executing. One option is to create a connection using SqlConnection, specify a connection string that points to the local server, and open the connection. This requires specifying credentials for logging in. The connection is in a different database session than the stored procedure or function, it may have different SET options, it is in a separate transaction, it does not see your temporary tables, and so on. If your managed stored procedure or function code is executing in the SQL Server process, it is because someone connected to that server and executed a SQL statement to invoke it. You probably want the stored procedure or function to execute in the context of that connection, along with its transaction, SET options etc. This is called the context connection. The context connection lets you execute Transact-SQL statements in the same context that your code was invoked in the first place. In order to obtain the context connection, you must use the “context connection” connection string keyword, as in the code snippet above.

The second thing is that I used SqlContext.Pipe.Send several times in the code. For CLR database objects running in SQL Server, you can send results to the connected pipe using the Send methods of the SqlPipe object. Access the Pipe property of the SqlContext object to obtain the SqlPipe object. The SqlPipe class is conceptually similar to the Response class found in ASP.NET. This is extremely useful when you need to watch the execution steps of CLR objects. We will look at a couple of examples of how this works when we run the stored procedure.

Deploy the CLR assembly using Visual Studio

Now that we have created the managed stored procedure to handle the orders in XML format, let’s deploy it into SQL server to test it out. Deploying a CLR assembly in Visual Studio cannot be easier. All you have to do is right click the NorthwindOrdersCLR project and choose “Deploy”.

You may receive an error message saying “The current database compatibility level does not support managed objects.” This is because the Northwind database was created in SQL Server 2000 and CLR integration only works with SQL Server 2005/2008. We’ll fix this quickly by changing the compatibility level of the database to SQL Server 2005(90). Re-deploy the CLR project after you have changed the compatibility level.

Visual Studio should report “Deploy succeeded” if no errors have occurred. Open SQL Server Management Studio, expand the Northwind database’s Programmability node, and you should see the ParseOrders stored procedure listed under “Stored Procedures” and the NorthwindOrdersCLR assembly under the “Assemblies” node.

Execute CLR objects

CLR objects can be executed the same way native SQL objects are executed. Simply run the following SQL statement to test our ParseOrders.

exec
    ParseOrders '<Order><CustomerID>FRANK</CustomerID><EmployeeID>1</EmployeeID><OrderDate>2008/05/02</OrderDate></Order>'

If you’ve been following my code so far, you should receive the “Query executed successfully” message when you executed the query above. We can verify the data in the Orders table by executing a quick SELECT statement.

SELECT* FROM orders WHERE OrderID = (SELECT MAX(OrderID) FROM Orders)

The new order with CustomerID “FRANK” is shown in the result set.

I mentioned earlier that the SqlPipe.Send method was very useful for tracing the execution of CLR objects. We can demonstrate this by creating an exception and letting it get caught by the “catch” block, where I called the SqlContext.Pipe.Send to print out the exception message. Execute the SQL statement below.

exec
    ParseOrders '<Order><CustomerID>FRANK</CustomerID><EmployeeID>12345</EmployeeID><OrderDate>2008/05/02</OrderDate></Order>'

EmployeeID “12345” doesn’t exist in the Northwind..Employees table, so this statement will violate the foreign key constraint on the EmployeeID column in Northwind..Orders. We expect to see the exception message coming out of the pipe when this query finishes.

Nice and easy!!! The query executed successfully although the insert command failed due to the constraint. The failure was caught by our SqlException catch block and we got a friendly error message.

Deploy CLR assemblies into production environment

It is easy to deploy CLR assemblies with the aid of Visual Studio. But in the production environment, you probably cannot even connect to the SQL server from your Visual Studio. You will need to use the CREATE ASSEMBLY statement in SQL to deploy the assembly. First, copy the compiled DLL file into a path that the SQL Server is able to access. For example, copy the NorthwindOrdersCLR.dll file (located in the bin\debug or bin\release folder under your project folder) to c:\temp on the production SQL server. Open the SQL Management Studio, connect to the production SQL server, and execute the following SQL statement. This will create the CLR assembly in your production database.

CREATE ASSEMBLY NorthwindOrderCLR from 'c:\helloworld.dll' WITH PERMISSION_SET =
    SAFE

The “WITH PERMISSION_SET” option sets the permission the assembly can have when it executes on the SQL server. SAFE is applied by default if not specified otherwise, and SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. Another two possible values for PERMISSION_SET are EXTERNAL_ACCESS and UNSAFE. EXTERNAL_ACCESS enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.
UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code as well. SAFE is highly recommended by Microsoft.

You can download a completed version of the above sample here if you want to run it on your own machine

Posted on Saturday, May 3, 2008 6:38 PM CLR Integration , SQL Server | Back to top

This article is part of the GWB Archives. Original Author: frank wang

Replatforming Guide: Pros, Cons, and Impact

Deciding to replatform is no small feat; it’s like setting sails for new horizons with your digital presence. Weighing the

Cypress vs Selenium: Why Cypress is Better!

Navigating the competitive landscape of web testing tools, Cypress emerges as a noteworthy contender, outshining Selenium with its cutting-edge advantages.

A quick walk-through of CLR Integration with SQL Server

Related Posts

Replatforming Guide: Pros, Cons, and Impact

Cypress vs Selenium: Why Cypress is Better!