How to Create a SQL CLR C# project

In a previous post I showed you how to make HTTP requests from an SQL server Trigger and post data successfully to an API in some cloud server.

In this post am going to show you an alternative way to do the same using SQLCLR (SQL Common Language Runtime) which is a technology for hosting of the Microsoft .NET common language runtime within SQL Server.

In this post we are going to create a stored procedure that can be invoked by a trigger or a function in SQL server and the using the C# HttpWebRequest class make a POST request to our API hosted somewhere in the cloud.

Start by creating a new project in visual studio. Select SQL Server under installed templates and then give your project a name. I have given my project the name CLRAssembly. Click OK to create the project.

Right click on the project from the solution explorer then Add->New Item.

On the window that pops up select SQL CLR C# then class. Give your class a name and click Add.

 

 Now, we have our class CLRExample.cs. Replace the code in our class with the following code.

To generate your assembly file which is a dll file, Right Click  on the project from the Solution Explorer, select publish. Your assembly file will be created and saved in the project directory under the debug/bin folder.

In SQL server this assembly can only be accessed with permission level set to EXTERNAL_ACCESS under the properties of the project as shown below.

 

To sign your assembly for use in SQL server click the Signing button and then create a new key as in the screenshot below. 

 

So far so good, We are done with creating and publishing our assembly file and now its ready for use in SQL server.

To include your assembly in SQL server expand your database and then expand Programmatically->Assemblies. Right click on the Assemblies and click Add Assembly.

From the pop up that appears navigate to the location of your dll file and the click OK. This assembly will be added in to your database. 

 

 Execute the following sql queries to create the stored procedure and Asymmetric key that enables you access your CLRExample methods from sql.

 Double click on the assembly under assemblies and change the permission to External access.

And we are done!!!

Enjoy Coding!