Like This Blog 0

Added by Peter Avila December 24, 2012

One of the most exciting features of SQL Server is its ability to leverage the power of .Net. With .Net, SQL Server can more efficiently perform procedural tasks that are so inefficient in SQL, or perform non-database tasks, such as sending an HTTP Request when data in a table change.

This post will discuss the benefits of using .Net and show you how to create a .Net stored procedure (also known as a CLR stored procedure, where CLR is the Common Language Runtime used in .Net). This post is about how to set up a CLR stored procedure and not about all the things you can do in .Net. I’m assuming you have some experience using .Net and databases.

Why .Net?

There are two main reasons to use .Net in SQL Server. The first is when you need to use procedural logic instead of declarative logic, and the second is when you need to perform a non-database task.

First Reason: Using Procedural Logic Instead of Declarative Statements
When I ask my students to give me examples of programming languages, the answers are usually C, C++, C#, VB.Net, Java, and others. When we think of programming languages, we usually think of procedural languages such as these. A procedural language is used to tell the computer what you want by telling it how to do it.

Here is an example that uses a procedural approach to displaying the contents of the Employee table:

Open the Employee table
Loop until we reach the end of the Employee table.
 Display each column in the current row
 Move to the next row
End Loop

Now contrast that with and example that uses the declarative SELECT statement in SQL to perform the same task:

SELECT *
FROM Employee

Note that there is no mention in this statement about how data will be retrieved. This statement doesn’t go farther than declaring what we want. The how part is left up to SQL Server to figure out.

Transact SQL (TSQL) is the variety of SQL found in Microsoft SQL Server. TSQL is highly optimized for its declarative statements, like the SELECT statement in the example above, and most data retrieval needs can be performed using the declarative capabilities of TSQL; but, on some occasions, loops, branching, and other procedural approaches are needed. SQL Server is not optimized for the procedural capabilities of TSQL. If you find yourself needing to use cursors or other procedural approaches, consider writing your stored procedure in .Net.

But, before you do, think carefully about how you might use a declarative approach, instead. I’ve seen many developers resort to a procedural approach because that is what they were familiar with and not because it was the best approach. If you can write your stored procedures using declarative statements, instead, they will run faster—and very often much faster.

Second Reason: Performing Non-Database Tasks
Perhaps a more common reason for using .Net is when a stored procedure must perform some non-database task. I will use this second reason as a backdrop for the demo in this post. The demo will step through the creation and “wiring up” of a CLR Procedure that sends an HTTP Request to a server when data in a table change.

The Scenario Behind The Example
AdventureWorks is a company that sells bicycling equipment and supplies. They want to send a notification to a server whenever data in the Employee table change. The notification must include the BusinessEntityID (primary key) of the row that changed.

The Solution
It’s relatively simple to create an HTTP request in .Net. To address this need, a CLR stored procedure that does exactly that will be created. A trigger in the Employee table will call the CLR stored procedure and pass it the BusinessEntityID of the row that changed. The CLR stored procedure will include that BusinessEntityID in the HTTP Request that is sent.

What You Will Need
Our demo uses SQL Server 2012 and Visual Studio 2010, but with only minor modifications to the instructions in this post, you can also use SQL Server 2005, 2008, 2008R2 or 2012. You can also use Visual Studio 2008, 2010, or 2012.

We will use Visual Studio to create the .Net code for the CLR stored procedure and the database will be the AdventureWorks2012 database on SQL Server 2012. Because we will be deploying to a newer version of SQL Server from an older version of Visual Studio, we will encounter a database version issue and the deploy will fail. The demo shows how to work around this common problem.

Step-By-Step Demo

Create the CLR procedure

1. From the Windows Start button, select All Programs, then open the Microsoft Visual Studio 2010 folder and Open Visual Studio 2010.

2. Select New Project.

