AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1338&pId=-1
Working with Managed Stored Procedure using Visual Studio 2005
page
by Srinivas Jadhav
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24519/ 41

Introduction

Integration of SQL Server 2005 (formerly called as Yukon) with .NET CLR has given an opportunity for the .NET Professional to write Stored Procedure, Triggers, and UDF by using Managed Language such as C# and VB.NET.

Advantages of Managed Stored Procedure

·         Increased productivity

·         Significance performance gains

·         Ability to include the features of .NET code (e.g. arrays, sophisticated exception, and reusability of code)

·         Enhanced Safety and Security

·         Language options (like C#, VB.NET and VC++)

·         Stability and reliability

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
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  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
go
reconfigure
go

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]
GO
/****** Object:  Table [dbo].[Employee]    
Script Date: 06/06/2007 16:09:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
      [EmpID] [intNOT NULL,
      [EmpName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Salary] [numeric](18, 0) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
      [EmpID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFFON [PRIMARY]
) ON [PRIMARY]

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

[Microsoft.SqlServer.Server.SqlProcedure]
public static void MyManagedProcedure(double sal)
{
  using(SqlConnection conn = new SqlConnection("context connection=true"))
  {
    conn.Open();
    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";
 
    cmd.Parameters.Add(paramSal);
 
    SqlContext.Pipe.ExecuteAndSend(cmd);
  }
}

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] 
   @sal           

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.
cmd.Parameters.Add(paramCount);
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] 
   @sal
  ,@cnt OUTPUT
Print @cnt

SqlContext

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.

Debugging Managed Stored Procedure

You can now debug the CLR Stored Procedure the same way as the T-SQL Stored Procedure.

Following are the steps used to debug it.

In the Solution Explorer, right click the Test Scripts and Add Test Script; Test1.sql is added.

Rename it to MyManagedProcedure.sql.

Now you add the following code.

Listing 12

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

Set the break point in the code.

Open Server Explorer View > Server Explorer.

Right Click Database and Apply Allow SQL/CLR Debugging

Debug > Start Debugging. The Yellow Pointer appears at the break point.

You can view the Output.

Conclusion

From this article we came to learn how to create a CLR (Managed) Stored Procedure. We are able to access Input and Output parameters. You also learned the advantages of Managed Stored Procedure and how to debug a Managed Stored procedure like the T-SQL Stored Procedure.



©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-02-27 10:34:18 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search