By: Philip Miseldine

October 6th, 2004

Reader Rating: 9

One of the biggest strengths of SQL Server is its powerful support for the definition of logic inside the database through stored procedures, user-functions, and triggers. This allows developers to create well-recognised and adaptable interfaces from their business logic layers to the database.

Ad-hoc SQL queries written in this layer can easily be broken by small changes to the database. However, the use of a stored procedure means the required functionality and logic can be encapsulated within that procedure, producing far more reliant and transparent access. Permission to execute stored procedures can also be set individually on a per-procedure basis, which allows you to prevent certain users from accessing specific functionality.

In SQL Server 2000, stored procedures are written in the language T-SQL. This is a rich procedural language for variable evaluation, conditional statements, and output assignments, which allow for the manipulation and evaluation of data within our procedures.

SQL Server 2005, however, goes beyond T-SQL to provide us with the full power and breadth of functionality received from the .NET Framework. This is made possible by the hosting of CLR-compliant code within stored procedures and user functions.

SQL Server 2005 Express

The good news is that there's no need to wait until 2005 to experience this next edition of SQL Server. SQL Server 2005 Express Beta 2 is free to download, evaluate, and test. It's a great product that comes packed with almost all of the powerful features of its more powerful peer, the full SQL Server 2005, aptly codenamed "Yukon." The most notable difference between the two is that SQL Server 2005 Express does not ship with a GUI management tool. Though this can make the product hard to administer and test, XM (SQL Server Express Manager) [1] is due out before the year's end to fill this developmental gap.

This article will guide you through the process of building stored procedures that host CLR-code. You'll need to download and install the following components before you'll be able to use any of the code and techniques outlined:

1. Install the .NET Framework 2.0 Beta [2]
   Don't worry, you can use the beta edition of the framework alongside any other versions of the framework you have installed.


2. Install SQL Server 2005 Beta 2 [3]
   If you have any previous versions of SQL Server 2005 installed -- including Yukon betas -- remove these first.


3. Install C# Express 2005 [4]
   While every task we undertake in this article can be done without an IDE tool, when such a powerful IDE is free to download, it's worth doing!


4. Install QA Replacement Application [5]
   This is a great free tool that provides a GUI for your Express databases.

Your First CLR Stored Procedure

With all the software installed, we can now start to create a CLR-hosted stored procedure, or more accurately, a user function.

You can write code for use within SQL Server 2005 in any language that has a compiler that generates verifiable IL (Intermediate Language). Though this article uses C#, you can easily write for SQL Server in VB.NET, J#, or even Eiffel. This is also true of the .NET framework version to which your code is compiled: you aren't restricted to version 2.0 of the framework.

So, how does it work? The first step is to produce a .NET assembly containing classes that hold the methods you wish to call from your SQL Server procedures. This assembly is then registered within SQL Server, and specific procedures are created to link with the specific methods within the assembly. It is important to note that the signatures of both the methods you want to call, and the stored procedures you use to link, must be the same.

Users of the database can then query these new procedures as they would any other form; however, SQL Server executes the .NET method and outputs its results.

This process is outlined on the diagram below, in which the method "HelloWorld" takes a string as a parameter and prefixes it with "Hello" to produce its string output.

So, the workplan for writing such procedures is as follows:

1. Create a method you wish to host within SQL Server 2005.


2. Compile this class to create an assembly.


3. Register this assembly within SQL Server 2005.


4. Create a new function referencing the method you wish to call.

Step 1: Create a Method you Wish to Host Within SQL Server 2005

Open up Visual C# Express 2005, and select New -> Project from the menu. You should be presented with the following dialog:

A Class Library allows us to create an assembly rather than an executable application. Make sure this is selected, and type in a name for the project. Here, I've called it "SQLServerHostTest," but I'm sure you can think of a snappier title!

You'll now have a fresh, clean class within a new assembly, to which you can add the following methods to be hosted:

using System;
using System.Collections.Generic;
using System.Text;

#endregion

namespace SQLServerHostTest
{
 public class HostFunctions
 {
   public static string HelloWorld(string Name)
   {
     //prefix the Name variable with Hello
     return "Hello" + Name;
   }
 }
}

It should be noted that any method you wish to host within SQL Server 2005 has to be marked both public and static (or public shared in VB.NET), as only one instance of the method will be used.

Step 2: Compile to Create an Assembly

We can now compile the class library to create an assembly. Click F6 or click the Build -> Build Solution menu to achieve this. As assemblies are registered within SQL Server 2005 through file locations, you will need to know the exact location of the outputted assembly. To set the output path manually, click the menu "Project -> SQLServerHostTest Properties..," select the Build tab, and enter a path in the "Output Path" text box.

Step 3: Register this Assembly Within SQL Server 2005

While you can manually code the execution of SQL statements in C#, you can also use the QA Replacement Application to connect to your SQL Server and execute statements very easily, get more information on any errors you might receive, and view output results.

The actual command that registers an assembly with SQL Server 2005 is:

CREATE ASSEMBLY [ASSEMBLYNAME] FROM '[ASSEMBLYLOCATION]'

The [ASSEMBLYNAME] can be any name you wish, but it's good practice to prefix these definitions with "asm" in the same way that primary key objects should be prefixed with "pk". The [ASSEMBLYLOCATION] is the full file path of the assembly you wish to reference.

Hence, we need to execute the following SQL statement:

CREATE ASSEMBLY asmHelloWorld FROM 'c:\SqlServerHostTest.dll'

Using QA, enter the details of your database (Note: Your SQL Server is accessed through the YOURCOMPUTERNAME\SQLSERVERINSTANCE notation, rather than just "localhost").

You can now enter the SQL statement and click the Execute Text button to execute the statement on SQL Server 2005.

