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] [int] NOT 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 = OFF) ON [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.
