.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> ASP.NET >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Writing a CLR Proc Using Visual Studio - A Step Ahead Series

Posted By:Gaurav Arora       Posted Date: April 26, 2009    Points: 25    Category: ASP.NET    URL: http://www.dotnetspark.com  
 

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.


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend