Creating and linking CLR stored procedures for SQL Server 2005

May 18, 2008 at 10:24 AMAmer Gerzic

SQL Server 2005 has been released for a while now, and most of the new features are well known throughout programming community. Right after the initial release, I downloaded a copy of SQL Server 2005 Express, eager to explore new features. At first, there was a lot of reading and browsing the documentation; then I moved onto converting smaller projects to SQL Server 2005 edition, and finally I decided to move larger projects to my new favorite DBMS. Throughout conversion process, I was poised to utilize the newest feature of SQL Server 2005: CLR Stored Procedures.

Prerequisites

Before we start with a step-by-step tutorial, it is crucial to prepare following software components:

  1. Visual Studio 2005 - however, Visual C# Express would work too
  2. SQL Server 2005 - any edition: Express, Standard, Enterprise
  3. SQL Server Management Studio

All components should be available in free edition from Microsoft's website!

Step 1: Writing CLR Class Library

Writing CLR class library is actually very simple. Fire up Visual Studio to create new project: Under "Visual C# Projects", chose "Windows" and then "Class Library". Obviously, it is possible to write such library in any .NET language. At this point we are creating a .NET class library that is identical to any other managed class library. The output is a .NET assembly (or dynamic link library), that can be used in any .NET application. Once the library is created, we can start implementing stored procedures in .NET. For simplicity, the example code is short and does not implement any useful functionality (at least at this point).

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server; 

namespace SQLCLRLibDemo
{
    public class MyProcClass
    {
        [SqlProcedure]
        public static void Proc1()
        {
        } 

        [SqlProcedure]
        public static void Proc2(string p1)
        {
        } 

        [SqlProcedure]
        public static void Proc3(out SqlInt32 v1)
        {
            v1 = 0;
        }
    }
}

The code presented above demonstrates couple of important points:

  1. We are referencing "Microsoft.SqlServer.Server" - provides reference to SqlPipe and SqlContext objects necessary for interaction with hosting SQL Server;
  2. We are referencing "System.Data.Sql" - provides access to "regular" database objects like SqlConnection or SqlCommand;
  3. We are implementing three static methods - every CLR method that will be mapped to stored procedure must be declared as static;
  4. We are assigning "SqlProcedureAttribute" - used by Visual Studio to "automatically register method as stored procedure. Not used by SQL Server" (MSDN);

At this point we are ready to compile and import the assembly into SQL Server 2005. But before we do so, it would be useful to extend each stored procedure to perform a useful action on data. Here is refined code:

[SqlProcedure]
public static void Proc1()
{
    /* This sample is very similar to MSDN sample */
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT * FROM customer", connection);
        SqlContext.Pipe.ExecuteAndSend(command);
    }
} 

[SqlProcedure]
public static void Proc2(string p1)
{
    /* Same as proc1 except for the parameter */
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand(
              "SELECT * FROM customer WHERE id=@p1", connection);
        command.Parameters.AddWithValue("@p1", p1); 

        SqlContext.Pipe.ExecuteAndSend(command);
    }
} 

[SqlProcedure]
public static void Proc3(out SqlInt32 v1)
{
    v1 = 0; 

    /* 
     * Let's build some logic into it - let's add all sales larger than 2000.
     * In addition, we'll display each record (id and sales) as we add it. 
     * The added value will be returned within the parameter.
     */
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = 
              new SqlCommand("SELECT id, sales FROM customer WHERE sales>2000", 
                             connection); 

        /* Define custom row to be returned */
        SqlDataRecord record = new SqlDataRecord(
                                      new SqlMetaData("ID", SqlDbType.VarChar, 40),
                                      new SqlMetaData("Sales", SqlDbType.Int)); 

        /* Send signal that we are starting to send records to the pipe */
        SqlContext.Pipe.SendResultsStart(record); 

        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            /* Add sales */
            v1 += reader.GetInt32(1); 

            /* Populate row with data */
            record.SetValue(0, reader.GetValue(0));
            record.SetValue(1, reader.GetValue(1)); 

            /* Send this row to the output pipe */
            SqlContext.Pipe.SendResultsRow(record);
        } 

        /* Send signal that we are done sending the result */
        SqlContext.Pipe.SendResultsEnd();
    }
}

