SQL CLR stored
procedure code runs in an instance of the .NET CLR that is hosted by SQL
Server itself; it is not called as an external process, as COM-based
extended stored procedures (XPs) would be. Because SQL CLR code runs in
the context of the server, it treats objects in the database as native, local objects, more or less. Likewise, it must treat the client that calls it as remote.
This contextual environment is, in effect, the opposite of that under
which client and middle-tier ADO.NET code runs. This takes a little
getting used to, but once you’ve mastered thinking about things this
way, SQL CLR code becomes easy to write and understand.
Meanwhile, as .NET has no
intrinsic way of accessing local objects on the server or transmitting
data and messages to the client, you must use a special set of classes
to perform these tasks. These classes are contained in the Microsoft.SqlServer.Server namespace.
Note
As an aside, it is interesting and important to note that the Microsoft.SqlServer.Server namespace is actually supplied by the System.DataSystem.Data also further emphasizes the tight integration between .NET and SQL Server.
Framework assembly. This means that you don’t need to worry about
adding a reference to your project to use this namespace. The
namespace’s location within |
If you’d like, you can think of Microsoft.SqlServer.Server as a helper library for System.Data.SqlClient.
It supplies the SQL CLR code attributes we already mentioned, a few
enumerations, an exception class, an interface, and five classes: SqlContext, SqlPipe, SqlTriggerContext, SqlMetaData, and SqlDataRecord. We’ll cover SqlMetaData and SqlDataRecord at the end of this section, and we’ll cover SqlTriggerContext when we discuss CLR triggers. We’ll cover the SqlContext and SqlPipe objects right now.
At a high level, the SqlContext object, which is static,
provides a handle to the server-side context in which your code runs.
It also has a channel to the client through which you can return data
and text: its Pipe property, which in turn provides access to a properly initiated SqlPipe object.
A SqlPipe object can send data and messages to the calling client though several methods: Send, SendResultsStart, SendResultsRow, SendResultsEnd, and ExecuteAndSend. In the previous code sample, we used the SqlPipe object’s ExecuteAndSend method to implicitly open a connection, call ExecuteReader on an SqlCommand object that uses that connection, and transmit the contents of the resulting SqlDataReader back to the client. Although the implicit work done by ExecuteAndSend might have been convenient, it’s important to avoid such shortcuts in our detailed discussion on SQL CLR programming.
In general, SQL CLR stored procedure code that queries tables in the database must open a connection to that database, use the SqlCommand object’s ExecuteReader method to query the data, and then use one or a combination of the Send methods to send it back. The Send methods do not accept DataSet objects; they accept only SqlDataReader objects, strings, and/or special SqlDataRecord objects. Listing 1, which shows the implementation of the function spContacts from spTest.cs in the sample project, is a representative example of how this is done.
Listing 1. spContacts from spTest.cs
[SqlProcedure]
public static void spContacts()
{
SqlConnection conn = new SqlConnection("context connection=true");
SqlCommand cm = new SqlCommand("Select * from Person.Contact", conn);
conn.Open();
SqlDataReader dr = cm.ExecuteReader();
SqlContext.Pipe.Send("Starting data dump");
SqlContext.Pipe.Send(dr);
SqlContext.Pipe.Send("Data dump complete");
dr.Close();
conn.Close();
}
|
For this code to work, we need to use both the Microsoft.SqlServer.Server and System.Data.SqlClient namespaces (and if you look in the sample project rather than Listing 1, you’ll see that we have). This is because any conventional ADO.NET objects we might use, such as SqlConnection, SqlCommand, and SqlDataReader, are supplied to us from System.Data.SqlClient, just as they would be in a conventional client application or middle-tier assembly. As already discussed, we need the Microsoft.SqlServer.Server namespace in order to use objects such as SqlContext and SqlPipe. The stored procedure template in Visual Studio SQL Server projects includes the using statement for Microsoft.SqlServer.Server and System.Data.SqlClient automatically.
Note
Readers who worked with early beta versions of SQL Server 2005 might recall a System.Data.SqlServer
library, which in effect supplied all conventional and server-side
ADO.NET objects necessary to write SQL CLR code. This hybrid library was
eliminated and replaced with the dual-library approach later in the
beta process. |
Although server-side code uses SqlClient objects, it does so in a specialized way. For example, notice that the context connection=true connection string passed to the SqlConnection
object’s constructor. This essentially instructs ADO.NET to open a new
connection to the database in which the CLR assembly resides. Notice
also the second call to the SqlContext.Pipe object’s Send method. Here, the SqlDataReader parameter overload of the SqlPipe object’s Send method is used to push the contents of the SqlDataReader back to the client. You can think of this method as performing a while (dr.Read()) loop through the SqlDataReader and echoing out the values of each column for each iteration of the loop, but instead of having to do that work yourself, the Send method does it for you.
Before and after the SqlDataReader is piped, we use the String parameter overload of the Send
method to send status messages to the client. When this stored
procedure is run in Management Studio, the piped text appears on the
Results tab of the query window when you use the Management Studio
Results To Text option and on the Messages tab when you use the Results
To Grid option.
The rest of the listing contains typical ADO.NET code, all of it using objects from the SqlClient
provider. And that illustrates well the overall theme of SQL CLR
programming: Do what you’d normally do from the client or middle tier,
and use a few special helper objects to work within the context of SQL
Server as you do so.
Piping Data with SqlDataRecord and SqlMetaData
We mentioned that the SqlPipe object’s Send method can accept an object of type SqlDataRecord, and we mentioned previously that Microsoft.SqlServer.Server provides this object as well as an object called SqlMetaData.
You can use these two objects together in a CLR stored procedure to
return a result set one row at a time, instead of having to supply the SqlPipe object’s Send method with an SqlDataReader. This allows (but does not require) you to inspect the data before sending it back to the client. Sending SqlDataReader objects prevents inspection of the data within the stored procedure because SqlDataReader objects are forward-only result set structures. Using the ExecuteAndSend method and an SqlCommand object has the same limitation.
The SqlDataRecord
object permits .NET code to create an individual record/row to be
returned to the calling client. Its constructor accepts an array of SqlMetaData objects, which in turn describe the metadata for each field/column in the record/row.
Listing 2, which shows the implementation of function spContactCount from spTest.cs in the sample project, illustrates how to use SqlPipe.Send together with SqlDataRecord and SqlMetaData objects to return a single-column, single-row result set from a stored procedure.
Listing 2. spContactCount from spTest.cs
[SqlProcedure()]
public static void spContactCount()
{
SqlConnection conn = new SqlConnection("context connection=true");
SqlCommand cm = new SqlCommand("Select Count(*) from Person.Contact", conn);
SqlDataRecord drc = new SqlDataRecord(new SqlMetaData("ContactCount", SqlDbType.Int));
conn.Open();
drc.SetInt32(0, (Int32)cm.ExecuteScalar());
SqlContext.Pipe.Send(drc);
conn.Close();
}
|
The code declares variable drc as a SqlDataRecord object and passes its constructor a single SqlMetaData object. (Passing a single object rather than an array is permissible if the SqlDataRecord object will only have a single field/column.) The SqlMetaData object describes a column called ContactCount of type SqlDbType.Int.
Note
The SqlDbType enumeration is contained within the System.Data.SqlTypes namespace. The SQL Server Stored Procedure template inserts a using statement for this namespace. If you are creating SQL CLR code without using this template, you should add the using statement yourself. |
The rest of the code is rather straightforward. First, a context connection and command are opened and a SELECT COUNT(*)
query is performed against the AdventureWorks Person.Contact table.
Because the query returns a single scalar value, it is run using the SqlCommand object’s ExecuteScalar method. Next, the value returned by ExecuteScalar is casted into an integer and that value is loaded into field/column 0 (the only one) of the SqlDataRecord object using its SetInt32SqlDataRecord is then piped back to the client using the SqlContext object’s Send method. method. The
Note
If we wanted to send back multiple SqlDataRecord objects, we would send the first one using the SqlContext object’s SendResultsStartSqlDataRecord objects using the SendResultsRow method. We would call the SendResultEndSqlDataRecords had been sent. method and then send all subsequent method after all |
Once the stored
procedure has been deployed (the techniques for which we will discuss
shortly), you can execute it from SQL Server Management Studio as you
would any other stored procedure. Although the result is a single value,
it is presented as a column and the column name ContactCount is shown on the Results tab of the query window. Keep in mind that this COUNT(*) query result could have been returned without using the SqlMetaData and SqlDataRecord objects; the sample is provided to demonstrate the use of these objects as an alternative to piping SqlDataReader objects and text to the client.
It’s important to
understand how to perform data access and retrieval in CLR stored
procedures. As a .NET developer, you already know how to do more
computational tasks within your code, so our samples illustrate
server-side data access more than anything else. As proof-of-concept
code, these samples are completely adequate.
Meanwhile, you should
avoid writing CLR stored procedures that merely perform simple “CRUD”
(Create, Retrieve, Update, and Delete) operations. Such tasks are better
left to conventional T-SQL stored procedures, which typically perform
these operations more efficiently than ADO.NET can. CLR stored
procedures work well when you need to perform computation on your data
and you need the expressiveness of a .NET language to do so (where such
expressiveness is missing from T-SQL).
For example,
implementing a “fuzzy search” using business logic embedded in .NET
assemblies to determine which data has an affinity to other data is a
good use of SQL CLR stored procedures. Regular-expression-based data
validation in an update or insert stored procedure is another good
application of SQL CLR integration. As a general rule, straight data
access should be left to T-SQL. “Higher-valued” computations are good
candidates for SQL CLR integration.
