In the first part of this article, we discussed how the CLR integration works in SQL Server 2005. This included writing stored procedures, but we could not do anything interesting in those first examples because we had no access to the data inside the database.

In this second article, we explore how to work with the actual data inside SQL Server.

Using the In-Proc Managed Provider

Data access inside the database is a funny beast. Unlike earlier previews of SQL Server 2005, there are not two separate managed providers.

To access data within the database server, you would use the SqlClient managed provider, just as you would from client-side code.

For example, the following code is what it might look like from the client:

using System.Data.SqlClient;

// ...

using (SqlConnection conn = new SqlConnection("..."))
using (SqlCommand cmd = conn.CreateCommand()) {
  try {
    cmd.CommandText = "...";
    conn.Open();

    using (SqlDataReader rdr = cmd.ExecuteReader()) {
 
      while (rdr.Read()) { 
        // Do something
      }
    } // Reader is disposed
  } finally {
    conn.Close();
  }
} // using statements ensure Disposed is called

Inside SQL Server, this changes to simply ignore the need for a connection. The connection is assumed:

using System.Data.SqlClient;

// ...

using (SqlCommand cmd = new SqlCommand()) {

  cmd.CommandText = "...";

  using (SqlDataReader rdr = cmd.ExecuteReader()) {
 
    while (rdr.Read()) { 
      // Do something
    }
  } // Reader is disposed
} // using statements ensure Disposed is called

Why can we assume the connection? We can make that assumption because we are running this code inside of the context of a connection. When the client calls our code (stored procedure, function, etc.) it is on a connection that is already opened. The managed provider uses the existing connection if a new one is not included. In fact, there is a new connection string form that allows your code to be virtually identical as the client code:

using System.Data.SqlClient;

// ...

using (SqlConnection conn = new SqlConnection("context connection=true;"))
using (SqlCommand cmd = conn.CreateCommand()) {
  try {
    cmd.CommandText = "...";
    conn.Open();

    using (SqlDataReader rdr = cmd.ExecuteReader()) {
 
      while (rdr.Read()) { 
        // Do something
      }
    } // Reader is disposed
  } finally {
    conn.Close();
  }
} // using statements ensure Disposed is called

Using the "context connection=true;" tells the managed provider to use the existing connection. You are still using SqlClient though, so you can create connections to this or other servers as you like:

using System.Data.SqlClient;

// ...

string connectionString = "Server=dbmachine;Database=Northwind;";

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = conn.CreateCommand()) {
  try {
    cmd.CommandText = "...";
    conn.Open();

    using (SqlDataReader rdr = cmd.ExecuteReader()) {
 
      while (rdr.Read()) { 
        // Do something
      }
    } // Reader is disposed
  } finally {
    conn.Close();
  }
} // using statements ensure Disposed is called

The only limitation here is that the assembly must be marked as "EXTERNAL" permission level. Otherwise, you get security failures when trying to create any connection other than the context's connection.

Sending Data to the Client

Now that we can connect to the server and issue commands, you need to figure out how to send data back to the client. Returning data involves using a SqlPipe object. The SqlPipe is an object whose job it is to send results (of all sorts) back to the calling code. In the most basic case, you can tell the pipe to execute and send a single command object:

using (SqlCommand cmd = new SqlCommand()) {

  cmd.CommandText = "...";

  SqlContext.Pipe.ExecuteAndSend(cmd);

} // using statements ensure Disposed is called

One thing to note is the SqlContext object: it's a static object used in SQL Server code to retrieve a small number of static objects like the SqlPipe. We will discuss the SqlContext as we use it in further examples.

The SqlPipe class also supports the notion of sending a DataReader object back to the client:

using System.Data.SqlClient;

// ...

using (SqlCommand cmd = new SqlCommand()) {

  cmd.CommandText = "...";

  using (SqlDataReader rdr = cmd.ExecuteReader()) {

    SqlContext.Pipe.Send(rdr);
 
  } // Reader is disposed
} // using statements ensure Disposed is called

Using the DataReader version of Send returns the entire results of the DataReader from the current point in the result forward. This includes additional result sets, if there are any. In fact, this code example is functionally equivalent to the previous one. You could have moved the record pointer (with DataReader.Read()) before you call Send, and it would only return the current record and everything after it.

Custom Result Sets

On occasion, you want to craft your own result sets to return to the client. SQL Server 2005 supports this by allowing you to returning a new object type called a DataRecord. A DataRecord exemplifies a single record of a result.

Before SQL Server 2005 can return a DataRecord, it needs to know the DataRecord's form (or schema). This is accomplished by creating the schema with SqlMetaData objects. The SqlMetaData class is constructed with a name and a SqlType object, and a number of optional arguments (mostly about adding data constraints, such as string length):

SqlMetaData[] columns = new SqlMetaData[3];
columns[0] = new SqlMetaData("ID", SqlDbType.Int);
columns[1] = new SqlMetaData("Name", SqlDbType.NVarChar, 50);
columns[2] = new SqlMetaData("theDate", SqlDbType.DateTime);

Once you have the schema defined, you can create DataRecord objects:

SqlDataRecord record = new SqlDataRecord(columns);

record.SetInt32(0, 1);
record.SetString(1, "Bob Higgins");
record.SetDateTime(2, DateTime.Now);

SqlContext.Pipe.Send(record);

Note that the constructor for the SqlDataReader object takes an array of SqlMetaData objects to describe the schema. We could add another section after this to return another result:

SqlDataRecord record2 = new SqlDataRecord(columns);
record2.SetInt32(0, 2);
record2.SetString(1, "Jeff Bagmore");
record2.SetDateTime(2, DateTime.Today);

SqlContext.Pipe.Send(record2);

The problem with this approach is that it returns two result sets:

ID          Name          theDate                 
----------- ------------- ----------------------- 
1           Bob Higgins   5/24/2005 3:55:01 PM    
No rows affected.
(1 row(s) returned)
ID          Name          theDate                 
----------- ------------- ----------------------- 
2           Jeff Bagmore  5/24/2005               
No rows affected.
(1 row(s) returned)

If you need to return more than one record, SqlPipe supports this with a SendResultsStart, SendResultsRow, and SendResultsEnd:

SqlDataRecord record = new SqlDataRecord(columns);

record.SetInt32(0, 1);
record.SetString(1, "Bob Higgins");
record.SetDateTime(2, DateTime.Now);

SqlContext.Pipe.SendResultsStart(record);

SqlContext.Pipe.SendResultsRow(record);

record.SetInt32(0, 2);
record.SetString(1, "Jeff Bagmore");
record.SetDateTime(2, DateTime.Today);

SqlContext.Pipe.SendResultsRow(record);

SqlContext.Pipe.SendResultsEnd();

Since we told SQL Server that we think of this as one result set, we now get:

ID          Name                 theDate                 
----------- -------------------- ----------------------- 
1           Bob Higgins          5/24/2005 4:01:50 PM    
2           Jeff Bagmore         5/24/2005               
No rows affected.
(2 row(s) returned)