Geeks With Blogs
Frank Wang's inspirations on .NET
IEnumerable<Inspiration> inspirations = from i in DataContext.Inspirations where i.Sharable == true select i

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.

CreateProject

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.

AddDBRef

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.

CreateSP

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".

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.

image

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.

image

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.

image

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.

image

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


Comments on this post: A quick walk-through of CLR Integration with SQL Server

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
1) Could i use SqlPipe.Send in try condition like print in T-SQL? Are they similar?

2) Please fix link url, now it look that
http://http//76.87.58.250/dotnetinspirations/sourcecode/NorthwindOrdersCLR.zip
Left by Alexey Kucherenko on May 04, 2008 11:03 AM

# A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
Very nice one!

Can u explain little bit about debugging?

Left by sushil on Jun 19, 2008 3:53 AM

# Walk through SQL 2005 Stored Procedure
Requesting Gravatar...
Can you explain how can we use Store procedure for insert update and delete in database through visual C#.Also selcting record from database.
Left by Fahim on Jun 28, 2008 12:10 AM

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
How can i work it out when I need to INSERT the data from the return stored procdures.

For example once I test and deploy the assembly and test the result and it shows the perfect result then I want to try to insert that data into temptable or any table. how I can acheive that.
Thanks
Left by Amin on Oct 08, 2008 9:43 PM

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
I dont have the Database subcategory under c# or the SQL Project Template anywhere in my New Project dialog. Do I just not have the correct version of VS2008 (using Standard w/ SP1).
Left by Sam on Oct 23, 2008 3:27 PM

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
Hi,
Will the stored procedure not execute if i use VS2008 to write the store procedure and use SQLServer2005 to deploy the stored procedure.
Please let me know.

With Regards

Left by LITU on Feb 05, 2009 3:27 AM

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
great article, thanks.
Left by quan on Jan 29, 2010 2:16 AM

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
is it possible to do the same with c++ ?
Left by kiran on Feb 28, 2010 5:26 AM

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
Thanks for this great post. I found it very useful. I have a quick question. Can you please answer? I have created a CLR stored procedure connecting to database in development server. Now I need to deploy this project/procedures to database in production serveer. How do I change the database connection string? Is it configurable? I am not allowed to deploy to production. I have to create an instruction for network/db person to deploy. Thanks in advance.
Left by shankar on May 17, 2010 11:04 PM

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
Many XML documents can be very large. The MAX varchar length is 8000 characters. What do you do when the XML document is larger than 8000 characters?
Left by Tom on Aug 11, 2011 9:43 PM

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
I know this is an old thread but I have a problem. When I'm deploying the assemblies the stored procedures do not get created with a dbo. prefix but with my account name.

Why is this happening?
Left by Mark Tagliaferro on Feb 13, 2012 6:58 AM

# re: A quick walk-through of CLR Integration with SQL Server
Requesting Gravatar...
How can we globalize the assembly so that we can access the assembly in all the databases.
Left by Amol Choudhari on Mar 02, 2012 3:50 AM

Your comment:
 (will show your gravatar)


Copyright © Frank Wang | Powered by: GeeksWithBlogs.net