Many developers become sluggish when it comes to learning TSQL stored procedures and deal with functions and triggers. As we, all know the famous saying “If necessity is the mother of invention, then laziness is sometimes its father” then welcome the tricky way of doing what you want in SQL without writing complex TSQL syntax. This method is by using .NET assemblies and calling .NET DLLs inside the SQL Server using SQLCLR.
What is SQLCLR?
SQL Server 2005 introduced the integration of the Common Language Runtime (CLR) component of the .NET Framework for Microsoft Windows. This means that you can write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions, using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. The Microsoft.SqlServer.Server namespace contains a set of new application programming interfaces (APIs) so that managed code can interact with the Microsoft SQL Server environment.
Therefore, SQLCLR is a framework that bridges the environment of the SQL Server database engine with the rich programming environment of .NET. This allows for:
- Extending capabilities of queries beyond T-SQL built-in functions.
- Performing certain operations faster than being done in T-SQL.
- Better interaction with external resources than can be done via xp_cmdshell.
Enabling CLR Integration:
The Common Language Runtime (CLR) integration feature is off by default in Microsoft SQL Server, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration using Transact-SQL, use the “clr enabled” option of the “sp_configure” stored procedure as shown:
sp_configure ‘clr enabled’, 1
GO
RECONFIGURE
GO
Now, let us play with some C#.NET code and do practical stuff based on what introduces above. For this purpose, we are going to create a new assembly in other words a class library in Visual Studio and name it TestSqlClrAssembly. The needed assemblies in the References section of the library are:
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
By default, if you are using Visual Studio 2015 or 2017 these assemblies are already found in the references. These assemblies are not all used but usually these are the ones that SQLCLR integration normally need them.
Now, create a class called TestClass where this class will include a simple method that will return the length of a string provided a parameter for the function. Instead of having this as a stored procedure in SQL and use TSQL syntax to create it and use it to get the input string length, we will simply create a method to do this functionality and call it using SQLCLR later from the class library that includes the TestClass class.
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StringCharCount (string inputString)
{
SqlContext.Pipe.Send(inputString.Length.ToString());
}
As shown above, the declared method should be static since the SQL Server does not instantiate the class TestClass or create any new object from it to call the method. Moreover, the return type must be always void since the result is channeled via the SqlContext.Pipe class. We use the attribute [Microsoft.SqlServer.Server.SqlProcedure] before the method declaration in order to inform the SQLCLR to recognize this method as a stored procedure. As shown below, the method Send in the SqlContext.Pipe class allows you to provide it with other parameters type like SqlDataRecord object and SqlDataReader object in addition to the input of type string, which is used in our example. We chose to use the input of type string in order not complicate things in our example but you can try it using the same technique and methodology of work where the provided object type differs according to what you need in your project.
When we finish writing the method declaration, we build the class library to generate the assembly (dll file) that will be used in SQL for calling this stored procedure. For testing purposes, we will create a testing database and run the below TSQL statement in it (you can use this dll in any target database you’re working on following the same steps and commands)
CREATE ASSEMBLY TestSqlClr
FROM ‘(class library compilation path)\ TestSqlClrAssembly.dll’
WITH PERMISSION_SET = SAFE
GO
The upper statement loads the TestSqlClrAssembly.dll binary file into the testing database and this means that SQL does not reference this dll at its own path but completely loads it into the MDB database file. This implies that anytime you want to do any changes to the class library or modify the code, then you have to rebuild this library again to generate the new dll file and use the same statement above to load the new dll into the database but replacing the CREATE with ALTER. The permission set defined as SAFE since we do not need to access any information or data outside out testing database in addition that we are not using any unmanaged code inside it. Moreover the StringLengh() method can’t be recognized by SQL CLR as a stored procedure, so we have to manually point to this method by creating a procedure that calls it and returns its value. The way of doing this is by following the below statement. Note the same parameter naming in the created SP.
CREATE PROCEDURE spStringLength (@inputString nvarchar(max))
AS
EXTERNAL NAME TestSqlClr.[TestSqlClrAssembly.TestClass].StringLength
GO
Now we are ready for fun by executing the upper SP that will call our created method in the class library, but before doing so do not forget to enable the CLR by executing the commands mentioned at the beginning of this post, which will enable the SQLCLR at the serve side.
EXEC dbo.spStringLength ‘This is my first sql clr assembly run’
The returned value of the upper statement is 37!
We can create much more complex scenarios using the same strategy mentioned in this post, where the created methods to be linked to SPs in the SQL Server. Do not forget that each time you modify the code in the class library; you have to load again the new dll assembly file to the database by altering the assembly command statement. I hope that I can discuss in further posts scenarios that are more complex by using triggers and functions in SQL CLR.
Rami Chalhoub 
Leave a Reply