.NET CLR in SQL Server 2005

Of recent interest to me has been the ability to deploy user-defined managed functions, stored procedures, and other objects to SQL from within Visual Studio 2005. With the .NET CLR in SQL Server 2005, these managed functions can be written and deployed from within Visual Studio, even as part of an automated build process. In wanting to investigate this further, I’ve decided to write a few articles on how this technique is used and on how it may be useful to you. When I first heard about the ability to (essentially) deploy .NET assemblies into SQL Server and then have SQL Server make managed calls to them for use in queries I was skeptical, thinking this was yet another lost solution embarking upon a life quest for a problem to solve. However, several examples have since been drawn to my attention regarding the usefulness of this solution in exposing features of the richer .NET languages for use in advanced queries.

The first of these areas is that of regular expressions. Earlier this year, I came across an article describing how calls to the .NET 2.0 RegEx classes could be wrapped in a managed function for use in the where clause of a SQL query. My ears (eyes) perked up at this, for the idea of using regular expressions to match data in queries could add a massive amount of power to my SQL toolbox. Even better, because the call would be to the .NET classes, we already know that I’d be able to pre-compile the expressions if needed as that is a feature of that implementation. The rest of this article walks through a simple example for creating a C# Database Project in Visual Studio 2005, deploying a managed function developed in that project, and then writing a query to use that function for RegEx matching within the WHERE clause. For those interested in more information, the original article can be found here: http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx.

Creating a C# Database Project in Visual Studio

First, open Visual Studio, then select File->New->Project. In the New Project dialog, expand the Visual C# node and select Database, then choose SQL Server Project from the list of templates. After the project is created, a dialog named Add Database Reference will ask for the specification of a database connection to work with for this project. This connection is used to deploy the project to SQL Server. Select a connection from the dialog or create a new one by clicking Add New Reference. I’ll assume for the brevity of this article that you either have a SQL database or can create a new one and that you can establish a database connection to it. Next, right-click the new SQL Server Project and select Add->New Item. Choose User-Defined Function from the Add New Item dialog and name it whatever you like; I used the class name RegexUtilities.cs in the test project I wrote for this article. This will add a snippet of code in the class that is a template for the function. Replace the entire contents of the code window with the following:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions {

[Microsoft.SqlServer.Server.SqlFunction]
public static bool Match(SqlChars searchString, SqlString regexPattern) {
Regex regex = new Regex(regexPattern.Value, RegexOptions.Compiled | RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline);
return regex.IsMatch(new string(searchString.Value));
}

}

The code here is a very simple wrapper around the Regex.IsMatch method and is adapted from the source code supplied by David Banister is his original article. I’ve cut a couple of things down for simplicity. Two parameters are passed to the method named Match: the string to search and the regex pattern to use in the match. The first line of the method creates a new compiled Regex object from the supplied pattern. The second line of codes returns a Boolean that indicates whether or not the supplied searchString was matched. First build the project with Ctrl+Shift+B and then right-click over the project in the Solution Explorer pane and select Deploy. This deploys the managed function to the SQL server you specified in the database connection for the project.

Now it’s time to test the function out in a query. First, let’s take the Social Security Number example and check that everything made it across OK. Open SQL Server Management Studio and connect to your database, then open a New Query window. Now type the following into the new query window:

select dbo.Match( N‘123-45-6789’, N‘^d{3}-d{2}-d{4}$’ )

You should receive the following error:

Msg 6263, Level 16, State 1, Line 1

Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.

This is because we have not yet enabled a state within SQL server that is disabled by default for security reasons. CLR code obviously wields great power and, as such, the ability to execute that code within the SQL process gives any would be malicious code a very nice process under which to run, and in an authenticated context. To run our example then, we must first instruct SQL server to allow CLR code to execute. This is achieved by running the following SQL within a query window:

sp_configure ‘clr enabled’, 1
go

reconfigure
go

A confirmation message should indicate that the ‘clr enabled’ option was changed from 0 to 1. It indicates that RECONFIGURE should be run to install the change, hence the second SQL statement in the snippet above. Now we are ready to execute our Social Security Number example again. This time you should receive a single result for (No column name) with a value of 1. This indicates a match and was reported as a literal with no column name because of the structure of the statement we ran. Change part of the statement’s left parameter to be an invalid social security number and re-run the example to see that a 0 is received (indicating false) this time instead.

Now the real purpose of this article was the use of Regex in a WHERE clause and for this pick a table that contains a string column. Now construct a SQL statement similar to the following:

select dbo.Match(C.[First Name], N‘^w{8}$’), C.[First Name]
from C Customer

The statement above displays two columns of data, the first containing the result of the regular expression call, the second containing the customer first name. The regular expression in this example has been simplified to match words of eight characters in length (an oversimplification of the actual syntax, I know, but sufficient for the purposes of this article). Any customers with a first name of eight characters in length will be accompanied by a 1 in the left-hand column, the others with a 0.

That’s it for this article. A quick demonstration of how managed functions in SQL Server 2005 can open the power of the .NET class library for use within SQL.

This entry was posted in .NET Framework, SQL Server and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *