In SQL Server 2005 and 2008 the Common Language Runtmie (CLR) is tightly integrated with the database engine, allowing you to write stored procedures in any .NET language such as C# and VB.NET. CLR stored procedures have some distinct advantages over traditional stored procedures, and they also have their drawbacks. In the world of application developers who use languages like C# and the world of database developers who use T-SQL, there are different definitions of "maintainability", and ultimately it will come down to what camp most of your development staff are in.
If you have a comfort level or more staff for database maintenance, then traditional stored procedures will fit into your architecture more easily, and CLR stored procedures will augment where specifically needed. However, when used properly CLR stored procedures will be a powerful tool in your development arsenal. The drawbacks to them are that they are deployed very differently (i.e. you can't run a create script in Management Studio and be done with it; you need to either deploy the assembly through Visual Studio to a database, or physically copy the assembly to the database server, and then register it manually in Management Studio, also you do lose some tracing data that you would normally see inside of SQL Profiler. Some of the more appropriate times to use them however are during operations that require high speed text manipulation, cryptography, and remotely accessing resources outside of SQL Server (such as calling a webservice to geocode address data).
Before we begin with creating our first CLR Stored Procedure, we need to enable the CLR in the database that we want to deploy the procedure to; to do this you can reference my previous posting on How to Enable CLR Stored Procedures.
Step One: Createing the CLR Procedure Project
To create the initial project, click on File -> New-> Project; then select Visual C#-> Database-> SQL Server Project
Let's name the project CLRStoredProcedures, and click ok. During the project creation phase, you will be prompted to add a reference to a database for the current project, create one to the AdventureWorks database if you don't already have one, and select that.
Step Two: Add a Stored Procedure to the Project
Next we need to add stored procedure class to the project, where we can add our methods. To do this, right click in the Solution Explorer, select "Add", and then select "Stored Procedure..."
After the item is added to the project you will be looking at a blank stub method in a C# class file, where you can customize the code to do what you need.
Now, let's go ahead and change the code in this file, so we can do something productive with it!
First, we should change the method name to something useful, in this case we can use the name "GetCustomersByTerritoryID". Guessing based on the name, we will need to be passing in an argument, TerritoryID to our procedure, so also add an argument to the function called "TerritoryID" and of type int. Below, is the code for the procedure, you can copy and paste it, and I will explain line by line what is going on.
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetCustomersByTerritoryID(int TerritoryId)
{
SqlPipe pipe = SqlContext.Pipe;
SqlCommand cmd = new SqlCommand("select * from [Sales].[Customer] where [TerritoryID] = @TerritoryID");
cmd.Parameters.AddWithValue("@TerritoryID", TerritoryId);
using (SqlConnection con = new SqlConnection("context connection=true"))
{
try
{
cmd.Connection = con;
con.Open();
pipe.ExecuteAndSend(cmd);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
}
Ok, once we get inside the declaration of the procedure, the first line is SqlPipe pipe = SqlContext.Pipe; this is the mechanism that we have available to us to send any results back to the client. I am going to make some basic assumptions that you have experience working with ASP.NET or data access with C#, and can already see the similarities here. We start off with instantiating a SqlCommand object, giving it a query to execute that is properly parameterized, and opening a database connection. Now, notice here that it does have a very different connection string that we are used to. It does not specify a server, database, username or password... or for that matter anything we normally see in a connection string. That is because we are already connected to the database and are already authenticated, so none of that is necessary. We simply set the "Context Connection=true" flag, and we're set!
The next big difference you see is that we are calling the ExecuteAndSend() function on the pipe object, and passing in the SqlCommand object as an argument; doing this executes the command and sends any subsequent results back to the caller.
Step Three: Build, Deploy, and Execute!
Now that we have the code already laid down, the final step is upon us, we first build the class file by clicking "Build" on the top menu, then choose the option to build it, then "Build" again, and choose deploy. After this says "Deploy Successful" in the status pane, we jump over into Management Studio, open up a query window to the AdventureWorks database, and try executing it
And that's it! We are done with our first CLR Stored procedure, you can call this like you normally would any stored procedure, and better yet, you can take advantage of the .NET framework from inside of SQL Server 2005 and 2008 from languages and platforms other than ASP.NET! ColdFusion, Classic ASP, PHP, all of them can benefit from this integration with no requirements on the application side! Attached to this post is the actual C# class file used in this tutorial.
Posted
Oct 31 2008, 06:25 PM
by
dacrowlah