3. From the Installed Templates region, select Database and then SQL Server. In the main region of the screen, select Visual C# SQL CLR Database Project. We are going to develop our .Net code using C#, but notice that you also have the option to use Visual Basic.Net. Then click the OK button.

4. Since this is a database project, you will need to select a database to which the procedure we are about to create will become attached. After you specify a SQL Server in the Server name textbox, the databases on that server will appear in the drop-down below, the one prompting you to Select or enter a database name. You can see that I’ve selected the AdventureWorks2012 database that resides on the SQL Server 2012 on my laptop. Click the Test Connection button at the bottom if you’d like to verify that the connection is OK. Then click the OK button. If you are asked to enable SQL/CLR debugging, go ahead and answer yes.

5. A new database project that is connected to the database we specified has been created and we can see its components in the Solution Explorer window. We’re going to add a stored procedure to this project. Right-click in the Solution Explorer window, select Add and then Stored Procedure….

6. In the Add New Item dialog that appears, select the Stored Procedure template and click Add. Note that you can create code for different types of database objects; you are not limited to stored procedures.

7. Note the new stored procedure that is listed in Solution Explorer. Right-click on it, select Rename and then type HTTPRequest.cs for the new name and press Enter.

8. In the code window to the left of the Solution Explorer window, change the code to receive a parameter and send and HTTPRequest. Note the [Microsoft.SqlServer.Server.SqlProcedure] attribute that is necessary for a CLR procedure. Note also the SqlString datatype. SqlString is what you need to use for strings that will be processed by, or that come from, SQL Server. It is not the same as a .Net String data type. Since our parameters will be loaded with values in a SQL Server trigger, we will need to use SqlString.

Deploying the CLR Stored Procedure to the Database
We are ready to package up this CLR stored procedure and make it accessible from within the database like any of the database’s other stored procedures. The first step will be to build the project. This will create a .dll containing the CLR stored procedure. The second step will be to deploy the .dll to the database.

1. Select the Build menu and then Build CLRDemo.

2. Verify that the .dll was created in the applications bin\Debug folder.

3. Select the Build menu again but this time select Deploy CLRDemo. If this succeeds, the stored procedure is deployed to the database to which we connected in A.4, above.

Unfortunately, Visual Studio is notorious for issues with deployment of CLR objects. If you are using Visual Studio 2010 to deploy to a SQL Server 2012 database as this demo does, you will likely get an deployment error due to the version of the database. Check the lower left corner of the screen in case you received a “Deploy failed” message. If you are not sure whether the deployment succeeded, you can check the output in Visual Studio. Select the View Menu and then Output. You can also just check the database itself to see if the CLR object appears in SQL Server Management Studio Object Explorer. Two things need to be present, an assembly and a stored procedure:

If you see both of these objects, then your deployment succeeded and you are ready to go below to C. Using the CLR Stored Procedure; otherwise, continue here with step 4.

4. Rather than struggle with the Visual Studio issue, we will do a manual deployment in SQL Server Management Studio. Open a new query window in there with the AdventureWorks2012 database selected and run the following command to create the assembly from the .dll that was built in Visual Studio (substitute the red text in the single quotation marks with the path to the .dll on your machine):

5. Right-click on the Assemblies folder and select Refresh to verify that the new assembly was created successfully.

6. Now run the following command to create a stored procedure in the database that will run the CLR procedure in the assembly you just created:

7. As you did in step 5, above, refresh the Stored Procedures folder to see the new stored procedure created in step 6.

Using the CLR Stored Procedure
We had decided to call the HTTPRequest procedure from triggers in the Employee table so that we can send an HTTP request whenever data in that table change.

1. Run the following command in SQL Server Management Studio to create the two triggers. Note the calls to the new stored procedure in both triggers:

And that’s it! Next time data change in the HumanResources.Employee table of the AdventureWorks2012 database, the CLR Stored Procedure will send an HTTP request to the server that includes the BusinessEntityID from the Employee table.

Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