CLR Integration: Bringing functionality and productivity to SQL Server

i

If you’ve been following the recent developments for SQL Server, you’ve probably noticed Microsoft is not only bringing more and more features onto the platform but also increasingly encouraging developers to extend the base functionalities through integration with other programming languages including those outside the core .Net languages of C# and VB.Net.

In SQL2016, it was all R and data science and now with SQL 2017, it’s Python, something more towards general data operations.  According to Microsoft, the plan is to bring more languages onto the platform and at a faster rate which I think is a great idea.

But even before all this and as early as SQL 2005, we had Common Language Runtime (CLR) Integration. This earlier approach to integration was completely different; with R and Python, their scripts were wrapped inside TSQL and executed like and with the TSQL similar to how dynamic SQL works. With CLR, you’re actually creating a DLL (basically a .Net application, just without the .exe) and importing this (and any dependent DLLs, if any) into SQL Server and running this via TSQL. So what you do is write the C#/VB code, compile it and import that DLL into SQL Server and executing it via TSQL. What this gives you is the power of .Net in your TSQL code. TSQL works great with sets but basically useless outside a database. C#/VB.Net is general object oriented and designed to do much, much more.

To give you a quick taste of how things work, I’ll use a simple example to illustrate the steps for implementation, followed by something a bit more advance to show what’s possible. As this is intended to show  what is possible, it should be clear we’re not going to cover all the important issues you’ll need to consider. There are many, further reading is well advised.

The objects you can create from .Net (I’ll use C#) includes the list below. Once created, they are exactly like their TSQL equivalents as far as usage is concerned.

  • CLR Stored Procedures (Procs)
  • CLR Triggers
  • CLR Functions
  • CLR Aggregates

For the simple demo I’ll create a Stored Proc, but even something simple (and handy) like this, zipping some files was only possible as recently as .Net Framework 4. I’ll then create a CLR Function for the more advanced one-it calls a Tableau REST API. I’m using this API simply because I got the codes ready and a matter of reusing it from another project, you could well call any other API. I’ll use something different/more interesting next time 🙂

Many Tools, Many Approaches

Firstly, there are many ways to do this including different software and templates. The Microsoft documents for example doesn’t even use Visual Studio, instead just a text editor for the creation of the DLL. I prefer using the SQL Server Database Project template in Visual Studio as it has more features though a deeper knowledge of SQL integration is required than the other templates/approaches. More importantly, you can debug your C# codes this way. Irrespective of the approach/tools, it’s the same three steps.

Step 1: Create the DLL (Assembly)

I’m using C# in Visual Studio to create the DLL. The two methods below corresponds to  the two procs we’ll be creating (hard coded here for demo-you would obviously use parameters with a try catch block in real life).  Once codes is ready, build the project and ensure the DLL is created in the bin folder. The first method/proc will zip all files in the directory specified, the second to unzip the same files. Note the System.IO.Compression reference-that’s not a supported library and so we’ll also need to import that DLL later together with what we’re building here.