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")
Try
Con .Open()
Dim Cmd As New SqlCommand("select eid,ename,sal from emp", Con)
Dim oReader As SqlDataReader
oReader = Cmd .ExecuteReader()
SqlContext.Pipe.Send(oReader)
Catch ex As Exception
SqlContext.Pipe.Send(ex.ToString())
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
GO
--To enable SQL Server 2005 for CLR integration
EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO
create table emp
(
eid varchar(10),
ename varchar(40),
sal int
)
GO

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

execute SP_DisplayEmp
GO


CLR integrated Procedure Ouput is