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.

Lets Begin!

Before we begin run the following code to prepare your SQL enviorment for CLR integration.

EXEC sp_configure 'clr enabled', 1;  RECONFIGURE WITH OVERRIDE;


First Create the correct type of project…

CLR Project Type

Next add the following dependencies via the nuget package manager….

Tools » Nuget Package Manager » Manage Nuget Packages for Solution…

CLR Project Type

Next paste this code into the project.

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using VaderSharp;

public class Sentiment
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static double ParseSentiment(string input)
        SentimentIntensityAnalyzer analyzer = new SentimentIntensityAnalyzer();
        var results = analyzer.PolarityScores(input);
        return results.Compound;


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.

CREATE ASSEMBLY SentimentParser from 'C:\Users\XXX\source\repos\CLRFunc\CLRFunc\bin\Debug\CLRFunc.dll' WITH PERMISSION_SET = SAFE  

AS EXTERNAL NAME SentimentParser.Sentiment.ParseSentiment; 

Function Results

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.

Ian Fogelman

Ian Fogelman

My name is Ian Fogelman. I like to develop data driven solutions with SQL Server, Python, .NET and predictive analytics.