Managed Code @ SQL Server – Part 1: Stored Procedures

One of the facilities of the Common Language Runtime (CLR) is hosting. The CLR is implemented as a COM server (with a known interface) than can be instantiated from within any Windows application. In that case, the application is the CLR host. Examples of possible hosts are Internet Explorer, the ISAPI filter of ASP.NET and SQL Server.

If SQL Server is the host, it will be executing managed code. In this case, we can use any .NET language to define the “programmability objects” that we usually have on databases: stored procedures, functions and triggers. In this post i’ll be giving an example with stored procedures, but the process is quite similar for functions and triggers.

Any static method with a return type of void or int can be a “managed stored procedure”. Naturally, its parameters need to be of T-SQL compatible types. To get going, we need to create a class library that somewhere inside has an appropriate static method, put the right imports in and compile it. As you would expect, there is some Visual Studio goodness. To make the work easy, you can create a new project under Database Projects\Microsoft SQL Server\CLR SQL. Then, if you add a new item you’ll see the "Stored Procedure” option. VS will create a class with a static method that is decorated with the SqlProcedure attribute. In this case, the attribute is used only by VS to identify the “managed stored procedures” when deploying the project but with functions and triggers there are some important options on their attributes (SqlTrigger and SqlFunction).

To access the database and execute SQL statements we use the ADO.NET API as in other data access application. The following example ilustrates this scenario.

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 SampleSP(int id)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand cmd = new SqlCommand(
                "select description from CLRSQLSample where id = @id", conn);
            cmd.Parameters.AddWithValue("@id", id);
            conn.Open();
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
    }
};

In this example I’ve created a sample stored procedure that has an integer value as parameter (if you need to handle NULL values, use the ADO.NET SQL data types, like SQLInt32 and SQLDecimal). Besides the common ADO.NET code there are two points to notice:

  • “context connection=true” – this connection string indicates that we want to connect to “self” from within the managed stored procedure.The context connection lets you execute T-SQL statements in the same context (connection) that your code was invoked in the first place by using an “in-proc” provider.
  • SqlContext class – represents an abstraction of the caller’s context. The Pipe property (SqlPipe) gets the pipe object that allows the caller to send result sets, messages, and the results of executing commands back to the client.

Having defined the managed stored procedure, we need to associate it to SQL Server. You can do it easily from VS by selecting the “Deploy” option on the “Build” menu. You can also do it with T-SQL statements.

First we need to configure the server to be “CLR enabled”.

exec sp_configure 'clr enabled', 1
reconfigure

Then, we create the assembly object.

CREATE ASSEMBLY CLRSQLSampleAssembly
FROM 'C:\Users\Luís\Desktop\CLR_SQL\CLR_SQL\bin\Debug\CLRSQLSample.dll'
WITH PERMISSION_SET = SAFE

Even though we’re writing managed code, we can’t use all the functionalities of .NET. The “PERMISSION_SET” option specifies the permission set that we wan’t to grant to the assembly and has impact on the types and members that it can use (more info).

Finally, we create the stored procedure object in SQL Server based on the previously defined static method.

CREATE PROC CLRSQLSampleSP @id int
AS EXTERNAL NAME CLRSQLSampleAssembly.StoredProcedures.SampleSP

If default parameter values  are needed we specify them on the create proc statement and their values will be defined by SQL Server before we get to managed code.

And that’s it! We can execute our managed stored procedure the same way we do with regular ones:

exec CLRSQLSampleSP @id = 1

In the next post I’ll be covering another aspect of using managed code in SQL Server that might be much more useful: user-defined types.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s