Creating CLR Functions for SQL Server

When to use the CLR in Microsoft SQL Server
By, Dan Buskirk
May 10, 2007

One of the most exciting new features of MS SQL Server 2005 is its ability to host the .NET Common Language Runtime (CLR). This feature was not, however, designed merely to provide an alternative to Transact SQL (TSQL). In any development project, it is important to use the right tool for the right job. If you want to create a stored procedure that performs standard operations on relational data, then without doubt TSQL is the platform to choose. Since TSQL is designed solely for the purpose of manipulating relational data, it is superb at that job. However, there are many tasks which fall outside of the realm of relational data. It is for these tasks that CLR code might be a wise choice. Such tasks might include writing log information directly to disk or gathering data from a web service.

Requirements for Creating SQL Server Functions and Stored Procedures in a .NET Language.

You must use .NET 2.0 or later. Certain memory management features of the 2.0 CLR were specifically created for use by SQL Server. Previous versions simply won't work.

Starting Our CLR Function for SQL Server

Sorry, we are not going to create HelloWorld.dll. We want to create a function which exploits the power of the CLR to do something TSQL simply cannot, and if we can provide a practical tool in the process, all the better. For starters, we will create a pair of CLR functions which will make regular expressions available within TSQL. Regular expressions invaluable for string pattern matching, and prove useful in many database query tasks.

Regular Expressions in .NET

The primary .NET class of interest to us is the Regex class, which is defined in the System.Text.RegularExpressions namespace. We will use two important members, IsMatch and Replace. Ismatch returns True if the input string matches the regular expression pattern. Replace, as its name suggests, replaces the matching portion of a string with some other string. We will not discuss the regular expression patterns just yet; that will be more fun once our SQL Server function is running.

Creating the Visual Studio Project

If you are using Visual Studio 2005, life is good. Create a new C# class library project. Rename the project "TextFunctions" and the default class file "RegularExpressions". For our first functions, will will only be using two namespaces. System.Text.RegularExpressions is required for regular expressions, and Microsoft.SqlServer.Server is the basic namespace for creating SQL Server CLR code. Visual Studio creates a code file that should look like Figure 1.

1 using Microsoft.SqlServer.Server;

2 using System.Text.RegularExpressions;

4 namespace TextFunctions

5 {

6 public class RegularExpressions

7 {

9 }

10 }

Figure 1.

In my experience, the "TextFunctions" namespace declaration in the C# source code will cause problems later on when we try use then functions in our assembly within SQL Server. Delete this part of the code.

Now we will create our first function. To create functions for SQL Server, the C# compiler needs to know what we are up to. Functions destined to become SQL Server functions must therefore be marked with the code attribute

[Microsoft.SqlServer.Server.SqlFunction()]

The actual code for our first C# function, called RegExmatch(), is very simple. We instantiate the Regex class, using the pattern expression as an argument to the RegEx constructor. This is the regular expression that ultimately will be passed as an argument by the code calling our function. If the expression contains a syntax error, a runtime error will be thrown. At present, however, we are not including any error handling. After instantiating the Regex object, we call the IsMatch method providing the user's input string as an argument. The return value of IsMatch() is sent back to the calling code. Your code should look something like that in Figure 2.

7 [Microsoft.SqlServer.Server.SqlFunction()]

8 public static bool RegExMatch(string Input, string Pattern)

9 {

10 System.Text.RegularExpressions.

11 Regex RegexInstance = new Regex(Pattern);

12 return RegexInstance.IsMatch(Input);

13 }

Figure 2.

The second function, RegExReplace(), does not introduce any new concepts. Here is the code:

15 [Microsoft.SqlServer.Server.SqlFunction()]

16 public static string RegExReplace(string Input, string Pattern, string Replacement)

17 {

18 Regex RegexInstance = new Regex(Pattern);

19 return RegexInstance.Replace(Input, Replacement);

20 }

Figure 3.

It is worth mentioning, however, that we got off easy this time. The arguments to both functions are strings, and the return types are bool and string. These data types map neatly onto SQL data types. Since our code will be called by TSQL code, we must be able to map a correspondence between the SQL type and the .NET type. Not all types are so cooperative as string.

The completed class should look like this:

1 using Microsoft.SqlServer.Server;

2 using System.Text.RegularExpressions;

5 public class RegularExpressions

6 {

7 [Microsoft.SqlServer.Server.SqlFunction()]

8 public static bool RegExMatch(string Input, string Pattern)

9 {

10 System.Text.RegularExpressions.

11 Regex RegexInstance = new Regex(Pattern);

12 return RegexInstance.IsMatch(Input);

13 }

14 [Microsoft.SqlServer.Server.SqlFunction()]

15 public static string RegExReplace(string Input, string Pattern, string Replacement)

16 {

17 Regex RegexInstance = new Regex(Pattern);

18 return RegexInstance.Replace(Input, Replacement);

19 }

20 }

Figure 4.

You should be able to compile the code with only the usual typographical errors to correct. The libraries required by this project included in the compilation by default, even if you choose to do a commandline compilation rather than use the Visual Studio.

Now comes the fun...