Feb 11, 2014

How to create CLR stored procedure in visual studio 2010 in vb.net

We can create CLR stored procedure in visual studio 2010 in vb.net very easily if you following steps:

Step 1: Create a Visual Basic SQL CLR Database Project in visual studio.


Step 2: You will get Add Database Reference window. Click on Add New Reference...


Step 3: In New Database Reference window fill database information where you want to create CLR stored procedure.


Step 4: In pop up, you should enable SQL/CLR debugging for this connection.


Step 5: Right-click on your project in solution explorer and choose Add -> New Item...
Now select Stored Procedure and write down any name of your stored procedure.


Step 6: You will get auto gendered code something like this:

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


Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub  uspGetEmployeeInfo ()
        ' Add your code here
    End Sub
End Class

Step 7: Here you can write code of your stored procedure according to your requirement. In this example, we are going to create such CLR stored procedure which displays the information of employees from Employee table. Open SQL server management studio (SSMS):

a. Creating Employee table in the SampleDb database.

USE [SampleDB]

CREATE TABLE [dbo].[Employee](
    [EmpID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [EmpName] VARCHAR(100) NULL,
    [Age] TINYINT NOT NULL DEFAULT(0),
    [Country] VARCHAR(50) NULL,
 )

b. Insert some data into it. Let us assume Employee table has following data:

EmpID
EmpName
Age
Country
1
Scott
32
USA
2
Greg
31
UK
3
Akram
30
Pakistan
4
Rohit
23
India
5
Nicolus
28
Russia

c. Enable "clr enabled" configuration option.

EXECUTE SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

d. Set your database trustworthy:

ALTER DATABASE SampleDB SET TRUSTWORTHY ON

Step 8: Edit the code of CLR stored procedure in visual studio:

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

Partial Public Class StoredProcedures

    Private Shared command As SqlCommand
    Private Shared reader As SqlDataReader

    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub uspGetEmployeeInfo()

        Using connection As New SqlConnection("context connection=true")

            connection.Open()
            command = New SqlCommand("SELECT EmpName,Age,Country FROM Employee", connection)
            reader = command.ExecuteReader()

            SqlContext.Pipe.Send(reader)

        End Using

    End Sub
End Class

Step 9: It is not possible to create CLR stored procedure in .net framework 4.0 so change it to 3.5
For this right click on your project and choose Properties. Now select Compile tab and click on Advanced Compile options... and change the .net framework


Step 10: Now make your project unsafe. For this select Database tab in the properties window of your project and choose Permission level Unsafe.


Step 11: Now build your solution and deployed your project.


Step 12: If deployment is successful then it will create CLR stored procedure in your database in SSMS.


Step 13: We cannot modify or see the script of CLR procedure in SSMS. Here we can only execute this stored procedure

USE [SampleDB]

DECLARE  @return_value INT
EXEC @return_value = [dbo].[uspGetEmployeeInfo]
SELECT   'Return Value' = @return_value

Output:
EmpName
Age
Country
Scott
32
USA
Greg
31
UK
Akram
30
Pakistan
Rohit
23
India
Nicolus
28
Russia

No comments:

Post a Comment