Creating C# CLR's For SQL Server
Before we begin the lesson let me touch on breifly what this post addresses.
In today’s lesson I am addressing how to run c# code in a SQL server enviorment.
This is accomplished by something called Common Language Runtimes or CLR’s.
These CLR can come in flavors or Visual basic or C# code, today I will demo the C# flavor.
Because it is C# it gives a wide range of options in terms of the .Net frame work and limitless abilities to bring in packages for an immense level of flexibility.
Today I will be using a sentiment parser called Vader Sharp that will return a sentiment score of a text string.
This will be turned into a SQL server function and will be able to have SQL server data passed as a parameter and a data type of float return which is a number between -1 and 1 representing the sentiment of the text.
Before we begin run the following code to prepare your SQL enviorment for CLR integration.
First Create the correct type of project…
Next add the following dependencies via the nuget package manager….
Tools » Nuget Package Manager » Manage Nuget Packages for Solution…
Next paste this code into the project.
On the visual studio ribbon click build.
Make a note of the build location on your file system, we will need this for the T-SQL code to create the assembly that references the .dll that was just created.
Now that we have the dll built, we can call this function that will run the C# code from SQL server, to do this we must first create the assembly, then create a database object to reference that external assembly. This can be any type of database object ( stored procedure, table value function, scalar function) in this example I am using a scalar function.
As you can see we can now return data using .Net and C# code just like we would a regular SQL Server function!
Some common hangups when dealing with CLR’s are the data type matchings between the CLR and Sql Server. Here is a conversion chart to assist with that.