Tuesday , March 19 2024

What is CLR and How to import dll in sql server

CLR is a feature introduced with SQL Server 2005.

Using the CLR (Common Language Runtime), you can perform a number of complex processes that you cannot do with TSQL using the .NET Framework, and you can import the dll into SQL Server and use it at the database level.

You can also write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued) and user-defined aggregate functions in any net.framework language.

NOTE: You cannot perform these operations on Visual Studio 2003.

Let’s clarify the CLR by making an example. First, the CLR must be activated at the instance level.

Then let’s prepare a code with Visual Studio. I have written a program that contains the following code that calculate the free space on the disks. You can use this code. In this article, we will import the dll generated by this code into the sql server and then use this dll to run the script that calculates the free space on the disks with tsql.

I will prepare my application using Visual Studio 2010. First, we’ll open a new SQL CLR project through Visual Studio as follows. But I will show what is difference in Visual Studio 2015 at the end of the article.

We select SQL Server from the Database tab and then select “Visual C # SQL CLR Database Project”. If you want to write your code with Visual Basic, you should select “Visual Basic SQL CLR Database Project”.

We named our application “SqlServerCLRUygulamasi”.

When we click OK gives a warning as follows. This warning implies that if we are developing our application for SQL Server 2005 or SQL Server 2008, we need to have the .NET Framework 3.5 installed on the server on which we have developed the application, and we can change the version of the .NET Framework that the application uses from the properties tab of the project.

From Solution Explorer, you can right-click the project and then click on Properties to set the .net framework as follows.

After clicking OK, we will choose the SQL Server Instance. Click Add New Reference.

In the screen that appears, we write the instance name in the “Server name” section where we will import dll, in the “Connect to database” section, we write the related database name.

When we click Ok, it gives a warning as follows. This warning says that SQL / CLR should be activated.

And it asks if we want to activate SQL / CLR. Click Yes to continue.

You can also enable CLR at instance level with the help of the following script.

After opening the project, you can add the Item you want by clicking Add as below.

We select Class from the above screen and add the following libraries on the page.

In order to avoid getting errors during importing dll into sql server,, dll into sql server, we define class and function as public as above. It is also important that the function is static.

I’m sharing the code below so you can copy it.

Then we build our project as follows.

After building, the dll of our project was created. Click on “SQLServerCLRUygulamasi” in the Solution Explorer on the right, go to the path in ProjectFolder on the Properties tab, copy the file from bin-> Debug, and paste it to the place you want. I copied it to the “C:\Backup” folder.

Then we import the code to the sql server with the help of the following script.

If TRUSTWORTHY is not active in the database, the code above will receive the error as follows.

Msg 10327, Level 14, State 1, Line 9

CREATE ASSEMBLY for assembly ‘SqlServerCLRUygulamasi’ failed because assembly ‘SqlServerCLRUygulamasi’ is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

You can use the following script to enable TRUSTWORTHY in the database. Enabling TRUSTWORTH may cause some risks. I explained these risks in my article “How To Give Restrict Access To dmw and dmf To Logins Without Giving View_Server_State Permission“.

After enabling TRUSTWORTHY in the database as above, I am running ASSEMBLY create script in UNSAFE mode and then creating a stored procedure that will use this DLL as below.

When I run the Stored Procedure as follows, it shows me the free space on disk C as follows.

If you set the ASSEMBLY code as SAFE, you may receive an error as follows when you run the stored procedure.

Msg 6522, Level 16, State 1, Procedure DiskFreeSpace_SP, Line 22

A .NET Framework error occurred during execution of user-defined routine or aggregate “DiskFreeSpace_SP”:

Creating a project and class on Visual Studio 2015 is slightly different. First, we create the project as a SQL Server Database Project.

Then right click on the project and click on Add New Item as below.

We select “SQL CLR C #” under SQL Server as below, and we add Class.

To do the framework setting in Visual Studio 2015, right-click the project and click Properties as follows.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories