CLR Stored Procedure in SQL Server 2005

March 11, 2008 by chiragrdarji

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,

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedure1()
    {
        // Put your code here
    }
};

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,

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int sp_InsertEmployee(string FirstName, string LastName, string City)
    {
        using (SqlConnection cnn = new SqlConnection("context connection = true"))
        {
            cnn.Open();
            SqlCommand cmd = new SqlCommand("Insert into Employee values ('" + FirstName + "','" + LastName + "','" + City + "')", cnn);
            int intRowsAffetced = (int)cmd.ExecuteNonQuery();
            return intRowsAffetced;
        }
    }
};

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,

sp_configure 'clr enabled',1
GO
reconfigure
GO
sp_configure 'clr enabled'

Fig – (6) Enabling CLR Integration

(3) Create assembly in SQL Server.

CREATE ASSEMBLY
    LearnCLRIntegration -- Assembly name, you can write which you want
FROM
    'C:\LearnCLRIntegration.dll'  -- write appropriate path and DLL name
WITH PERMISSION_SET = SAFE
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,

CREATE PROC clrsp_InsertEmployee
(
@FirstName varchar(50),
@LastName varchar(50),
@City varchar(50)
)
AS
EXTERNAL NAME LearnCLRIntegration.StoredProcedures.sp_InsertEmployee -- write name as [assemblyName].[Namesace.ClassName].[FunctionName]
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,

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using System.Data.Common;
using System.Data;
 
namespace DAL
{
    public static class EmployeeManagement
    {
        public static int InsertEmployee(string FirstName,string LastName,string City)
        {
            Database db = DatabaseFactory.CreateDatabase("ConnectionStr");
            DbCommand cmd = db.GetStoredProcCommand("sp_InsertEmployee");
            db.AddInParameter(cmd, "FirstName", DbType.AnsiString, FirstName);
            db.AddInParameter(cmd, "LastName", DbType.AnsiString, LastName);
            db.AddInParameter(cmd, "City", DbType.AnsiString, City);
 
            int intRetValue = (int)db.ExecuteNonQuery(cmd);
 
            return intRetValue;
        }


    }
}

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 !!!

Posted in DotNet 2.0, Sql Server, Techincal | 29 Comments

29 Responses

on March 16, 2008 at 2:56 pm | Reply SQL SERVER - 2005 - Introduction to CLR and Integration to CLR with SQL - Simple Sample Example of CLR « Journey to SQL Authority with Pinal Dave

[...] March 14, 2008 by pinaldave It is always interesting to learn new technology. I always wanted to learn about CLR but I somehow never got chance to practice on it. I had requested excellent blog writer and .net expert Chirag Darji to write introduction to CLR and integration with SQL Server. I think he has posted some great stuff on this blog about introduction to CLR integration to SQL Server. [...]
on March 25, 2008 at 5:16 am | Reply sri

i want a coding for stored procedure in sqlserver2005 for .net2.0 application.my requirement is two input parameters username and password ,one output parameter.if two input parameters are already registerd in sqlserver database the output parameter will return 1.oterwise it returns 0.
plz write code for that.
on March 25, 2008 at 6:37 pm | Reply chiragrdarji

sri,

You can use following SP.

CREATE PROCEDURE usp_ValidateUser
(
@UserName VARCHAR(200),
@Password VARCHAR(20),
@Result bit OUTPUT
)
AS
BEGIN
SET @Result = 0
IF EXISTS
(
SELECT UserId FROM [USERINFO]
WHERE UserName = @UserName
AND Password = @Password
)
BEGIN
SET @Result = 1
END
END
on April 19, 2008 at 12:47 pm | Reply naresh

query:

pls send sytax for update query using stored procedure in sql server 2005
on April 19, 2008 at 12:52 pm | Reply jayakrishna

hello sir,
Q:what is the query string, to update the values in c#.net.
pls send the answere.

thankyou sir;
on April 29, 2008 at 7:41 am | Reply kannan

create procedure. (productid,categoryid, keyword)
if productid and categoryid is not empty, get allproduct from Gbproduct table.
if productid is empty, select products with categoryid.

if categoryidis empty, select categoryidis with categoryid.
to get image for a product join Gbimage table with productid as a common key.
Pls send stored procedure full syntax.
kannan
on May 11, 2008 at 5:50 am | Reply invoke41

Hey,

