Basic Operations with Stored Procedures

In this section we will focus on support for traditional stored procedure features such as:

• Returning a result set

• Returning the value

• Using input and output parameters

Returning Value

Transact-SQL stored procedures can return int values to the caller. CLR stored procedures can be declared to return no value (void) or to return 2-byte (SQLIntl6 or System.Intl6) or 4-byte integers (SQLInt32 or System.Int32):

 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {      [SqlProcedure]      public static System.Int32 ap_MyFirst()      {          SqlContext.Pipe.Send("Hello world!\n");          return 0;      } }; 

In C#, you must return a value using a return statement. If you are developing a stored procedure in Visual Basic .NET, then to return a value you must use a function, not Sub(routine):

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures     <SqlProcedure()> _ Public Shared Function  ap_MyFirst () As System.Int32        SqlContext.Pipe.Send("Hello world!\n")      ap_MyFirst = 0      End Function End Class 

Tip 

You can also use int as a CLR alias for a 4-byte integer.

Connection Context

It is possible to create a new connection inside of a CLR procedure using System. Data.SqlClient.SqlConnection. A developer would just need to specify connection parameters (such as login info) and open a connection:

 SqlConnection con = new SqlConnection() con.ConnectionString = "Data Source=SQL2005; Initial Catalog=Asset5; Integrated Security=True; "; con.Open(); // Operations that use the connection 

Unfortunately, such a connection would not be part of the caller transaction; it would not have access to temporary tables created on the caller, it would have different Set options, and it would be slower because commands and results would have to go through several additional layers and other similar issues.

This problem is solved by opening a connection that invokes a Transact-SQL statement that starts the CLR procedure. This is called a context connection. You can open such a connection if you specify "Context Connection=true" instead of the regular connection string:

 SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Context Connection=true"; conn.Open(); 

It's even better if you combine the first two lines into a single Using construct with a block:

 using(SqlConnection con = new SqlConnection("context connection=true")) {     con.Open();     // Operations that use the connection } 

However, there are some restrictions on context connections, such as:

• You can have only one such connection open.

• It's not possible to use Multi Active Result Sets (MARS).

• It's not possible to set other connection attributes (once it contains "Context Connection=true").

• It's not possible to cancel requests using SqlCommandCancel. The engine will simply ignore them.

Returning a Result

SqlPipe is the class that contains the Send method that is designed to return a result to the caller. There are three overloaded methods with that name that take as their parameter a string, a record object, or a reader object. You have seen in earlier sections how to return a string. In the following examples, I will focus on returning a tabular result.

I will start by demonstrating the creation of a result based on a reader object. SqlDataReader and SqlCommand classes are part of the core ADO.NET (as well as SqlConnection). The following stored procedure creates a connection and then a command object based on it. The command object will execute the specified batch when the connection is opened. The reader object is created when the ExecuteReader method is invoked on the SqlCommand object. Finally, rows from the reader are returned through SqlPipe using the Send method:

 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 cp_EqTypeList()      {         using (SqlConnection conn = new SqlConnection("Context Connection=true"))       {         SqlCommand cmd = new SqlCommand();         cmd. Connection = conn;         cmd.CommandText = "select * from dbo.EqType"; cmd.CommandType = CommandType.Text;         conn.Open () ;         SqlDataReader rdr = cmd.ExecuteReader();         SqlContext.Pipe.Send(rdr);         rdr .Close () ;       }    } }; 

In Visual Basic .NET, this would be something like the following:

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures  <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub cp_EqTypeListVB()         ' Add your code here         SqlContext.Pipe.Send("Hello world!\n")         Using conn As New SqlConnection("Context Connection=true"             Dim cmd As New SqlCommand()             cmd.Connection = conn             cmd.CommandText = "select * from dbo.EqType"             cmd.CommandType = CommandType.Text             conn.Open()             Dim rdr As SqlDataReader             rdr = cmd.ExecuteReader()             SqlContext.Pipe.Send(rdr)             rdr.Close()         End Using     End Sub End Class 

