CLR Based Objects In SQL Server

SQL Server inherently supports only TSQL (Microsoft proprietary extension of SQL) programming language. But with the advent of SQL Server 2005 it became possible to execute and create SQL Server objects using the CLR (Common Language Runtime) of the .Net framework.The .Net framework was natively integrated into SQL Server 2005 and is still being supported in the latest version of SQL Server which is SQL Server 2016 and Microsoft has indicated that it has no intentions to deprecate this feature in any of its future editions.

Advantage of CLR of the .Net framework is that it provides managed code that adds flexibility of services like Code access security, Thread management, Just in time (JIT) compilation and Garbage collection, all of which executes under managed code which is impossible to achieve using native TSQL.Any code that executes under CLR is called as managed code.

So what does .Net framework integration in SQL Server mean for .Net application developers? The biggest advantage is that with CLR hosted in Microsoft SQL Server one can create Stored procedures, Triggers, User-defined functions, User defined data types in managed code using any of the .Net framework languages including C# and VB.Net. This gives the flexibility of creating SQL Server objects without the need to learn TSQL usually needed to create these SQL Server objects. The .Net framework library in many ways is more richer and flexible than TSQL as it offers rich classes and objects that lack in native TSQL.Objects created in .Net framework executes as a managed code which is compiled into native code before execution which significantly enhances code performance, scalability and memory management.

For more in-depth information on CLR you can have a look at the following MSDN link

SQL Server Supported Objects using CLR

Using CLR integration we can create

  • CLR Stored Procedure
  • CLR User Defined Function (Tabled and Scalar Valued)
  • CLR Trigger
  • CLR User defined data types
  • CLR Aggregates

CLR Stored Procedure

In this exercise we will create a CLR Stored procedure that would export a set of records of a table to a text file. But before that we will have to enable CLR integration at the SQL Server instance and set the TRUSTWORTHY ON property of the database where we intend to host our CLR object.CLR integration and Trustworthy property is set to off by default.

Let us first create a database using the following command and insert a few records into the table in the same database from which we intend to export to a text file.

 

CREATE DATABASE CLR_DB

GO

Now create a table in the database and insert a few records to it.

Use CLR_DB

GO

 

Create table tblexport (id int, Value varchar (20))

Insert into tblexport

Values ( 1,’a’), (2,’b’), (3,’c’), (4,’d’)

Set the TRUSTWORTHY of the database to ON discussed earlier.

ALTER DATABASE CLR_DB

SET TRUSTWORTHY ON

GO

 

Once done that enable the CLR access to the instance using the below command.

 

sp_configure ‘clr enabled’, 1

RECONFIGURE

 

Following message would be displayed after the execution of the command

Configuration option ‘clr enabled’ changed from 0 to 1. Run the RECONFIGURE statement to install.

For this exercise we will use VS 2010 but one can use any other latest version of VS.Lets create a new CLR project in it.

clr1

1

Note: If you are using SQL Server 2012 change the framework of the project to V4.0,if using SQL Server 2008R2 change the framework of the project to V3.5 and if using SQL Server 2005 change the framework to V2.0 .

This can be done by Right Clicking project >>Properties

clr2

And change the Target Framework. In this case it would be V3.5

clr3

Right click the project and click Add as shown below.Select the “New Item” option.

clr4

Then select the option Stored Procedure and name the class as SP_Export.cs

clr5

You will be redirected to the following screen.

clr6

Add the following method to the class

public static void SP_Export(SqlString location)

{

using (StreamWriter writer = new StreamWriter (location.ToString()))

{

using (SqlConnection con = new SqlConnection(“context connection=true”))

{

con.Open();

 

SqlCommand cmd = new SqlCommand(“select * from tblexport”, con);

using (SqlDataReader reader = cmd.ExecuteReader())

{

while (reader.Read())

{

writer.WriteLine(reader[0].ToString());

}

}

 

}

}

}

 

Note: Do not forget to add a reference of namespace System. IO to the class that is highlighted in the screenshot above as the code in this exercise needs to read and write to a file.

clr7

Once done build the project and browse to the physical location of the project dll. In this case it is

D:\CLR_Project\CLR_SP\CLR_SP\bin\Debug\ CLR_SP.dll

clr8

clr9

clr10

The target location will contain a DDL file named CLR_SP.dll.

Create an assembly with the name SP_Export in the database created earlier which is CLR_DB using the following code

Use CLR_DB

GO

 

CREATE ASSEMBLY SP_Export

FROM ‘D:\CLR_Project\CLR_SP\CLR_SP\bin\Debug\CLR_SP.dll’

WITH PERMISSION_SET = UNSAFE;

GO

clr11

 

Note: CLR assemblies can be created with different levels of security namely : SAFE,UNSAFE or EXTERNAL_ACCESS with SAFE being the default permission and works very well in almost all the scenarios. UNSAFE permission is usually set for assemblies that are unverifiable or requires access to restricted objects like Windows API’s. EXTERNAL_ACCESS is used in scenarios where the code needs access to external resources such as file drives, network resources, registry etc. etc.

Once the above step is done create a stored procedure named SP_Export which will utilize the created assembly.

CREATE PROCEDURE [dbo].[SP_Export]

(

 

@location NVARCHAR (255)

 

)

AS

EXTERNAL NAME [SP_Export].[StoredProcedures].[SP_Export];

GO

 

clr12

 

In the SQL Query analyzer execute the SP using the following code and browse to the location which was set up as the parameter for the SP. In this case it is D:\CLR_Export\Export.txt

USE [CLR_DB]

GO

 

EXECUTE [SP_Export] ‘D:\CLR_Export\Export.txt’

clr13

 

Verify the file to make sure the intended records were exported to it.
Conclusion:

The intention of this article was to introduce CLR in SQL Server and touch base the methodology by which one can develop a simple CLR database object using Visual Studio.Inspite of the numerous advantages that encompasses CLR it is not feasible or advisable to create and deploy CLR Objects for every trivial SQL task. It would be more beneficial to use them where there is a need to interact with system level objects through SQL Server or directly consume web services or WCF services data into SQL Server or there is a need to read methods in SQL Server from other third party CLR objects.

Thanks for reading..

Sachin Nandanwar, Senior SQL Developer – Avitas Technologies

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s