Writing CLR Stored Procedures in C# - Returning Data (Part 2)

By Bill Graziano on 23 June 2005 | Tags: Stored Procedures , CLR

In Part 1 I covered a simple stored procedure that printed the current date. In this article I'll cover writing a stored procedure to return a recordset back to the client. In the process we'll learn a little bit more about .NET and C# including the SqlConnection, SqlCommand and SqlDataReader classes.

If you're following along in Visual Studio open up the solution you created in the previous article. If you didn't save it that's not a problem. Right click on the Project and choose Add Stored Procedure. Name the file GetAddressTypes and click Ok. As before it creates a shell of a method called GetAddressTypes. Notice the class declaration:

public partial class StoredProcedures

In the last article, the method we created was also in the StoredProcedures class. In previous versions of .NET a class had to be contained in a single file. In .NET 2.0 a class can be split over multiple files if you use the partial keyword. This lets us put each method in its own file if we want (and makes my article writing easier).

At a minimum you need to make sure you reference the following namespaces:

using System;
using System.Data; 
using System.Data.SqlClient; 
using Microsoft.SqlServer.Server;

In earlier beta releases of SQL Server 2005 you used a different set of class libraries when you called SQL Server from inside a stored procedure than when you called SQL Server from a client application. With the April CTP we now use the "client" libraries in both cases. Much of the code in this article will look very familiar to application developers.

The first thing we need is a connection object to connect to SQL Server. We declare that like this:

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

This creates a variable of type SqlConnection called conn. This class resides in the System.Data.SqlClient namespace in the .NET Framework. One of the properties of a SqlConnection is ConnectionString. We reference this using the notation VariableName.PropertyName. You may be more familiar with connection string that look like this:

Data Source=YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;

Using the "Context Connection" connection string tells the SqlConnection object to connect to the existing context inside SQL Server. Since this code will run inside a stored procedure inside SQL Server we don't need to "relogin".

Now we need a SqlCommand object to actually run a SQL statement. That looks like this:

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"SELECT AddressTypeID, [Name] 
    FROM Person.AddressType 
    Order by AddressTypeID";

The SqlCommand object also resides in the System.Data.SqlClient namespace. This creates a new SqlCommand variable named cmd. The first property we set for this variable is the Connection property. The Connection property will only accept objects of type SqlConnection. So we assign conn, our SqlConnection variable, to the Connection property of cmd, our SqlCommand variable.

The CommandText property holds the SQL statement that we wish to execute. Remember that in C# each statement is ended by a semi-colon. This means that statements can wrap multiple lines. In this case it's my string that wraps multiple lines. In C# you can embed special escape characters inside a string by using a backslash to "escape" them. If you ran the following code

string s = "First Line\r\nSecondLine";

and printed the result it would appear on two lines. \r and \n are the character escapes for carriage return and line feed. Placing an @ sign in front of a string says to ignore any escape characters inside the string literal. It also allows us to just hit the Enter key and have the string continue on the next line.

Now that we have our variables setup we can execute the our SQL statement. That code looks like this:

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);

rdr.Close();
conn.Close();

The first step is to open the connection to our SQL Server. We this by calling the Open method of our SqlConnection variable. We're going to store these results in something called a SqlDataReader. This is used to store a stream of read-only data from SQL Server. In later articles we'll look at how to step through the individuals rows in the result set. One of the methods of our SqlCommand variable is ExecuteReader. This method executes the SQL in the SqlCommand object and returns the result as a SqlDataReader. Other methods allow you to execute the SQL statement and not return any values or return only a single scalar value. We'll discuss those in a future article.

In the last article we used a SqlPipe object to send data back to our client. We do the same here but we do it a little differently. The Send method of the SqlPipe class is defined as a static method. static is a C# keyword that says we don't need to instantiate an object of type SqlPipe to call the Send method. In the previous article, the Send method accepted a string and returned that back to the client. The Send method can also accept a SqlDataReader as a parameter. This has the effect of sending the result of the query back to the client as a rowset just like if we had directly executed the SQL statement. Having a method that accepts multiple different types of parameters is called overloading.

Finally we have to clean up our objects. First we close the SqlDataReader and then we close the SqlConnection. We use the Close method in both of these cases. The final method should look like this:

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

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"SELECT AddressTypeID, [Name] 
    FROM Person.AddressType 
    Order by AddressTypeID";

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);

rdr.Close();
conn.Close();

If you Deploy this Project to SQL Server it will create a stored procedure called GetAddressTypes that we can execute just like any other stored procedure. It will return a resultset just like any other stored procedure. That looks like this:

EXEC GetAddressTypes


AddressTypeID Name
------------- ----------------------------
1             Billing
2             Home
3             Main Office
4             Primary
5             Shipping
6             Archive

(6 row(s) affected)

That gives us a very basic stored procedure to return a record set back to the client. There are some tricks we can use to simplify our code though. We can rewrite our method to look like this:

SqlConnection conn = new SqlConnection("Context Connection=true");
SqlCommand cmd = new SqlCommand(@"SELECT AddressTypeID, [Name] 
    FROM Person.AddressType 
    Order by AddressTypeID", conn);

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);

rdr.Close();
conn.Close();

We used the following two short cuts to shrink the code: When a SqlConnection is created it can accept the connection string as a parameter. When a new object is created (in this case a SqlCommand object) its constructor method is executed. Even when you create an object without any parameters it still have a constructor method that runs. I'll cover constructors and overloading in more detail in a future article. Notice that the SqlCommand object can accept both the SQL statement and the connection as parameters when it's created.

In this article we wrote a basic stored procedure that returned a record back to the calling program. We discussed the SqlConnection, SqlCommand and SqlDataReader objects. Next we'll look at different ways to deploy the stored procedure to SQL Server.