Programming Microsoft SQL Server 2005 : CLR Stored Procedures and Server-Side Data Access

1/29/2011 6:36:58 PM
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.


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
public static void spContacts()
SqlConnection conn = new SqlConnection("context connection=true");
SqlCommand cm = new SqlCommand("Select * from Person.Contact", conn);

SqlDataReader dr = cm.ExecuteReader();
SqlContext.Pipe.Send("Starting data dump");
SqlContext.Pipe.Send("Data dump complete");

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.


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
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));

drc.SetInt32(0, (Int32)cm.ExecuteScalar());

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.


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


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.

CLR Stored Procedure Usage Guidelines

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.

  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 3) - Using the Resource Governor
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 2)
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 1)
  •  Reporting Services with SQL Azure : Deploying the Report & Creating a Subreport
  •  Reporting Services with SQL Azure : Creating the Report Design
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 2) - Using RANK, DENSE_RANK and NTILE
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 1) - Using ROW_NUMBER
  •  SQL Server 2008 : Demystifying Data Types - Computed Columns
  •  Programming Microsoft SQL Server 2005: Overview of SQL CLR - Visual Studio/SQL Server Integration
  •  Programming Microsoft SQL Server 2005: DDL Triggers and Notifications
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone