Step by Step procedure of CLR Integration in SQL Server 2005

CLR integration in SQL Server 2005:

In T-SQL impossible when we need a stored procedure with some array-operations, text-parsing or complex math operations. However, it is quite easy if our Server allows us to deploy C# or VB.NET code that is used within the Server process. In other words, if we need complex procedural code, we can write it as managed code.

SQL Server 2005 we can write store procedure, triggers,user defined functions , user defined types etc in managed code(CLR) also. It acts as the operating system for the CLR when it is hosted inside SQL Server.

Procedure for Creating CLR integrated Store Procedure in SQL Server 2005 using Visual Studio 2005:

Open Visual Studio 2005 IDE.
· Go to menu File -> New -> Project.
· Within the “New Project” dialog box, select “database” (VB) as the project type and select “SQL Server Project” as the template. Provide your own project name, choose your own location and finally click OK.

You will be prompted with the “New Database Reference” dialog, where you need to provide the instance name of your SQL Server 2005 with database information.

When you are prompted for “SQL/CLR debugging” press “YES”.

Go to the Solution Explorer, right click on project and go to Add -> Stored Procedures

Select “Stored procedure” as in the template, provide the name of the stored procedure as “StoreProcedure1” and finally click add.

Add your code like this

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures1

Public Shared Sub SP_DisplayEmp()
Dim Con As New SqlConnection("context connection=true")
Con .Open()
Dim Cmd As New SqlCommand("select eid,ename,sal from emp", Con)
Dim oReader As SqlDataReader
oReader = Cmd .ExecuteReader()
Catch ex As Exception
End Try
End Sub

End Class

Here SqlContext.Pipe statement is an sql ouput statement.
After this ReBuild and Deploy the project through solution explorer.

Now new procedure named "SP_DisplayEmp" is added in master database. This procedure retrieve all the data which available in Emp table.

Open SQL server 2005 Query wizard and execute the following statements

use master
--To enable SQL Server 2005 for CLR integration
EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
create table emp
eid varchar(10),
ename varchar(40),
sal int

insert into emp values('E01','Crys',81000)
insert into emp values('E02','Jhon',72000)
insert into emp values('E03','Gogul',90000)

execute SP_DisplayEmp

CLR integrated Procedure Ouput is

1 comment:

Jonathan Kehayias (MCITP, MVP) said...

That is a very bad example of how to use SQLCLR. If you do a basic benchmark, you will find that your CLR stored procedure is very slow compared to a standard TSQL Stored Procedure. Unless you have complex computational, looping or string parsing code that can't be written efficiently in TSQL as setbased logic, you shouldn't be using SQLCLR. TSQL should be used to retrieve data in the manner that your example does. It is generally around 10 times slower in SQLCLR.