CLR Stored Procedures – Part 1: Highlights

New to .NET integration in SQL Server? Here is some useful information put together for you:

·         .NET integration is one of the key feature in SQL Server 2005

·         The CLR is hosted by SQL server thereby executing the stored procedures in SQL Server’s process space

·         CLR stored procedures uses ADO.NET objects (SqlCommand, SqlConnection, DataSet etc) to retrieve data from and write data into SQL Server

·         Microsoft.SqlServer.Server namespace provides the classes and attributes for supporting the managed CLR routines

·         An object called ‘SqlContext’ represents the connection instantiated by the CLR stored procedure and manages passing of parameters

·         ‘SqlContext’ is defined in the Microsoft.SqlServer.Server namespace

·         To create a CLR stored procedure

o       Create a Database Project in Visual Studio

o       Add a database reference

o       Add à Stored procedure to the project

·         The steps for creating the CLR stored procedure is explained in my blog here

·         The default entries created when you add a CLR stored procedure  using visual studio are shown below. (Stored procedure name is given as ‘GetSales’):

 

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 GetSales()

    {

        // your code here

    }

};

 

·         The [Microsoft.SqlServer.Server.SqlProcedure] attribute specifies that this method is a stored procedure

·         The method GetSales() is static, as it needs to be invoked without instantiation

·         The namespace System.Data.SqlTypes provides all the data types that correspond to each of the SQL Server data types (Example: SqlTypes.SqlDateTime, SqlTypes.SqlInt32 etc.)

 

CLR Stored Procedure Parameters

 

·         The parameters are passed by specifying them in the stored procedure method. Example is given below

 

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void GetSales(SqlDateTime StartDate, SqlInt32 Days)

    {

        // your code here

    }

·         Output parameters can be passed by using the ref keyword

 

SqlPipe

 

·         SqlPipe is an object that allows us to send data or commands to be executed from a CLR routine back to the caller.

 

·         Send() method of the SqlPipe is used to send the data which has 3 overloads

            Send(string message)

            Send(SqlDataRecord record)

            Send(SqlDatareader reader)

     

 

·         Send() methods can be called any number of times during the course of a CLR stored procedure.

 

Example:

      command.CommandText = “Select * from MonthlySales”;

      SqlDataReader reader = command.EndExecuteReader();

      SqlContext.Pipe.Send(reader);

 

·         ExecuteAndSend() method of the SqlPipe does take care of the execution and subsequent return of the result back to the caller. So the last 2 lines of the above example can be replaced with:

SqlContext.Pipe.ExecuteAndSend(command);

 

 

·         CLR stored procedure can return multiple result sets and multiple messages or errors

 

Error Handling

 

·         Use Try{…} Catch{..}

·         To throw custom exceptions, use RAISEERROR

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s