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

Posted by TheBoyan

I have been programming since I was 11 years old (Yes, I'm one of those, for real). First programming language I learned was BASIC, on a Commodore64. Then around high-school it was C and Assembly. Just before and during university days it was C++. I started doing some professional work during this period, part of the reason why I dropped out of uni in my final year. I have never stopped since then. Going through a plethora of technologies ranging from C and Assembly languages, Delphi to C++, C#, Java and so on and so forth... trough 20 (and more, who counts, does it even matter) years of software development. I have not lost even a single bit of my ambition and love for the craft from those first young days, nor the energy...I absolutely love what I do. I like to get my hands into all aspects of software development. Now-a-days I use mainly Microsoft related technologies .NET C#, with a lot of database design/management usage of SQL server, but not in any way limited to that.

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