Creating Stored Procedures with Managed Code

Introduction

SQL is by concept a language to manipulate sets of data; therefore Microsoft SQL Server 2005 database system uses T-SQL (Transact SQL) for writing structure code to control the data flow. Prior to Microsoft SQL Server 2005, the only way to write procedures and functions was by using T-SQL, but now, Microsoft SQL Server 2005 provides an integration with the Common Language Runtime (CLR), and consequently the procedures and functions can be written using managed code in any .NET language such as C#. This article is intended to illustrate how to implement a stored procedure with managed code.

The business scenario

As illustrative purposes, we're going to develop a stored procedure to return a list of products by its subcategory using the AdventureWorks database and Production. The product table shipped with the installation of Microsoft SQL Server 2005 (Listing 1). The main steps are to create a class and the underlying business logic to get a list of products, build this class into an assembly, register the assembly in the SQL Server engine, and then create a stored procedure in the database which is an interface to the corresponding method in the class hosted in the assembly.

SELECT *
FROM Production.Product
WHERE ProductSubcategoryID = @ProductSubcategoryID;

Listing 1

Developing the solution

The first step is to create an SQL Server project by opening Visual Studio.NET 2005 and selecting File | New | Project (see Figure 1).

Figure 1

Because a SQL Project is tied to a particular database, in the creation of the project we have to specify a reference to the underlying database. This information is gathered from the database connections that we have created before (see Figure 2).

Figure 2

In order to debug the stored procedure, we have to enable SQL/CLR debugging support for the connection. Visual Studio.NET prompts to enable or not this feature. Click on the Yes button (see Figure 3).

Figure 3

In order to develop the stored procedure with managed code, we have to go to the Solution Explorer, right-click on the StoredProcedureWithManagedCode project, and choose the New Item option (see Figure 4).

Figure 4

In the Add New Item window, select the Stored Procedure item (see Figure 5).

Figure 5

This will create a new class. Replace the class name for ProductSelector and the SQL procedure name for GetProductBySubcategoryID. The template now resembles Listing 2.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class ProductSelector
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetProductBySubcategoryID(int ProductSubcategoryID)
    {
        // Put your code here
    }
}

Listing 2

In order to execute the SQL statement in Listing 1, we have to create a SqlCommand object, set its CommandText property to the SELECT SQL statement in Listing 1, execute and send the result set to the client application (see Listing 3).

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class ProductSelector
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetProductBySubcategoryID(int ProductSubcategoryID)
    {
        SqlCommand objCommand = new SqlCommand();
        objCommand.CommandText = "select * " +
                                  "from Production.Product " +
                                  "where ProductSubcategoryID = @ProductSubcategoryID";
        
        SqlParameter parProductSubcategoryID = objCommand.Parameters.Add("@ProductSubcategoryID", SqlDbType.Int);
        parProductSubcategoryID.Value = ProductSubcategoryID;
        
        SqlContext.Pipe.ExecuteAndSend(objCommand);
    }
}

Listing 3

SqlContext represents the context of the caller and provides access to the Pipe object which is an instance of the SqlPipe class. The Pipe object, as its name implies, is the channel for the communication between the database server and client application enabling executing SQL statements and receiving the result sets through the ExecuteAndSend method.

Now it's time to deploy the solution to the AdventureWorks database by right-clicking on the StoredProcedureWithManagedCode project and selecting the Deploy option (see Figure 6). The deployment process comprises of steps to build the code into an assembly, register the assembly with the database and creating the appropriate objects in the database linked to the method in the assembly.

Figure 6

After deployment, when you open the Microsoft SQL Server Management Studio and navigate to the Programmability | Assemblies node in the AdventureWorks database, you will see the assembly generated by the StoredProcedureWithManagedCode project (see Figure 7).

Figure 7

A stored procedure named GetProductBySubcategoryID is also created. You can see these stored procedures in the Programmability | Stored Procedures node in the AdventureWorks database (see Figure 8).

Figure 8

Now we have to enable the possibility to execute stored procedures in the Microsoft.NET framework (see Listing 4).

EXEC sp_configure 'clr_enabled', 1;
RECONFIGURE;

Listing 4

Finally, we're going to execute the GetProductBySubcategoryID stored procedure (see Listing 5).

DECLARE @ProductSubcategoryID INT;
SET @ProductSubcategoryID = 14;
EXEC dbo.GetProductBySubcategoryID @ProductSubcategoryID;

Listing 5

And the result resembles Figure 9.

Figure 9

Conclusion

In this article, I've illustrated how to create a stored procedure with managed code using C# language. Now you can apply this solution to your own business scenario.