Using .net assemblies inside SQL Server: Quick start guide

Are you are an experienced .net programmer and you are too lazy to learn TSQL stored procedures, triggers or functions? Yes? Then read on this quick start guide on using .net assemblies inside SQL Server using SQLCLR.

SQLCLR (or SQL Common Language Runtime) is a technology for hosting of the .NET CLR engine inside SQL Server. This allows managed code, written in any .net language, to be hosted and run in the SQL Server environment. This technology is available in SQL Server 2005 or 2008 and allows you to create Stored Procedures, Triggers (for DML or DDL statements), UDFs, UDTs or even User-defined types, which allows you to create simple or complex data types which can be serialized or deserialized within the Database.
[ad#468×60]
Now, the example. I used Visual C# 2008 and SQLServer 2008 Express x64, but this example should work with 2005 editions as well. Remember, this is just a quick start guide, so the example is very simple.

Then create a new assembly (class library) project in Visual C# and name it tryExceptSQLCLR. Start by adding the following assemblies:

using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;

(we won’t be using all of them, but these are the ones you will normally need for SQLCLR integration)

Now lets start by creating a new Stored Procedure. This (useless) SP will use an input string and return the string length. SP’s are created as static methods, as SQLServer will not instantiate your class. Return type must always be Void, as all the SP result must be channeled via the SqlContext.Pipe class.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void CountStringLength(String inputString)
{
   SqlContext.Pipe.Send(inputString.Length.ToString());
}

SQLCLR recognizes the CountStringLength as a SP using the Microsoft.SqlServer.Server.SqlProcedure attribute. Pipe.Send also allows you to return SqlDataRecord objects or even full SqlDataReader result sets, but for the sake of simplicity, lets just return the string length as a string for now.

Build the assembly and that’s it, our new SP is ready! Now, create a new database in SQL Server and run the following TSQL statement:

CREATE ASSEMBLY tryexceptAssembly 
  FROM  '(path to the compiled assembly)\tryExceptSQLCLR.dll' 
  WITH PERMISSION_SET = SAFE
go

This loads the assembly binary file into the database – this is important: the assembly is not linked/referenced from your hard-disk or GAC, its actually loaded into the MDB file, so every time you compile a new version you have to reload it using the ALTER ASSEMBLY command. Our example only needs the SAFE permission_set, as we are not accessing information outside the database and we are not using any unmanaged code inside.

Few more things to go: SQLServer does not recognize automagically all the SPs inside this assembly – we have to create them all, one by one, with the correct parameters.

CREATE PROCEDURE spCountStringLength (@inputString nvarchar(max))
AS 
EXTERNAL NAME tryexceptAssembly.[tryExceptSQLCLR.Class1].CountStringLength
go

(if you run into trouble, check out this post – MattN has done a lot of trouble shooting for us)
CREATE PROCEDURE is not required every time you reload the assembly into the database using the ALTER ASSEMBLY command – if you change your SP header (e.g. alter the input parameters, method name, etc), SQLServer will only complain when you try to EXEC the SP, so Unit Test your project to prevent this.

And, finally, before we can execute our SP, we must enable CLR in our database, as SQLCLR support is disabled by default. Run the following:

sp_configure 'clr enabled',1 
go 
reconfigure with override 
go

(this enables SQLCLR serverwide)

And voila, our SP is ready to run!

EXEC dbo.spCountStringLength N'The lazy fox... whatever'

… that returns 24, so it works.

Remember, every time you change and build your assembly you must upload it again into the database:

ALTER ASSEMBLY tryexceptAssembly 
  FROM  '(path to the compiled assembly)\tryExceptSQLCLR.dll' 
  WITH PERMISSION_SET = SAFE
go

If you want to publish your SQLCLR assembly in your newly developed product inside a DDL script do the following inside SQL Management Studio

• Open your database (the one with the most recent assembly loaded)
• Expand “Programmability” and “Assemblies”
• In the context menu of your assembly (in this case tryexceptAssembly) choose the option “Script assembly as”->”Create to”->”New editor window”

This will generate the statement needed to load your assembly using DDL commands only, without hard-disk file dependencies. The statement is basically the same as you saw in the beginning, but the entire assembly binary file coded as HEX:

CREATE ASSEMBLY [tryexceptAssembly]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001E4DB94A0000000000000000E00002210B010800000800000006000000000000CE2700000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000007427000057000000004000008003000000000000000000000000000000000000006000000C000000F82600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000D4070000002000000008000000020000000000000000000000000000200000602E72737263000000800300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000B027000000000000480000000200050080200000780600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002001900000001000011281100000A026F1200000A0A1200281300000A6F1400000A2A1E02281500000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000020020000237E00008C020000E002000023537472696E6773000000006C05000008000000235553007405000010000000234755494400000084050000F400000023426C6F620000000000000002000001471502000900000000FA0133001600000100000016000000020000000200000001000000150000000E00000001000000010000000200000000000A00010000000000060045003E000600820070000600990070000600B60070000600D50070000600EE00700006000701700006002201700006003D0170000600750156010600890156010600970170000600B00170000600E001CD013B00F40100000600230203020600430203020A0088026D020A009E026D020A00A9026D020600BA023E000600CC023E000000000001000000000001000100010010001E00250005000100010050200000000096004C000A00010075200000000086185E000F00020000000100640011005E00130019005E00130021005E00130029005E00130031005E00130039005E00130041005E00130049005E00130051005E00180059005E00130061005E00130069005E00130071005E001D0081005E00230089005E000F0091005E000F009900B1022D00A900C1023200B100D2023600A100DB02130009005E000F002000830028002E00330068002E00130053002E001B0053002E00230059002E002B003E002E000B003E002E003B0053002E004B0053002E00530089002E006300B3002E006B00C0002E007300C9002E007B00D2003A0004800000010000000000000000000000000025000000020000000000000000000000010035000000000002000000000000000000000001006102000000000000003C4D6F64756C653E0074727945786365707453514C434C522E646C6C00436C617373310074727945786365707453514C434C52006D73636F726C69620053797374656D004F626A65637400436F756E74537472696E674C656E677468002E63746F7200696E707574537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053716C436F6E746578740053716C50697065006765745F5069706500537472696E67006765745F4C656E67746800496E74333200546F537472696E670053656E640000032000000000004EEF9C17A7B837438681FDDA362695DF0008B77A5C561934E089040001010E03200001042001010E042001010205200101113D042001010804010000000400001251032000080320000E030701081401000F74727945786365707453514C434C5200000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230303900002901002439396364316462642D323265342D343632622D393363392D66356639366564653331303200000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000001E4DB94A0000000002000000600000001427000014090000525344533E76D4B9A768514DB4FF25422CD3EFAD02000000653A5C6C69786F5C74727945786365707453514C434C525C74727945786365707453514C434C525C6F626A5C52656C656173655C74727945786365707453514C434C522E706462009C2700000000000000000000BE270000002000000000000000000000000000000000000000000000B02700000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000280300000000000000000000280334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00488020000010053007400720069006E006700460069006C00650049006E0066006F00000064020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000480010000100460069006C0065004400650073006300720069007000740069006F006E0000000000740072007900450078006300650070007400530051004C0043004C0052000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000048001400010049006E007400650072006E0061006C004E0061006D0065000000740072007900450078006300650070007400530051004C0043004C0052002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300030003900000000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000740072007900450078006300650070007400530051004C0043004C0052002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D00650000000000740072007900450078006300650070007400530051004C0043004C0052000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000D03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE

GO

Next time i’ll expand on this subject with more complex (and useful) examples – triggers in SQLCLR. I’ll write the article when i have some free time or when my PayPal donations reach one million dollars – whichever one comes first. (donate below :) )