Nice post. Just remember it is always bad practice to use a cursor unless you really REALLY have to. I wouldnt write a cursor in C# if you can solve it using a set oriented approach.
The fact that a cursor is slow is not so much that T-SQL is interpreted but more that it is against the nature that Sql Server is optimized to go about its query ways.

That being said, some exotic once in a lifetime things cannot be solved without a cursor. But always look at sets first.
on May 12, 2008 at 4:03 am | Reply chiragrdarji

invoke41,

Thanks for sharing your thaughts.
on July 23, 2008 at 10:58 am | Reply Kamal Jindal

Hey,

Nice post.

i followed one by one step, but when i create procedure then it is giving one error

”
CREATE PROCEDURE for “clrsp_InsertEmployee” failed because T-SQL and CLR types for parameter “@FirstName” do not match.”

Please give me the solution

Kamal Jindal
on July 29, 2008 at 8:53 am | Reply TB

Hi,
I am working on VB.Net 2005 SQL Project, in that we need to create stored procedure using VB.Net code. Finally
need to deploy the creted procedure, which automatically creates the stored procedure in the SQL Server.
My querry is since i’m using complex stored procedure, in which I need to pass the output of one SP to the input
of new SP and further need to compare the output also. Can you please tell me how I can store these output values
from the SP’s permanently in some datatype/collection. What is that dataholder eg. which can be used to handle these
values?
on August 11, 2008 at 3:08 pm | Reply Sreedev

Hi,

This is a nice article. Thanks for it. I would like know if it is possible to raise an event in a external application for example a windows service, through a CLR Trigger ?

Thanks in advance.
on September 23, 2008 at 8:09 am | Reply TARUN

hey man… again i found the help from ur blog.. thanks alootttttttttttttttttttttttttttttttttttttt
on October 9, 2008 at 7:10 pm | Reply JIMISH

hi can anybody show me how to do simple stored procedure in Sql Server and connection to the C# with user defined DSN please….
on October 20, 2008 at 6:37 pm | Reply ledaker

Good tutorial, thanks
on November 8, 2008 at 6:46 am | Reply Adinath

pls send sytax for update query using stored procedure in sql server 2005
on December 17, 2008 at 12:36 pm | Reply shruthi

excellent article….iam benifitted alot..thankyou so much…
and let me have your email addrs plz……….thanz shruthi…
on December 30, 2008 at 3:41 pm | Reply Anup

Below is my Query

Create FUNCTION Function9005A (
@ProcessCase Integer,
)

RETURNS TABLE
(
BranchCode Integer,
BranchName nvarchar(50),
ProductCode nvarchar(50),
ProductName nvarchar(100),
CustNo Integer,
CustName nvarchar(150),
)
AS
EXTERNAL NAME World.[World.Mine.Function9005A].InitMethod
go

I have this Assembly created and able to register it in Sql server 2005. I am able to retrive function name (For e.g. : Function9005A) From the sysobjects table but is there any query or stored proc to retrive the assembly name of that function. (For e.g. World in the example provided)
on January 27, 2009 at 10:59 am | Reply heena

sir please create and execute Create Table tblStockGroups
(
CmpID Decimal Not Null,
StkGrpID Decimal Identity(10,10) Not Null,
StkGrpName Varchar(100) Not Null,
StkGrpAlias Varchar(100) Not Null,
StkGrpDescription Varchar(1000) Null,
StkGrpParent Decimal Not Null,
AddQuantity Bit Default 0,
);
Alter Table tblStockGroups Add
Constraint FKCompanyID Foreign Key(CmpID) References tblCompanies(CmpID) On Delete Cascade,
Constraint PKStockGroupID Primary Key(StkGrpID),
Constraint UnqCmpStkGrpName Unique(CmpID, StkGrpName),
Constraint FKStkGrpParent Foreign Key(StkGrpParent) References tblStockGroups(StkGrpID);

procedure of following tables
on February 10, 2009 at 6:02 am | Reply Mohd Javed

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
=============================================
Author:
Create date:
Description:
=============================================
*/

ALTER PROCEDURE [dbo].[procFetchDocDownload]
@CARID int,
@Error int OUTPUT,
@ErrorMessage varchar(10) OUTPUT

AS
BEGIN

