Using a .NET assembly in SQL Server 2008

Many times you fnd yourself in a situation where you have to do something that seems too complex to implement in TSQL or you just want to use some feature from the .NET framework, or you already have something implemented in an assembly and you want to use it in your stored procedure or function. While TSQL is very powerfull and there are lots of things you can accomplish the answer to the above is to just use a .NET assembly in your TSQL code.

Let’s say, for the sake of this presentation we create the following class:

namespace SqlClr
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.SqlServer.Server;

    public class SqlClrClass
    {
        /// <summary>
        /// Returns a new string for a sql function.
        /// </summary>
        /// <param name="inputString">Original string(just for showing an input parameter).</param>
        /// <returns>Formated string.</returns>
        [SqlProcedure]
        public static string GetFunctionString(string inputString)
        {
            return string.Format("Original string was: {0}", inputString);
        }
    }
}

Notice that we have to import the  Microsoft.SqlServer.Server namespace and we need to add [SqlProcedure] attribute to our method. This is essential for this to work.

Since I’m using VS2010 and .NET Framework 4.0 and SQL Server 2008 R2, which at the time doesn’t support .NET 4 assemblies integration. I have to build the assembly for .NET Framework 3.5. In order to do that you go to the project’s properties and in the Application tab choose .NET Framework 3.5 as the target framework.

Next we to to the SQL Server.

In order to use our assembly we have to enable managed code execution feature
of the SQL Server which is disabled by default. So, to do that we need to
execute the following query:

sp_configure 'clr enable', 1
GO
RECONFIGURE
GO

This will have our assembly execution enabled and we’re ready for our next step.

To register an assembly you need to have owner rights on your database or you should be the local system admin or server admin.

Now we are ready to register our assembly with the following code:

CREATE ASSEMBLY [SqlClr]
AUTHORIZATION dbo -- or your user
FROM '{Your path to the assembly}\SqlClr.dll'
WITH PERMISSION_SET = SAFE
GO

Or you can just go to [your database] -> Programmabiliy right click on Assemblies and choose New Assembly… and choose the specific options from there.

It is very important to notice here that if you are using other assemblies in your code either from the .NET Framework or others you need to add them here also by providing the path to them. For this example we don’t need to do that as we are not referencing other assemblies from our code other that normal.

Once the query is executed or you added the assembly using the New Assembly… window and we verify that the assembly is present in [your database] -> Programmabiliy ->Assemblies we are ready for our next step.

The next step is to create a wrapper function for the method we are going to use from our assembly.

CREATE FUNCTION [dbo].[fn_GetFunctionString](@inputString [nvarchar](MAX))
RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlClr].[SqlClr].[GetFunctionString]

And we are all set to go.

We can now just execute our function for test:

DECLARE @inputStr nvarchar(MAX);
DECLARE @result nvarchar(MAX);
SET @inputStr = 'Hello .NET assembly';
SELECT @result = dbo.[fn_GetFunctionString](@inputStr);

or use it in another stored procedure or whatever we want 🙂

You should get the result: The original string was:

 Hello .NET assembly

Until next time….Happy coding.

Advertisements
Using a .NET assembly in SQL Server 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s