CLR Integration in SQL Server 2005
page 4 of 7
by Soyuj Kumar Sahoo
Average Rating: 
Views (Total / Last 10 Days): 44506/ 45

Advantages of CLR procedure over T-SQL procedure

·         Releases the tension to become Master of T-SQL for doing complex database operations.

·         Through this we can move our Business layer into database, getting closer to Data for increasing performances by avoiding unnecessary server trips.

·         Here .NET Framework Base Class Libraries (BCL) are available to use, through which we can solve our complex logic operations like string manipulations, threading operations, file management, cryptography, etc.

·         It is known as Managed Procedure that means it deals with automatic garbage collection, memory management, exception handling, etc.

·         As a developer in C#.NET or VB.NET we can use our OOP's concepts for more structured operations.

·         This feature provides the ability to leverage the features of .NET Code Access Security (CAS) to prevent assemblies from performing certain operations.

View Entire Article

User Comments

Title: CLR integration with SQL2005   
Name: Narender
Date: 2009-09-29 7:38:59 AM
Very informative and nice example...
Title: Can we do with CSC C#   
Name: Kaushal Yadav
Date: 2009-05-26 8:29:43 AM
can we do it with csc compiler?
while i m tring with csc, dll is not generating,
is there any other way to gnerate dll with csc compiler/
guide me.

Thanks in advance.
Title: Great Article (No way to go any where)   
Name: Kaushal Yadav
Date: 2009-05-26 8:26:19 AM
This is very helpful for learning prospective.
a lot of thanks for this article
Title: Very very excellent Article   
Name: Prashant Bhande
Date: 2009-04-27 6:17:30 AM
This is the article i am finding from last few days..
Its very helpful for me in my project.

Thanks Dear .........Soyuj Kumar Sahoo
Title: Excellent Article   
Name: Matt Hedenberg
Date: 2009-04-22 12:00:02 PM
Thanks for the tutorial and samples, just excellent!
Title: getting syntax error while compling it on dos promt   
Name: Kaushal
Date: 2009-04-11 9:40:02 AM
public class clsmyclr
public clsmyclr()

public static void myclrpro(int intCatID)
using (SqlConnection sqlCon = new SqlConnection("context connection=true"))
SqlCommand cmdsel = new SqlCommand("Select * from tblcategory where catid=" + intCatID, sqlCon);
SqlDataReader oReader = default(SqlDataReader);
oReader = cmdsel.ExecuteReader();
catch (Exception ex)

hie Soyuj , its really gd article but i m getting syntax error while creating assembly, so may i knw where i m going wrong
Title: Thanks!   
Name: Eric Fickes
Date: 2009-03-21 5:03:51 PM
Thanks, this post works like a champ! Bummer it's in VB though ;)
Title: Very good and helpfull article.   
Name: Rajesh Verma
Date: 2009-01-22 6:21:20 AM
This is what i m searching for a long, really very helpful.
thaks Soyuj
Title: best one   
Name: niraj
Date: 2008-09-10 4:02:12 PM
thank you for this article
Title: Good one   
Name: Vijay Kumar
Date: 2008-02-20 1:13:32 AM
Hi Soyuj,
Really a nice article.

Thank you
Vijay Kumar
Software Engineer
Title: CLR Integration   
Name: Hemant Sudehely
Date: 2008-02-11 2:53:52 AM
Thanks for providing such a good article to understand features of sql-server2005. Its really good to read.
Title: Very nice Article   
Name: Pankaj Bahuguna
Date: 2008-01-14 6:17:46 AM
Thanks Soyuj Kumar Sahoo,

Just because of this code,
I have been able to implement CLR Integration in my application
Title: Nice Article   
Name: Sumit Kumar Sharma
Date: 2007-09-27 7:30:01 AM
Hi Soyuj,
This article is really useful for me to builed and deploy the Assemblies into Sql Server.

Thanks Once again....!!!
Title: Execution of the CLR Stored Procedure failed   
Name: Raaz
Date: 2007-08-20 9:06:57 AM
The following are the steps that were followed to call the webservice from CLR Stored Procedure...

I was able to create the assembly and CLR stored procedure in SQL SERVER but failed at final execution of the procedure....

wsdl /o:TerraService.cs /n:ClrWebServices.Test
cLR Procedure

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using ClrWebServices.Test;

public partial class StoredProcedures
public static void InsertLocation(SqlString city,
SqlString state, SqlString country)
using (SqlConnection cn =
new SqlConnection("context connection=true"))
string query = "INSERT INTO dbo.Locations
(City,State,Country,AerialMap) VALUES
(@City, @State, @Country, @Image)";

using (SqlCommand insertCommand =
new SqlCommand(query,cn))
SqlParameter[] sqlParams =
new SqlParameter[4];
sqlParams[0] =
new SqlParameter("@City", city);
sqlParams[1] =
new SqlParameter("@State", state);
sqlParams[2] =
new SqlParameter("@Country", country);
sqlParams[3] =
new SqlParameter("@Image",



public static byte[] GetLocationImage(SqlString city,
SqlString state, SqlString countr
Title: Can't deploy the assembly   
Name: Mr SQL
Date: 2007-03-30 7:23:15 AM
I get an error when attempting to deploy the assembly to SQL Server Express. It comes up with

Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'MyCLRAssembly' failed because assembly 'MyCLRAssembly' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

Does anybody know hot to fix this? I have followed all the steps as above and they worked fine until I hit this error.

Many thanks.
Title: WonderFul One   
Name: Vinuraj
Date: 2007-03-15 6:10:12 AM
Its a Wonderful One I am searching. With a Brief Descrition I Got it.
Title: Its realy nice and very very important stuff for creating the system.web.dll   
Name: Nitin Shende
Date: 2007-02-06 6:26:08 AM
Thanx for the detail description.
This article covers all the steps required for the creating the assembly, which reference of the system.web.dll
Do posting more and more.
Title: Problem with system.web.dll   
Name: Josh
Date: 2007-01-15 5:09:17 PM
I'm having the same problem as Lan calling CREATE ASSEMBLY on an assembly which references System.Web.dll.

See this post for more info. Please respond with help there.


Title: Need help for deploying assembly   
Name: Lan
Date: 2007-01-14 9:04:23 AM
This is great! Thank you for the detail description.
I'm trying to deploy a simple assembly that references the System.Web.HttpUtility class. I got the following error:

Warning: The Microsoft .Net frameworks assembly 'system.web, version=, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Drawing' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Drawing.BufferedGraphicsContext::bFillColorTable][mdToken=0x6000243][offset 0x00000053][found address of Byte] Expected numeric type on the stack....

When I tried to deploy the system.web.dll, I got the same error. Any idea of what could be the problem?

Thank you!
Title: Its really cool   
Name: vas
Date: 2007-01-08 10:00:25 AM
Its one of the best article i read so far. keep it up. do posting more...
Title: fed back for soyuj   
Name: ranjan patra
Date: 2006-12-26 9:57:51 AM
the way u discussed all the things is awosome.
it is always expected from a guy like u to share u'r knowledge with others.
as i personally know u it is a great pleasure to get some basic idea about stored procedures .
keep it up.
tk cr
Title: Very Very Important and new stuff   
Name: Abhishek Kumar Singh
Date: 2006-12-20 5:25:37 AM
This article covers one of the newest and important functionality of SQL Server 2005. As a .net programmer I got to know feature to implement. Thanx Soyuj for providing such a wonderful content.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-05-24 1:28:48 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search