As we all know SQL Server 2005 has CLR integrated with it. This means one can write a code in manage application and execute it SQL Server. I read about this since Microsoft has launched SQL Server 2005, however I have never used it practically till last week.
First question came in my mind when I read about CLR integration was why we require CLR in SQL. After searching on internet and reading some good articles I found the reason. As we all know SQL server has performance issue with cursor and looping. When we use recursive functions or a cursor in stored procedures we have to compromise with performance. While integrating CLR we can write the looping and recursive functionalities in C# (or any other language). CLR stored procedures can take advantage of the built-in functionality provided by the classes in the .NET Framework, making it relatively easy to add functionality such as complex mathematical expressions or data encryption. Plus, since CLR stored procedure are compiled rather than interpreted like T-SQL, they can provide a significant performance advantage for code that’s executed multiple times. Here is the link which shows real time performance difference between T-SQL statements and CLR Procedure. The second reason can be (which is my observation) most of the developers are really good at programming however when it comes to deal with SQL sps and user defined functions they are not as smart as in programming.
In this article I will show you how to create a CLR stored procedure and how to use in code.
Open visual studio, click on new project and select SQL Server project from C# or VB projects and name it as LearnCLRIntegration,
Fig – (1) Creating new project.
Right click on on project in solution explorer and select Add New Item and select stored procedure.
Fig – (2) Create New Stored Procedure.
This will generate a new class as shown in code snippiest,
1: using System;
2: using System.Data;
3: using System.Data.SqlClient;
4: using System.Data.SqlTypes;
5: using Microsoft.SqlServer.Server;
6:
7:
8: public partial class StoredProcedures
9: {
10: [Microsoft.SqlServer.Server.SqlProcedure]
11: public static void StoredProcedure1()
12: {
13: // Put your code here
14: }
15: };
Fig – (3) Code Snippiest for new CLR stored procedure.
[Microsoft.SqlServer.Server.SqlProcedure] attributes tells the compiler that the fuction StoredProcedure1() will be the CLR stored procedure. You can have SqlFunction, Sqltrigger, SqlMethod, SqlUserDefinedType.. and more as options. You may have understand what this class is however let me describe it more. In this class we can write different methods as we write in normal class. The attribute on method helps compiler to identify that whether this method is a stored procedure or a function or a user defined type. So if you are creating a trigger you need to set the attribute to [Microsoft.SqlServer.Server.SqlTrigger].
I have created a demo table “Employee” in my database and created a sp to insert a record in table. I know that using CLR stored procedure for insert , delete and update when you not have any complex logic before performing above operation is not advisable, however to keep the example simple I have used simple Insert example. Below code shows Insert SP,
1: using System;
2: using System.Data;
3: using System.Data.SqlClient;
4: using System.Data.SqlTypes;
5: using Microsoft.SqlServer.Server;
6:
7:
8: public partial class StoredProcedures
9: {
10: [Microsoft.SqlServer.Server.SqlProcedure]
11: public static int sp_InsertEmployee(string FirstName, string LastName, string City)
12: {
13: using (SqlConnection cnn = new SqlConnection("context connection = true"))
14: {
15: cnn.Open();
16: SqlCommand cmd = new SqlCommand("Insert into Employee values ('" + FirstName + "','" + LastName + "','" + City + "')", cnn);
17: int intRowsAffetced = (int)cmd.ExecuteNonQuery();
18: return intRowsAffetced;
19: }
20: }
21: };
Fig – (4) Simple Insert SP
We have used “context connection = true”. It means this CLR stored procedure will be executed in same connection by which the data access layer code has called this sp. Now, Compile the code, if it compiles successfully right click on project in solution explorer and click on Deploy. This will create a assembly in SQL server and creates a clr stored procedure automatically. You can do this task manually also. (If you have used class library as project type instead of SQL Server Project you have to follow steps mentioned below. If you have used SQL Server Project do ignore following 4 steps.) To do this follow steps,
(1) Create a signed assembly from you code. To do this, right click on project and click “properties”. Select the “Sign the assembly” checkbox and select “<New>” from dropdown list. Give a appropriate name to key and that’s it. Now when you compile the project it will generate signed assembly.
Fig – (5) Signing Assembly (Generating strong name for assembly)
(2) Enable CLR integration for selected database. To do this write following commands in query analyzer,
1: sp_configure 'clr enabled',1
2: GO
3: reconfigure
4: GO
5: sp_configure 'clr enabled'
Fig – (6) Enabling CLR Integration
(3) Create assembly in SQL Server.
1: CREATE ASSEMBLY
2: LearnCLRIntegration -- Assembly name, you can write which you want
3: FROM
4: 'C:\LearnCLRIntegration.dll' -- write appropriate path and DLL name
5: WITH PERMISSION_SET = SAFE
6: Go
Fig – (7) Create assembly in SQL Server
There are three modes for Permission Set: SAFE, UNSAFE, EXTERNAL_ACCESS; SAFE – DLL can access only local resources (on same machine), EXTERNAL_ACCESS – DLL can access any resource on network and same machine. In both case it checks ACL for resource. UNSAFE – DLL can access anything without restriction. Choose appropriate permission set for your dll.
(4) Create a stored procedure from assembly,
1: CREATE PROC clrsp_InsertEmployee
2: (
3: @FirstName varchar(50),
4: @LastName varchar(50),
5: @City varchar(50)
6: )
7: AS
8: EXTERNAL NAME LearnCLRIntegration.StoredProcedures.sp_InsertEmployee -- write name as [assemblyName].[Namesace.ClassName].[FunctionName]
9: Go
Fig – (8) Creating a stored procedure.
Here you can see your DLL and stored procedure in SQL server as shown below,
Fig – (9) CLR stored procedure and assembly in SQL Server.
You can use this SP as normal stored procedure in code or in query analyzer. Below is the code that uses this SP for insert,
1: using System;
2: using System.Collections.Generic;
3: using System.Text;
4: using Microsoft.Practices.EnterpriseLibrary.Data;
5: using Microsoft.Practices.EnterpriseLibrary.Common;
6: using System.Data.Common;
7: using System.Data;
8:
9: namespace DAL
10: {
11: public static class EmployeeManagement
12: {
13: public static int InsertEmployee(string FirstName,string LastName,string City)
14: {
15: Database db = DatabaseFactory.CreateDatabase("ConnectionStr");
16: DbCommand cmd = db.GetStoredProcCommand("sp_InsertEmployee");
17: db.AddInParameter(cmd, "FirstName", DbType.AnsiString, FirstName);
18: db.AddInParameter(cmd, "LastName", DbType.AnsiString, LastName);
19: db.AddInParameter(cmd, "City", DbType.AnsiString, City);
20:
21: int intRetValue = (int)db.ExecuteNonQuery(cmd);
22:
23: return intRetValue;
24: }
25:
26:
27: }
28: }
Fig – (10) Code to use CLR stored procedure in Data Access Layer
Conclusion
CLR stored procedures can not be used as replacement of T-SQL statements. Execution of normal T-SQL statement is much faster than CLR Stored Procedure. We can use CLR Stored Procedure to avoid cursors and complex computation.
Happy Programming !!!
Read Full Post »
Tech Treasure 