SQL Server 2012 : Exploring SQL CLR - Your First SQL CLR Stored Procedure, CLR Stored Procedures and Server-Side Data Access

1. Your First SQL CLR Stored Procedure

Although SQL CLR programming can get quite complex and involved, in reality it offers a simple model that any .NET developer can use with high productivity in relatively short order. That’s because the crux of SQL CLR functionality is nothing more than the ability of SQL Server 2012 to load .NET assemblies into your database and then allow you to use the functions and types within the assembly as you define your columns, views, stored procedures, triggers, and functions.

To gain a good understanding of SQL CLR integration, you must examine its features and techniques carefully. Before doing so, however, let’s quickly walk through an end-to-end scenario for creating and executing a SQL CLR stored procedure. This will make it easier for you to understand the individual features as we describe them.

Strictly speaking, any .NET class library assembly (in certain cases, using appropriate .NET code attributes in its classes and functions) can be loaded into your database with a simple T-SQL statement. To see how easily this works, open a query window in SSDT or SSMS using a connection to the AdventureWorks2012SQLCLRDemo.dll is located in the VS\SQLCLRDemoManual\SQLCLRDemo\bin\Debug subfolder. If the sample code parent folder were C:\Demos, you would load the assembly into the AdventureWorks2012 database with the following T-SQL command: sample database. In the sample code folder, confirm that the file

CREATE ASSEMBLY SQLCLRDemo
 FROM 'C:\Demos\VS\SQLCLRDemoManual\SQLCLRDemo\bin\Debug\SQLCLRDemo.dll'

There are other syntax options for the CREATE ASSEMBLY command, but for now we’ll focus on the preceding limited usage.

Functions in an assembly that reside within a class and perform local computational tasks and certain types of data access can be easily exposed as SQL Server stored procedures, triggers, or functions. As with conventional T-SQL stored procedures, triggers, and functions, all it takes is a simple T-SQL CREATE PROCEDURE, CREATE TRIGGER, or CREATE FUNCTION statement to make this happen.

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

public partial class Sprocs
{
  public static void spContactsQuick()
  {
    SqlContext.Pipe.ExecuteAndSend(new SqlCommand("SELECT * FROM Person.Person"));
  }
}

The spContactsQuick method is designed to connect to the database in which its assembly has been loaded (AdventureWorks2012), perform a SELECT * against the Person.Person table, and then use special server-side objects to send the data back to the client application. To make this CLR code available via SQL Server as a stored procedure, also called spContactsQuick, you simply execute the following command from an SSMS or SSDT query window:

CREATE PROCEDURE spContactsQuick
 AS EXTERNAL NAME SQLCLRDemo.Sprocs.spContactsQuick

Important

Be sure to enter the Sprocs.spContactsQuick portion of the command verbatim. This phrase is case sensitive.

To test the SQL CLR stored procedure, run it from an SSMS or SSDT query window as you would any conventional stored procedure, as shown here:

EXEC spContactsQuick

Or simply:

spContactsQuick

When the execution completes, you should see the contents of the Person.Person table in the Results tab of the query window.

As you can see from this rather trivial example, writing a CLR stored procedure can be very easy and is a lot like writing client-side or middle-tier code that performs data access using ADO.NET. The biggest differences involve the provision of a database connection and the fact that the data must be “piped” back to the client rather than merely loaded into a SqlDataReader and returned, manipulated, or displayed through a user interface (UI). The presence of the SqlContext object also differentiates SQL CLR code from conventional ADO.NET data access code. We’ll cover the use of the SqlContext object and its Pipe property in the next section.

The bits of T-SQL and C# code just shown certainly don’t tell the whole SQL CLR story. The use of the ExecuteAndSend method allowed us to skip over a number of otherwise important concepts. There are three ways to deploy assemblies, and you’ve seen only a simplified version of one of those ways. Security considerations must be taken into account, and we haven’t even begun to look at triggers, functions, aggregates, or UDTs. So although the example showed how easy SQL CLR programming can be, we’ll now take our time and show you the nooks and crannies.

2. CLR Stored Procedures and Server-Side Data Access