At this point we implemented all three stored procedures to preform following:

  1. Proc1 executes select statement and simply returns all selected records. Very similar example can be found in MSDN documentation;
  2. Proc2 executes select statement with a parameter passed to the stored procedure. It simply demonstrates parameter passing;
  3. Proc2 is somewhat more complex and it demonstrates following:
    • Parameter passed to the stored procedure is marked as output parameter. In this way, the procedure is able to return the value to the caller.
    • The procedure is defining custom record type using SqlDataRecord class.
    • The procedure is returning custom defined records as they are retrieved from the database. For this, the procedure is using SqlContext.Pipe.SendResultsRowStart, SqlContext.Pipe.SendResultsRow, and SqlContext.Pipe.SendResultsRowEnd methods.

For more information about different aspects of stored procedure programming using .NET, please refer to MSDN documentation referenced at the end of the article.

Step 2: Linking CLR Class Library with SQL Server 2005

At this point we have created class library that is ready to be linked with SQL Server 2005. In order to link the library, we have to make it accessible to the server. Following code is necessary to successfully install CLR library.

create assembly SQLCLRLibDemo from 'c:\SQLCLRLibDemo.dll'
with permission_set=safe;

As we can see from the code above, the library to be installed is located on the C drive. Once the SQL code is executed, the library is installed. It is important to note the "permission_set" option, which enables the user to set the access rights for the library installed. In other words, it enables/disables the library from executing specific type of the code (as opposed to authorization/authentication). For example, if the option is set to SAFE (as example above), the library is not allowed to access external devices like network or registry. Modifying the permission set, it is possible to allow the library to have full and complete access to the server. For detailed explanation of permission set option, please refer to MSDN documentation.

Once we install the library, it is available to SQL Server objects. However, to start executing the library, we must properly configure SQL Server to be able to execute CLR code. For that, there is a single option that must be enabled:

exec sp_configure @configname = 'clr enabled', @configvalue = 1
reconfigure with override

As we can see from the code above, we are using built-in SQL Server stored procedure to enable CLR option. The statement above must be executed at least once for each server utilizing the library.

Step 3: Creating Stored Procedure

Once the library is installed, it is necessary to create CLR stored procedures. Actually, probably more appropriate term would be to map CLR procedures into database catalog. In order to do so, we will use CREATE PROCEDURE statement as shown below:

create procedure sp_clr_proc1
as 
external name
    [SQLCLRLibDemo].[SQLCLRLibDemo.MyProcClass].[Proc1]
go 

create procedure sp_clr_proc2(
    @p1     nvarchar(255)
    )
as 
external name
    [SQLCLRLibDemo].[SQLCLRLibDemo.MyProcClass].[Proc2]
go 

create procedure sp_clr_proc3(
    @v      int out
    )
as 
external name
    [SQLCLRLibDemo].[SQLCLRLibDemo.MyProcClass].[Proc3]
go

As we can see from the code above, the syntax for mapping a CLR procedure is very similar to creating regular SQL procedures. The only difference is the use of the statement EXTERNAL NAME, which enables the user to perform the mapping by specifying fully qualified name of the method to be mapped. The syntax for mapping the procedure is:

[assembly name].[fully qualified name of the class including nested namespaces].[method name].

Once all procedures are mapped, they can be used just as any other regular stored procedure.

References

Download

Following sample code includes Visual Studio 2005 solution with following:

  1. CLR Class Library;
  2. Sample application utilizing stored procedures - please modify connection string to use;
  3. SQL Script that creates all necessary components to run the demo - simply execute using SQL Server Management Studio;

Download Sample Application (17.25 kb)

Posted in: .NET | C# | SQL Server

Tags: , , ,

Comments (1) -

Computer Course Center in Delhi
Special classes on weekends are reserved only for working professionals, who cannot spend time training on weekdays. Professionals looking to learn new technologies in their fields, will find COSITC to be one of the best institute. Computer Course Center in Delhi
http://www.cositc.com/

Add comment

biuquote
Loading