CLR Integration in SQL Server 2005: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
CLR Integration in SQL Server 2005

page 4 of 7

by Soyuj Kumar Sahoo
Feedback

Average Rating:

Views (Total / Last 10 Days): 24124/ 199

Article Contents:

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.

« (Page 3)

View Entire Article

(Page 5) »

Article Feedback

Comment:
Title:
Name:
Url:	( Optional )

Please add 7 and 8 and type the answer here:

User Comments

Title: best one
Name: niraj
Date: 9/10/2008 4:02:12 PM
Comment:
thank you for this article

Title: Good one
Name: Vijay Kumar
Date: 2/20/2008 1:13:32 AM
Comment:
Hi Soyuj,
Really a nice article.

Thank you
Vijay Kumar
Software Engineer

Title: CLR Integration
Name: Hemant Sudehely
Date: 2/11/2008 2:53:52 AM
Comment:
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: 1/14/2008 6:17:46 AM
Comment:
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: 9/27/2007 7:30:01 AM
Comment:
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: 8/20/2007 9:06:57 AM
Comment:
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 http://www.terraserver-usa.com/TerraService2.asmx
--------------------
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
{
[Microsoft.SqlServer.Server.SqlProcedure]
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",
GetLocationImage(city,state,country));

insertCommand.Parameters.AddRange(sqlParams);

cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}

public static byte[] GetLocationImage(SqlString city,
SqlString state, SqlString countr

Title: Can't deploy the assembly
Name: Mr SQL
Date: 3/30/2007 7:23:15 AM
Comment:
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: 3/15/2007 6:10:12 AM
Comment:
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: 2/6/2007 6:26:08 AM
Comment:
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: 1/15/2007 5:09:17 PM
Comment:
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.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/8782336f27e9271c/?lnk=igtc#

Thanks,

Josh

Title: Need help for deploying assembly
Name: Lan
Date: 1/14/2007 9:04:23 AM
Comment:
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=2.0.0.0, 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=2.0.0.0, 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!
Lan

Title: Its really cool
Name: vas
Date: 1/8/2007 10:00:25 AM
Comment:
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: 12/26/2006 9:57:51 AM
Comment:
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
ranjan

Title: Very Very Important and new stuff
Name: Abhishek Kumar Singh
Date: 12/20/2006 5:25:37 AM
Comment:
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.