You can run a stored procedure this way as well. One way is to simply create a batch that contains an Exec statement and the name of a stored procedure. However, the preferred way is to specify that the type of command that you are running is CommandType.StoredProcedure:

 cmd.CommandText = "ap_Eq_List"; cmd.CommandType = CommandType.StoredProcedure; 

In this case you should not put the Exec command in front of the stored procedure name.

You can do the same without connection and reader objects. The pipe contains the ExecuteAndSend method that can consume a SqlCommand object and send the result through the pipe.

 [Microsoft.SqlServer.Server.SqlProcedure] public static void cp_EqType_List2() {    using (SqlCommand cmd = new SqlCommand())     {         //cmd.Connection = conn;         cmd.CommandText = @"SELECT * FROM dbo.EqType";        SqlContext.Pipe.ExecuteAndSend(cmd);     } } 

It is also possible to assemble a table "manually" with results and return it to the caller. I will show you this feature in the next section.

Returning Custom Records

Many of the examples I have shown so far have been artificial/academic because I was obtaining data from Transact-SQL statements and passing them (even with minimal or no transformation) to the caller from CLR. They were meant to demonstrate the usage of one class or the other, not to serve as architecture prototypes. You will often need to do the opposite—to return a custom recordset of values obtained during processing in a CLR procedure to the caller. You can create your records using the SqlDataRecord class and then send them through SqlPipe. Before you create an instance of SqlDataRecord, you need to define its structure as an array of objects of the SqlMetaData class:

 SqlMetaData[] fields = new SqlMetaData[3]; fields [0] = new SqlMetaData("LeaseId", SqlDbType.Int); fields [1] = new SqlMetaData("LeaseVendor", SqlDbType.NVarChar, 50); fields [2] = new SqlMetaData("LeaseNumber", SqlDbType.NVarChar, 50); fields [3] = new SqlMetaData("ContactDate", SqlDbType.DateTime); fields [4] = new SqlMetaData("TotalAmount", SqlDbType.Money); 

When the schema is finished, you will create an instance of SqlDataRecord and populate it with data:

 SqlDataRecord record = new SqlDataRecord(fields); record.SetInt32(0, 1001); record.SetString(1, "LeaseLeaseLease Inc."); record.SetString(2, "123-456-7890"); record.SetDateTime(3, DateTime.Now); record.SetSqlMoney(4, 2000); 

As the last step, you will send the record through the pipe to the caller:

 SqlContext.Pipe.Send(record) ; 

SQL Server will allow you to repopulate the record with different values and send it again through the pipe. However, the result of that action would be two recordsets with one record each (see Figure 12-1).

Figure 12-1: Stored procedure with two custom recordsets

It is possible to get one recordset with two records instead, but you will have to use the SendResultsStart, SendResultsRow, and SendResultsEnd methods of the SqlPipe class:

 // set record SqlDataRecord record = new SqlDataRecord(fields) ; // start record set SqlContext.Pipe.SendResultsStart(record) ; //assemble first record record.SetInt32(0, 1001); record.SetString(1, "LeaseLeaseLease Inc."); record.SetString(2, "123-456-7890"); record.SetDateTime(3, DateTime.Now); record.SetSqlMoney(4, 2000); // send record SqlContext.Pipe.SendResultsRow(record); // assemble second record record.SetInt32(0, 1002); record.SetString(1, "LeaseLeaseLease Inc."); record.SetString(2, "123-456-7891"); record.SetDateTime(3, DateTime.Now); record.SetSqlMoney(4, 4000); // send record SqlContext.Pipe.SendResultsRow(record) ; // send record set SqlContext.Pipe.SendResultsEnd(); 

Parameters in CLR Stored Procedures

By default, CLR procedure parameters are returned by value. The exceptions are Output parameters that must be declared to be by reference. In the case of Visual Basic .NET, this exception is very visible in the method declaration. You also need to include the <Out()> attribute:

 Public Shared Sub ExtPriceSum( <Out()> ByRef value As SqlMoney) 

In the C# method, you simply need to precede the parameter with the out keyword.

 public static void ExtPriceSum(out SqlMoney value)