Our previous “quick and dirty” sample looked at CLR stored procedure development, but we need to cover that topic more thoroughly now. We’ve already covered the mechanics of writing and deploying a stored procedure, but let’s back up a bit to try and understand how CLR stored procedures work from a conceptual standpoint.

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 Component Object Model (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. As such, 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. There, communicating with the database requires a remote connection (even if the database is physically on the same computer) and the ADO.NET code runs locally. The SQL CLR reversal of 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.Data.dll .NET 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 System.Data.dll also further emphasizes the tight integration between .NET and SQL Server.

If you want, 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 other classes: SqlContext, SqlPipe, SqlTriggerContext, SqlMetaData, and SqlDataRecord.

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 opened and initialized 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 preceding code sample, you used the SqlPipe object’s ExecuteAndSend method to implicitly open a connection, call ExecuteReader on a 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 for us to get started quickly, it’s important to avoid such shortcuts in detailed discussions of 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 DataSetSqlDataReader objects, strings, and special SqlDataRecord objects. Example 1, which shows the implementation of the function spContacts from spTest.cs in the SQLCLRDemo sample project, is a representative example of how this is done. objects; they accept only

Example 1. spContacts from spTest.cs.

[SqlProcedure]
public static void spContacts()
{
    SqlConnection conn = new SqlConnection("context connection=true");
    SqlCommand cm = new SqlCommand("SELECT * FROM Person.Person", 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();
}

Note

The implementation of spContacts in the SQLCLRDemoManual project is identical to that shown in Example 1, but is not decorated with the SqlProcedure code attribute.

Because the Person.Person table includes xml columns, and because such columns can cause a slowdown in SQL CLR stored procedures, the query in the sample code for spContacts uses a column list (which excludes the xml columns) in the SELECT clause rather than the * wildcard. We retained the SELECT * syntax in the printed code for simplicity and terseness.

For this code to work, you need to use both the Microsoft.SqlServer.Server and System.Data.SqlClient namespaces . This is because any conventional ADO.NET objects you might use, such as SqlConnection, SqlCommand, and SqlDataReader, are supplied from System.Data.SqlClient, just as they would be in a conventional client application or middle-tier assembly. And as already discussed, you need the Microsoft.SqlServer.Server namespace in order to use objects such as SqlContext and SqlPipe. The stored procedure template in the SSDT SQL Server Database Project template includes the using statement for Microsoft.SqlServer.Server and System.Data.SqlClient automatically.

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 returning 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 (returned to the consumer), the String parameter overload of the Send method is used to send status messages to the client. When this stored procedure is run, the piped text appears on the Results tab of the query window when you use the Results To Text option in SSMS (Results As Text in SSDT) and on the Messages tab when you use the Results To Grid (Results As Grid in SSDT) 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.ServerSqlMetaData. 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 a 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 a SqlCommand object has the same limitation. provides this object as well as an object named

The SqlDataRecord object permits .NET code to create an individual row to be returned to the calling client. Its constructor accepts an array of SqlMetaData objects, which in turn describes the metadata for each column in the row.

Example 2, which shows the implementation of the spContactCount function from spTest.cs in the SQLCLRDemo 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.

Example 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.Person", 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 have only a single column.) The SqlMetaData object describes a column named 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 will need to 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 Person.Person 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 cast into a .NET Int32 and that value is loaded into column 0 (the only returned column) of the SqlDataRecord object using its SetInt32 method. The SqlDataRecord is then piped back to the client using the SqlPipe object’s Send method.

Note

If you wanted to send back multiple SqlDataRecord objects, you would send the first object using the SqlContext object’s SendResultsStart method and then send all subsequent SqlDataRecord objects using the SendResultsRow method. You would call the SendResultsEnd method after all SqlDataRecord objects had been sent.

Once the stored procedure has been deployed (the techniques for which we will discuss shortly), you can execute it just 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 perform 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, Read, 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 computations on (or using) your data, and you require the expressiveness of a .NET language or the rich functionality provided by the .NET base class libraries to do so (where such expressiveness and base class library support are 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.