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.

Control State, Partial Class, Iterators, Nullable – At a glance

Control State, Partial Class, Iterators, Nullable – At a glance

Control State

Control State is a new feature added in Asp.net2.0 framework. Control state is very similar to View State, where value is stored in the hidden_VIEWSTATE form field, with a little difference i.e. Control State cannot be disabled. Control state is intended to be used only for storing crucial information across postbacks.

Partial Classes

Partial Classes are new feature added to the .net framework 2.0. Partial classes allow dividing up a single class into multiple class file. These classes are combined into a single class later when compiling. To create a partial class there is a simple keyword partial.

Example:

public partial class MathClass
{
public int Add(int a, int b)
{
return a+b;
}
}

public partial class MathClass
{
public int Substract(int a, int b)
{
return a-b;
}
}



At compile time system gather the information of all relevant partial classes MathClass in above case, and combined all into a single class.

Iterators

Iterators enable us to use foreach loops on our own custom types. To achieve the same we need to have class implement the IEnumerable interface.

Example:

public class mylistClass
{
internal object[] objElements;
internal int intcount;
public IEnumerator GetEnumerator()
{
yield return “Gaurav”;
yield return “Shuby”;
}
}

//here is the use
//put following code at anywhere as per your requirement, I used it on page_load

Void pageload(object sender, EventArgs e)
{
//create an instance of class
mylistClass custList = new mylistClass();

foreach (String custItem in custList)
{
Response.Write(“Item : “ +custItem.ToString() + “<br/>”);
}

}



Note:
You need to include System.Collections namespace in above.

Nullable

Nullable types are instances if System.Nullable. A nullable type can represent the normal range of values of its underlying value type plus an additional null value.

Example:


namespace AStepAhead.Nullable
{
class nullableclass
{
static void Main(string[] args)
{
int? num = null;
int? num1 = null;
if (num.HasValue == true)
{
Console.WriteLine("Num : {0}", num);
}
else
{
Console.WriteLine("Num has Null value");
}

//int y = num.GetValueOrDefault(); //throw an exception
int z;
try
{
//y = num.Value;
//Console.WriteLine("Y:{0}", y);
z = num1 ?? 2;
Console.WriteLine("Z:{0}", z);
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}


Console.ReadLine();
}
}
}


Note:
You need to include System.Collections.Generic namespace in above.