Note: You might receive an error that reads as follows:

"CREATE ASSEMBLY failed because method 'get_Value' on type 'SQLServerHostTest.Properties.Settings' in safe assembly 'SQLServerHostTest' is storing to a static field. Storing to a static field is not allowed in safe assemblies."

Often, C# Express will add to your library classes that are not compatible with SQL Server hosting. If you receive this error, manually compile your class into an assembly using the following command line:

 csc /out:c:\SqlServerHostTest.dll /t:library Class1.cs

Step 4: Create a new Function that References the Method you Wish to Call

Now that the assembly is registered with SQL Server, we can create a new function to wrap our call to the "HelloWorld" method in the assembly.

The command that achieves this is the standard CREATE FUNCTION command, which will already be familiar to users of SQL Server. However, we now need to add a reference to our assembly at the end of the declaration:

CREATE FUNCTION dbo.clrHelloWorld
(
 @name as nvarchar(200)
)
RETURNS nvarchar(200)

AS EXTERNAL NAME [asmHelloWorld].[SQLServerHostTest.HostFunctions].[HelloWorld]

In Beta 2 of SQL Server 2005, the syntax used to reference an assembly was changed so that instead of using colons, it utilised the more traditional dot notation. The AS EXTERNAL NAME initially takes the name of the assembly we registered (in this case asmHelloWorld), followed by the full class name including Namespaces (in our case, SQLServerHostTest.HostFunctions), and ends it with the method we wish to call: "HelloWorld."

Also, notice how the signature of the function corresponds with the signature of the "HelloWorld" method. The NVARCHAR type is used to relate to our string types.

Test The Procedure

Now that our new function has been created, we can test our progress. As our function returns a string type, we can use a simple Select statement to retrieve our result.

 SELECT dbo.clrHelloWorld('miseldine')

This returns the expected result:

 Hello miseldine

SqlPipes and SqlContexts

The functionality doesn't stop there, however. Introduced to ADO.NET are SqlPipe and SqlContext, which expose the calling database instance for you to query, and provide structured outputs for your procedures.

Let's look at SqlContext first. You can think of SqlContext as the SqlConnection object of the database that's calling your CLR stored procedure. From this, your procedures can query the same database.

The namespace System.Data.SqlServer, where these new objects reside, isn't registered by default. You can add a reference to the assembly "sqlaccess.dll," which you'll find in the bin/ folder of your SQL Server 2005 install.

The following examples use a table that stores the names of students and their final year exam results:

CREATE TABLE tblResults
 (ID INTEGER CONSTRAINT pkKeyMyId PRIMARY KEY,  
 studentName VARCHAR(50),  
 studentMark INTEGER)

Using SqlContext, we can produce a method called getAverageMark that queries the database from which our procedure is called, and returns the average of the marks attained by the students:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServer;
using System.Data.Sql;
using System.Data;

namespace SitePoint.Yukon
{
 public class HostFunctions
 {
   [SqlFunction(DataAccess=DataAccessKind.Read)]
   public static int getAverageMark()
   {
     SqlCommand currentCommand = SqlContext.GetCommand();
     currentCommand.CommandType = CommandType.Text;
     currentCommand.CommandText = "select AVG('studentMark') from tblResults";
     return (int)currentCommand.ExecuteScalar();
   }

 }
}

Similarly, we could access other databases and resources within our procedures, just as any .NET application can. However, by default, SQL Server 2005 does not allow CLR stored procedures to access external resources. When registering the assembly containing methods that access external resources, it is crucial to add WITH PERMISSION_SET = EXTERNAL_ACCESS to the CREATE ASSEMBLY command.

But, how do we return structures from our methods? This is where SqlPipe steps in. It provides a pipeline back to the calling database along which we can send results. Using the SqlPipe object, we can return any type of data that implements the ISqlReader or ISqlRecord interfaces. This means that we can quite easily return any data that contains rows and columns.

The example below grabs a set of records from an external database and pushes these back to our database using the SqlPipe.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServer;
using System.Data.Sql;
using System.Data;

#endregion

namespace SitePoint.Yukon
{
 public class HostFunctions
 {
   [SqlMethod]
   public static void getMarksExternal()
   {
     System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("connectionstring");
     System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select * from students", conn);
     conn.Open();
     System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
           
     //grab our SqlPipe from the current database instance
     SqlPipe pipe = SqlContext.GetPipe();
     pipe.Send(dr);
     conn.Close();

   }
 }
}

Because this is a procedure rather than a function (we aren't returning values, as such), we need to register this with SQL Server 2005 in a slightly different manner to that which we saw before. Whereas the previous example was registered using CREATE FUNCTION, we now need to use CREATE PROCEDURE:

CREATE PROCEDURE clrGetMarksExternal()
AS EXTERNAL NAME [asmStudent].[SitePoint.Yukon.HostFunctions].[getMarksExternal]

To execute it, we just enter the procedure name:

clrGetMarksExternal
GO

Summary

As you can see, SQL Server 2005 opens up a whole range of functionality that's not available through T-SQL alone. The code you can execute is more reliable, scalable, and managed. Also, it enables you to harness the full power of the .NET framework so you can produce clean and extensible methods for data access. And with the SqlPipe, you could return lists of registry strings, active directory results, or log files straight to SQL Server.

The possibilities are almost endless… and this just the beta!

[1] http://blogs.msdn.com/sqlexpress/archive/2004/07/02/172252.aspx
[2] http://download.microsoft.com/download/9/6/6/9666067a-660f-48e0-a755-a83a81036b67/dotnetfx.exe
[3] http://lab.msdn.microsoft.com/express/sql/default.aspx
[4] http://lab.msdn.microsoft.com/express/vcsharp/default.aspx
[5] http://www.tegels.org/qara/