Working with Managed Stored Procedure using Visual Studio 2005
page 3 of 5
by Srinivas Jadhav
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24520/ 42

Steps in Creating Stored Procedure

First, launch Visual Studio 2005 and create a new SQL Server project by selecting File | New Project | Database.

Figure 1

Give a name to the project. I have called it ManagedProcedureTest.

Create a connection for the Database.

Figure 2

Click on "Test Connection" and click "OK."

Once the connection is established, then the popup appears.

If you wish to enable SQL/CLR debugging, then click Yes.

Figure 3

Right Click project and add a new file.

Figure 4

Select Stored Procedure and Click "Add."

The code appears as follows.

Listing 1: MyManagedProcedure.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
  public static void MyManagedProcedure()
  // Put your code here

Build Solution, Right Click Project and then click on Deploy.

Stored Procedure will be deployed to the Database.

Once the Deployment is over, refresh the Database>Stored Procedures.

Figure 5

You can see the dbo.MyManagedProcedure.

Sp_Help [ManagedStoredProcedure].[dbo].[MyManagedProcedure] will show that the Type is an assembly stored procedure. The next step is to execute the procedure.

Listing 2

EXECUTE [ManagedStoredProcedure].[dbo].[MyManagedProcedure]

If the CLR is not enabled, then make it enable by sp_configure statement.

Listing 3

sp_configure 'clr enabled', 1

Then again run the Stored Procedure.

Listing 4

EXECUTE [ManagedStoredProcedure].[dbo].[MyManagedProcedure].

The result will give nothing as MyManagedProcedure does not contain any code.

Store the Procedure with the Input parameter.

Now we will try to access the employee table and do some simple operations to it.

Create an Employee table.

Listing 5

USE [ManagedStoredProcedure]
/****** Object:  Table [dbo].[Employee]    
Script Date: 06/06/2007 16:09:35 ******/
CREATE TABLE [dbo].[Employee](
      [EmpID] [intNOT NULL,
      [EmpName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Salary] [numeric](18, 0) NULL,
      [EmpID] ASC

Insert the records in the table.

I will pass the Input Parameter as Salary and get the employee details.

Add the following code in the MyManagedProcedure() method.

Listing 6

public static void MyManagedProcedure(double sal)
  using(SqlConnection conn = new SqlConnection("context connection=true"))
    SqlCommand cmd = new SqlCommand(
      "SELECT * FROM EMPLOYEE WHERE SALARY >= @sal", conn);
    SqlParameter paramSal = new SqlParameter();
    paramSal.Value = sal;
    paramSal.Direction = ParameterDirection.Input;
    paramSal.DbType = DbType.Int32;
    paramSal.ParameterName = "@sal";

Compile the code and deploy it.

In the SQL Server, refresh the Stored Procedure.

You can see one Input parameter @sal (float,Input,No default).

Execute the Stored Procedure.

Listing 7

DECLARE @sal float
SET @sal = 2000
EXECUTE [ManagedStoredProcedure].[dbo].[MyManagedProcedure] 

You will get the result for the employees whose salary is greater then 2000.

Store Procedure with Input & Output parameters.

Now, I want the Out parameter from the Managed Stored Procedure.

Do the following changes to the existing method.

I want the count of the number employees as an out parameter for the given salary.

Add the parameter "cnt" as ref to get the Count of number of employees for the given salary.

Listing 8

public static void MyManagedProcedure(double sal,ref int cnt)

Change the SqlCommand object.

Listing 9

SqlCommand cmd = new SqlCommand(
    "SELECT @cnt=COUNT(*) FROM EMPLOYEE WHERE SALARY >= @sal",conn);

Create another SqlParameter for Count.

Listing 10

SqlParameter paramCount = new SqlParameter();
paramCount.Direction = ParameterDirection.Output;
paramCount.DbType = DbType.Int32;
paramCount.ParameterName = "@cnt";
Add SqlParameter to the SqlCommand object.
At the end add the following code after SqlContext.Pipe.ExecuteAndSend(cmd);
cnt = Convert.ToInt32(paramCount.Value);        

In SQL Server, refresh the Stored Procedure.

You can see one Input parameter @sal (float,Input,No default) and @sal (float,Input/Output,No default).

Execute the Stored Procedure.

Listing 11

DECLARE @sal float
DECLARE @cnt int
SET @sal = 2000
SET @cnt = 0
EXECUTE [ManagedStoredProcedure].[dbo].[MyManagedProcedure] 
  ,@cnt OUTPUT
Print @cnt


When the execution of the code is requested as part of a user connection, access to the context of the caller from the code running in the server is required.

SqlContext provides access to the following components.

Pipe: It is an object through which the results flow to the client. It allows the caller to send result sets, messages and the results of executing commands back to the client.

TriggerContext: It is an object which can only be retrived from within a CLR trigger. It gets the trigger context used to provide the caller with information about what caused the trigger to fire, and a map of the columns that were updated.

IsAvailable: Specifies whether the calling code is running within the SQL Server and if the context connection can be accessed.

WindowsIdentity: It is used to retrieve the windows identity of the caller.

View Entire Article

User Comments

No comments posted yet.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2021  |  Page Processed at 2021-02-27 11:06:41 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search