Using a .NET assembly in SQL Server 2008

ClacSQLServer.zip

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005CLR&intSeq=2156
 
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('((1762.3857*11.0000)+(((1.0000*11.0000)+(1.0000*11.0000)+(1.0000*11.0000))+11.0000))/11.0000')
insert into testformula values('((15212.0000*44.2800)+((498.5244*1848.0700)+(596.5556*2.5112)+(596.5556*0.0000))+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('((15212.0000*44.2800)+((498.5244*1848.0700)+(596.5556*2.5112)+(596.5556*0.0000))+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('((15212.0000*44.2800)+((498.5244*1848.0700)+(596.5556*2.5112)+(596.5556*0.0000))+270478.2000)/44173.8800')
insert into testformula values('((15212.0000*44.2800)+((498.5244*1848.0700)+(596.5556*2.5112)+(596.5556*0.0000))+270478.2000)/44173.8800')
insert into testformula values('((15212.0000*44.2800)+((498.5244*1848.0700)+(596.5556*2.5112)+(596.5556*0.0000))+270478.2000)/44173.8800')
insert into testformula values('((15212.0000*44.2800)+((498.5244*1848.0700)+(596.5556*2.5112)+(596.5556*0.0000))+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')
insert into testformula values('((15212.0000*44.2800)+((498.5244*1848.0700)+(596.5556*2.5112)+(596.5556*0.0000))+270478.2000)/44173.8800')
insert into testformula values('((15212.0000*44.2800)+((498.5244*1848.0700)+(596.5556*2.5112)+(596.5556*0.0000))+270478.2000)/44173.8800')
insert into testformula values('((15212.0000*44.2800)+((498.5244*1848.0700)+(596.5556*2.5112)+(596.5556*0.0000))+270478.2000)/44173.8800')
insert into testformula values('(((1848.0700*498.5244)+(2.5112*596.5556))+(44.2800*12994.0000)+270478.2000)/44173.8800')


sp_configure 'clr enable', 1
GO
RECONFIGURE
GO

CREATE ASSEMBLY ClacSQLServer
AUTHORIZATION dbo
FROM 'C:\ClacSQLServer.dll'
WITH PERMISSION_SET = SAFE
GO

--(800) WITH EXECUTE AS CALLER

CREATE FUNCTION fn_GetFunctionString
(
 @inputString nvarchar(MAX)
)
RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ClacSQLServer].[ClacSQLServer.MathFunction].[Calculate2]

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

SELECT  dbo.[fn_GetFunctionString](ltrim(formula))
FROM testformula





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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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:

?

1 2 3 4

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:

?

1 2 3 4 5

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 onAssemblies 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.

?

1 2 3 4

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:

?

1 2 3 4

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
Hope this helps.
Until next time....Happy programming.