Writing CLR Stored Proc : Using Visual Studio

Overview & History

First of all let me say my thanks to
Mr. Joymon whose question inspired me to write this resource.

The present post is showing the another method to Write a CLR Proc apart from the method in my earlier post
Writting a CLR Stored Proc.

Step:Followings are the steps to start the above in Visual Studion

1. Start Visual Studio2005 or higher
2. Add new file from File -> New -> Project
3. Select Database Project [provide the name]
4. Create the project, it requires Database Reference [I used EmployeeDB here]
5. In Solution Explorer Right Click on the project name and Click Add
6. Select Stored Procedure
7. Add new File with name 'myTestStoredProcedure.cs'
8. The added file will look like :


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{

[Microsoft.SqlServer.Server.SqlProcedure]
public static void myTestStoredProcedure()
{
//Right some code here

}
};



I am writing a code for following three Stored Proc:

1. myTestStoredProcedure - Simply prints a message
2. spGetRolesList - Display the rows from table
3. spGetEmployeeList - Display the rows from table for specific Age group.

The following code tells how to create a CLR stored proc with the use of Visual Studio:


/************************************************
* Topic : How to Create CLR Proc using VS
* Author : Gaurav Arora
* Reference : A Step Ahead Series - SQL2005
* Visit : http://msdotnetheaven.com
* **********************************************/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
///
/// Prints a Message
///

[Microsoft.SqlServer.Server.SqlProcedure]
public static void myTestStoredProcedure()
{
//Simple proc
SqlPipe objSqlPipe = SqlContext.Pipe;
objSqlPipe.Send("Hi! I am simple CLR PROC");

}
///
/// Proc to Show Rows of [EmployeeDB]..[Roles] table
///

[Microsoft.SqlServer.Server.SqlProcedure]
public static void spGetRolesList()
{
//It returns rows from Roles table
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"Select * from [dbo].[Roles] Order By HireDate";
conn.Open();

SqlDataReader sqldr = cmd.ExecuteReader();
SqlContext.Pipe.Send(sqldr);

sqldr.Close();
conn.Close();


}
///
/// It shows rows from Employee table on basis of supplied age
///
/// a specified age
[Microsoft.SqlServer.Server.SqlProcedure]
public static void spGetEmployeeList(Int32 intAge)
{

//It returns rows from Employee table on basis of supplied age
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";


SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "Select * from [dbo].[Employees] Where Age >=@intAge Order By Age";

SqlParameter paramAge = new SqlParameter();
paramAge.Value = intAge;
paramAge.Direction = ParameterDirection.Input;
paramAge.DbType = DbType.Int32;
paramAge.ParameterName = "@intAge";

cmd.Parameters.Add(paramAge);

SqlDataReader sqldr = cmd.ExecuteReader();
SqlContext.Pipe.Send(sqldr);

sqldr.Close();
conn.Close();

}
};


Build and Deploy the assembly :

1. Press ctrl+shift+B or Select Build option Under Build
2. If project successfully Build, now its time to deploy the assembly in SQLServer Directory.
3. Right click on Project name in SOlution Explorer
4. Click on Deploy.
5. Check the Status Bar for further.
6. If It deployed successfully, you can check the CLR proc from Server Explorer
7. Expand EmployeeDB node.
8. Expand Assemblynode [ you can find 'AStepAheadProcVisual' CLR Assembly]

Executing CLR Stored Proc :

Using Server Explorer of VS

1. Expand the AStepAheadProcVisual node
2. Here you can see a class file and Assembly.info file including three procs.
3. Right click on anyone Stored Proc [option availale :Open, Execute, Step Into Stored Procedure ].
(a) Open : Directs to a specific proc.
(b) Execute : Executes the selected proc and result is available in OutPut window.
(c) Step Into Stored Procedure : It debugs the application following selected proc.


Using SQLServer Management Studio

1. Open your SQLServer Management Studio[if not opened earlier]
2. Type following code in Query Window



Use [EmployeeDB]
Go

DECLARE @Role int
SET @Role = 28
Exec [dbo].[spGetEmployeeList] @Role
Go



3. The above code will display the EmployeeList with Employees having age 28Yrs or more.
4. In same manner execute other procs



Use [EmployeeDB]
Go

Exec [dbo].[myTestStoredProcedure]
Go

Exec [dbo].[spGetRolesList]
Go

DECLARE @Role int
SET @Role = 28
Exec [dbo].[spGetEmployeeList] @Role
Go



Scripts of Databases:

Followings are the scripts of Table(s) used in given example:



USE [master]
GO
/****** Object: Database [EmployeeDB] Script Date: 01/01/2009 23:04:12 ******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'EmployeeDB')
BEGIN
DROP DATABASE [EmployeeDB]
CREATE DATABASE [EmployeeDB]
END

USE [EmployeeDB]
GO
/****** Object: Table [dbo].[employees] Script Date: 01/01/2009 23:05:24 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[employees]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[employees]

CREATE TABLE [dbo].[employees](
[id] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[firstname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('First Name'),
[lastname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('Last Name'),
[age] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('19'),
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[Roles]
CREATE TABLE [dbo].[Roles](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Role] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsRetiree] [bit] NOT NULL,
[HireDate] [datetime] NOT NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

GO



IMPORTANT NOTE:

1. If get error : Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
, While executing the above using SQLSERVER2005/2008, run following code in Query analyzer:



sp_configure 'clr enabled', 1
go
reconfigure
go



2. You can also Debug the Produced code within VS: by starting Debugging from DEBUG MENU


Hope the represented article is worthful for you.