April 18, 2010

SQL CLR Primer: part 2

Posted in .Net, How-to, SQL at 8:04 pm by Ben

In part 1 of this series, I showed you how to enable CLR on your server and create and load a simple scalar-valued user-defined function.  In part 2, I’m going to show you how to access the database from your CLR code and write a stored procedure.

Go ahead and launch Visual Studio, and either create a new SQL Server Project as described in part 1, or load one you have saved from earlier.  Right click on the project in Solution Explorer and choose Add » Stored Procedure.  You can see the primary difference between a UDF and a stored procedure immediately in the template: the stored procedure method is declared void (in VB, it’s a Sub instead of a Function) – it returns no value.  To create a stored procedure which accepts parameters, simply add those parameters to the method.  As noted in part 1, they should be of types that are defined in SqlTypes namespace.

Of course, a stored procedure is (generally, though less so with CLR) not much use unless it has some impact on the database.  You can create a database connection in a CLR routine exactly as in any .Net code, using the SqlConnection class.  Most often, you’ll want to connect to the database in which the procedure is running, as the user running it, and even as part of the same session and transaction.  You can achieve this using the special connection string “context connection=true”.  You can create other connections as usual, though those will usually require elevated permissions.

None of this is terribly complex, but it is a little abstract, so here’s some absurdly simple sample code to get you going:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void RecordLaterTime(SqlInt32 hours)
{
    using (SqlConnection conn = new SqlConnection(“context connection=true”))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand(“INSERT WhatTimeWillItBe VALUES (@time);”, conn);
        comm.Parameters.Add(“time”, SqlDbType.DateTime);
        comm.Parameters["time"].Value = DateTime.Now.AddHours(hours.Value);
        comm.ExecuteNonQuery();
    }
}

This stored procedure takes an int as a parameter.  It then inserts into a value into a table named WhatTimeWillItBe (which has a single datetime column) a value equal to the current date/time plus the number of hours passed in the parameter.  Clearly this is very simple, and could be done in T-SQL without resort to CLR code, but it demonstrates how the various pieces fit together.

Loading your assembly into the database uses exactly the same CREATE ASSEMBLY call as described in part 1 for a UDF (or for any CLR object).  Once the assembly is loaded, create your stored procedure in SQL as follows:

CREATE PROCEDURE [FunctionName] (Parameters)
AS EXTERNAL NAME AssemblyName.ClassName.MethodName;

But I’m sure that by now you could have guessed that.

This part in the series was quick and easy, but now you know how to create CLR stored procedures as well as methods, and you know how to access the calling database from your CLR code.  Next week I’ll show you another CLR object type and how to set permissions for access levels other than Safe so you can get some real power into your CLR code!

Permalink