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

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
Comment:
Very informative and nice example...
Title: Can we do with CSC C#   
Name: Kaushal Yadav
Date: 2009-05-26 8:29:43 AM
Comment:
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
Comment:
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
Comment:
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
Comment:
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
Comment:
public class clsmyclr
{
public clsmyclr()
{

}
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void myclrpro(int intCatID)
{
using (SqlConnection sqlCon = new SqlConnection("context connection=true"))
{
try
{
sqlCon.Open();
SqlCommand cmdsel = new SqlCommand("Select * from tblcategory where catid=" + intCatID, sqlCon);
SqlDataReader oReader = default(SqlDataReader);
oReader = cmdsel.ExecuteReader();
SqlContext.Pipe.Send(oReader);
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.ToString());
}
}
}
}

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
Comment:
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
Comment:
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
Comment:
thank you for this article
Title: Good one   
Name: Vijay Kumar
Date: 2008-02-20 1:13:32 AM
Comment:
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
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: 2008-01-14 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: 2007-09-27 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: 2007-08-20 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: 2007-03-30 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: 2007-03-15 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: 2007-02-06 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: 2007-01-15 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: 2007-01-14 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: 2007-01-08 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: 2006-12-26 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: 2006-12-20 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.






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


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-11-24 7:03:10 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search