Creating Stored Procedures with Managed Code

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

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.