SET NOCOUNT ON;
SELECT dbo.tbl_Documents.DocumentName,dbo.tbl_Documents.DocBinary
FROM dbo.tbl_Documents INNER JOIN dbo.tbl_CARAttachments ON dbo.tbl_Documents.DocId = dbo.tbl_CARAttachments.DocId
WHERE dbo.tbl_CARAttachments.CARID =@CARID
IF(@@Error0)
BEGIN
SET @Error=-100
SET @ErrorMessage=’Error’
END
ELSE
BEGIN
SET @Error=0
SET @ErrorMessage=’Data Fetched SUCCESS’
END

END
on February 10, 2009 at 6:04 am | Reply Mohd Javed Comm-IT

ALTER PROCEDURE [dbo].[procGetWorkgroup]
@LoginName varchar(30),
@LocationID int,
@Error int OUTPUT,
@ErrorMessage varchar(10) OUTPUT

AS
BEGIN

SET NOCOUNT ON;

SELECT dbo.tbl_LoginBranchMap.BranchId, dbo.tbl_WorkflowBranchMap.BranchName FROM dbo.tbl_LoginBranchMap INNER JOIN dbo.tbl_WorkflowBranchMap ON dbo.tbl_LoginBranchMap.BranchId=dbo.tbl_WorkflowBranchMap.BranchId
INNER JOIN dbo.tbl_LocationBranchMap ON dbo.tbl_LocationBranchMap.BranchID= dbo.tbl_WorkflowBranchMap.BranchId
WHERE (dbo.tbl_LoginBranchMap.LoginName = @LoginName AND dbo.tbl_LoginBranchMap.IsActive=’Y’ AND dbo.tbl_LocationBranchMap.LocationID=@LocationID)

IF(@@Error0)
BEGIN
SET @Error=-100
SET @ErrorMessage=’Error’
END
ELSE
BEGIN
SET @Error=0
SET @ErrorMessage=’SUCCESS’
END

END
on February 10, 2009 at 6:05 am | Reply Mohd Javed Comm-IT

ALTER PROCEDURE [dbo].[SP_WORKFLOW_MAP]
(
@iCARID Int,
@iCARType nvarchar(50),
@iExecutionId Int,
@iCrtnBy nvarchar(20),
@oError Int output ,
@oErrorMsg Varchar(MAX) output
)
AS
BEGIN

INSERT INTO [dbo].[TBL_WORKFLOW_MAP]
([CARID]
,[CARType]
,[ExecutionId]
,[crtn_by]
,[crtn_dt])
VALUES
(@iCARID
,@iCARType
,@iExecutionId
,@iCrtnBy
,getdate())

IF @@ERROR 0
BEGIN
SET @oErrorMsg = ‘ERROR IN SAVING DATA’
SET @oError = -1
END
ELSE
BEGIN
SET @oErrorMsg = ‘SUCCESS’
SET @oError = 0
END

END
NORMAL_EXIT:
RETURN
on February 10, 2009 at 6:06 am | Reply Mohd Javed Comm-IT

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: }
on February 10, 2009 at 6:08 am | Reply Mohd Javed Comm-IT

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1()
{ // Put your code here 14:
}
};
on February 10, 2009 at 6:10 am | Reply Mohd Javed Comm-IT

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:
}
on April 21, 2009 at 11:52 am | Reply Valdet

here i found what i needed, thanks a lot
on July 11, 2009 at 8:41 am | Reply asdsdasd

kmadflk
‘ladsfasdf

asdf
sdf
sdf
sdf
ds
s
d
sd
sd
sdadsgfasdsd
on July 11, 2009 at 8:41 am | Reply asdsdasd

dsfadsg aa
asdf
asd
asdf
asd
asdg
asdg
asdg
asdg
asdg
ads
gasd
g
asg
adsg
adsg
adg
asd
g
adg
adg
asdg
a
dsg
adsg
as
dgdasg
on August 2, 2009 at 2:29 pm | Reply kavikannan

how to create a stored procedure in sql server 2005
on September 25, 2009 at 9:43 am | Reply satinder singh

good example
but tell me how can call clr storep on th page
Thanx.

Comments RSS

	CyberChrist on Value does not fall within the…
	Chippo on Could not load file or assembl…
	Strikingknife on An error occurred creating the…
	James on Set Full Image Path in FCK…
	kosal on How to change App.config file …

Tech Treasure

Articles of ASP.NET, C#, SQL Server, Timesaver Tools and something about me

CLR Stored Procedure in SQL Server 2005

29 Responses

Leave a Reply

Recent Posts

c

Archives

Pages

Top Posts

Category Cloud

Blog Stats

Recent Comments

Twitter

Analytics