I have been written a number of posts recently taking a look at Microsoft SQL and some items of it's more obtuse feature set – Microsoft SQL CLR support is one of these hidden gems. Whether you want to add SHA256 hashing to SQL or add managed code features to your database functions (MSMQ support?) this will get you there.

Microsoft SQL has a very broad feature set that seems to only grow with time. One of the more powerful things that can be done is integration with managed code. Yes that's right, you can write c# or vb.net method and have it run inside MS SQL as a native SQL function or stored procedure.

Some of my readers may know that i recently changed job roles and started at BMF, as their Technical Lead. I always think that one of the greatest things that changing jobs can deliver is meeting new people. Richard Pendergast, is an example of one of these great new contacts (he’s a DB god and Kentico MVP) – it is Richard who introduced me to this feature of MS SQL, and also some examples of when it can be useful.

I must preface this ability with my previous post on stored procedures and my view that they lack much requirement in your modern developer toolbox, some may consider CLR support fits into the same category – think of this functionality as another "nice to have" in certain times of need. You may think there is absolutely no need for this functionality, and will never use it, but be happy in the knowledge simply that it is "there".

Some ideas to make your think

As much as some people may dread considering adding managed code to SQL, some examples of managed code that can benefit your SQL installation are:

• Regular expression support
• Custom encryption algorithms for stored procedures
• SHA256 support (as MD5 and SHA1 have both been now been shown to be insecure from a collision point of view)
• MSMQ support for SQL

A Quick Example

I'm going to write a class library that has a method that takes an input, a Regular Expression and returns 1 or 0 if it finds it. This way I can run a Regex query against a database table and have it only return the results that match my expression.

Create a new class library (I'll name mine MySqlTools)

Add a new class named RegExTools and replace the source of the class with the below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

namespace MySqlTools
{
    public static partial class RegexTools
    {
        public static readonly RegexOptions Options =
            RegexOptions.IgnorePatternWhitespace |
            RegexOptions.Singleline;

        [SqlFunction]
        public static SqlBoolean IsRegexMatch(SqlChars stringToSearch, 
            SqlString patternToMatch)
        {
            Regex regex = new Regex(patternToMatch.Value, Options);
            return regex.IsMatch(new string(stringToSearch.Value));
        }
    }
}

Compile this library and copy the DLL into an easy to remember folder (for this example I'm putting mine in C:\SqlBinaries)

Now open the database that you want to use this against in SQL management studio and pull up a new query window. What we need to do is turn CLR support on (it’s off by default), let the database know about our binary, and then assign and virtual function to our method so that it can be called from within SQL.

Enter the following into your query window to turn CLR support on:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Now enter the following into your query window to let SQL know about your binary and give it the name “RegExBinary”:

CREATE ASSEMBLY RegExBinary 
from 'C:\SqlBinaries\MySqlTools.dll' 
WITH PERMISSION_SET = SAFE

By default, the assembly is installed with only permission to execute "safe" code. There are three modes that a binary can be setup to run with and they vary in what the binary is allowed to do:

• SAFE: Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop.
• EXTERNAL: Access is allowed to external systems, such as the EventLog, File System, and network. Still no access to unsafe code or interop code.
• UNSAFE: Access is not limited whatsoever.

The security levels are specified when assemblies are added using the SQL shown above by using the WITH PERMISSION syntax.

You can also add associated files to a binary (such as a PDB file for debugging) using the following SQL:

ALTER ASSEMBLY RegExBinary 
ADD FILE FROM 'C:\SqlBinaries\MySqlTools.pdb'

Now, its time to create a virtual function to call our regular expression method in our freshly imported binary. You’ll note that i use the binary name and created above, followed by the fully qualified namespace  and then the name of the method that i want to call in the binary. I then map the inputs and outputs of my method to their associated SQL types:

CREATE FUNCTION RegExIsMatch
(
  @stringToSearch nvarchar(max),
  @patternToMatch nvarchar(max)
)
RETURNS bit
AS EXTERNAL NAME RegExBinary.[MySqlTools.RegexTools].IsRegexMatch

Now that we have imported the DLL, we can easily use this function against our current database. In my example I'm looking for products that have a description that contains the word orange,  i’m sure you can think of a better example dear reader, but its been a long day…

select * from Products 
where dbo.RegExIsMatch(Products.ProdDescription,'^(.*)orange(.*)') = 1