An online resource for professional ASP.NET developers

Using CLR to access .NET functions in SQL Server

Summary: An example of how we can create a function in .NET and then use them in SQL Server.

According to Microsoft, SQL Server 2005 significantly enhances the database programming model by hosting the Microsoft .NET Framework 2.0 Common Language Runtime (CLR). This enables developers to write procedures, triggers, and functions in any of the CLR languages, particularly Microsoft Visual C# .NET, Microsoft Visual Basic .NET, and Microsoft Visual C++.

How do we implement this functionality?

In order to use CLR we have to:

  1) Write a .NET class with a public function
  2) Compile the class to a DLL
  3) Register the DLL with SQL Server
  4) Create a SQL Server function to access the .NET function

Creating an example function and DLL

To provide a simple example that you can use, we need to first create a new project. In Visual Studio, start a new Class Library project and call it SQLServerCLRTest. Then, create a new Class called CLRFunctions and add the following code:
Public Class CLRFunctions     Public Shared Function HelloWorld(ByVal Name As String) As String         Return ("Hello " & Name)     End Function End Class
You'll notice that we have a simple function (which is Public Shared so that SQL Server can access it) which accepts a name as parameter and returns a simple message saying "Hello" to whichever name is passed in.

Now, we need to create a DLL out of this project so that we can register it with SQL Server. The easiest way to do this, is to simply click the Build->Build Solution menu item in VS which will build the application and create the DLL. As your project will be in debug mode the DLL will usually be found at a location such as:
C:\Documents and Settings\mark.smith\My Documents\Visual Studio 2005\Projects\SQLServerCLRTest\SQLServerCLRTest\bin\Debug\SQLServerCLRTest.dll
Once you've located this DLL we can copy it over to our SQL Server machine or simply make a note of this path if that happens to be the same machine as our development machine.

Turn on CLR functionality

By default, CLR is disabled in SQL Server so to turn it on we need to run this command against our database:
exec sp_configure 'clr enabled',1 reconfigure go
Registering the DLL

In order to use the function we wrote, we first have to register the DLL with SQL Server. To do this we have to create an assembly, assign it a name and point the assembly at the DLL. Using the path to the DLL we created, run the following command against the database:
CREATE ASSEMBLY asmHelloWorld FROM 'C:\SQLServerCLRTest.dll'
Accessing our function from SQL Server

In order to access our .NET function, we need to create a SQL Server function which makes use of the "EXTERNAL NAME" option which informs SQL Server that we will be using a CLR function. The function we will be creating will look like this:
CREATE FUNCTION dbo.clrHelloWorld ( @name as nvarchar(200) ) RETURNS nvarchar(200) AS EXTERNAL NAME asmHelloWorld.[SQLServerCLRTest.CLRFunctions].HelloWorld
There's two things to note about the above function. The first is that we use an nvarchar which will be the equivilant of the .NET string (it will produce an error if you try to use a varchar and the second is the format of the "EXTERNAL NAME" path. From the reading I've done, the format should be:

MyAssemblyName.MyClassName.MyMethodName

However, when I tried this I received an error from SQL Server stating that it couldn't find the type, so in order to get it to work I had to use the format:

MyAssemblyName.[MyAssemblyName.MyClassName].MyMethodName

Calling the function

Now that we've registered our function, we should be able to call it by using the following statement:
SELECT dbo.clrHelloWorld('Mark')
Hopefully, when you run this code you'll get a simple "Hello Mark" returned in your results.

This is obviously just an example to show you how to implement CLR, but to create something useful you could look at creating functions that deal with Regular Expressions, interact with the File System or Registry, send emails or simply access any resources that are located outside of SQL Server.

Posted on 17/05/2007 02:34:28