SQL Server "Yukon" and the CLR: Using Server Data

In my previous
article, I showed you how to write a very simple function in Visual Basic
.NET and then call it from T-SQL code in SQL Server "Yukon." But that's only
part of the CLR integration story for the next version of SQL Server. One
important part of the story that I left out is the in-process managed provider,
an ADO.NET provider that CLR functions can use to talk directly to the instance
of SQL Server that invoked them. In this article, I'll show you some of the
basics of using this plumbing.

A word of caution before I begin, though: Microsoft is giving us a public
look at Yukon at a very early stage in its development. Though it's been
demonstrated at the PDC and copies are in the hands of many beta testers, this
is far from final code. Likely many details will change on the way to the final
product, including namespaces, attribute names, and so on. But even though I
would be astounded if the code from this article were to run with the release
version of the product, it seems likely that the general patterns of working
with SQL Server and the CLR will remain intact. It's time to start thinking
about what you could do with this, not time to start writing code for
production.

Using the SqlContext Object

When you install SQL Server "Yukon", it includes an assembly with the
System.Data.SqlServer namespace. This is the in-process managed provider: a new
ADO.NET provider whose task it is to communicate back from the CLR to SQL
Server. But it doesn't communicate with just any SQL Server (there's already
System.Data.SqlClient for that). Instead, when you load CLR code into Yukon (by
declaring it as an assembly), the in-process managed provider lets you connect
directly to the server that's hosting your code. You can use this to retrieve
data from the server or to send data to the server.

Here's a simple first example, in the form of a user-defined function that
uses data from the server instance that calls it:

If you've used ADO.NET to work with SQL Server in the past, this code should
look very familiar to you. The key difference is that this code doesn't use a
SqlConnection object. Instead, it starts its work with the SqlContext object,
which you can think of as a SqlConnection that points directly back to the
calling database. In this case, I've used the SqlContext object to give me a
SqlCommand, and then executed a SELECT statement in that command. The results
are used as the value of the function.

After compiling the assembly, I can use it within SQL Server "Yukon" like
this (refer to the first article in the series for more details):

Related Articles

Note that these statements need to be run in the Northwind sample database to
work, because the VB .NET code is expecting to find one of the Northwind tables
when it calls back through the SqlContext object.

Using the SqlPipe Object

A second important object is the SqlPipe object. This is the key to sending
data back to SQL Sever "Yukon" from your CLR code. You can think of the SqlPipe
object as something like the ASP.NET Response object; anything you drop into the
SqlPipe comes out the other end in the calling T-SQL code. For example, to write
a stored procedure in the CLR, you use a SqlPipe object to transmit the results
back to the server. I'll add a second member to the Products class to
demonstrate how this works, with a few extras thrown in for good measure:

This code introduces a few new things. First, there's the SqlMethod
attribute, which tells Yukon to treat this member as a stored procedure
(assuming that it's properly registered on the database side of things). The
SqlPipe object comes directly from the SqlContext object, giving the code a
pipeline back to the calling database. But in this particular case, I'm also
opening a connection to another database. Note that I'm using objects in
the System.Data.SqlClient namespace for this, and that I have to use their
fully-qualified names so that the compiler knows I'm using the standard SQL
Server provider rather than the in-process provider.

At the end of the procedure, I call the Send method of the SqlPipe object to
send the results back to the calling T-SQL code. The send method has several
overloads; it can accept a string, a SqlError object, or an object that
implements ISqlReader or ISqlRecord. In this case, the standard SqlDataReader
class implements ISqlReader.

You'll see that I've added an extra clause to the CREATE ASSEMBLY statement
here. By default, an assembly registered with SQL Server "Yukon" doesn't have
permission to use resources outside of the local database instance. This will
block any use of the System.Data.SqlClient namespace (among many other
operations). By using WITH PERMISSION_SET = EXTERNAL_ACCESS, I'm telling SQL
Server "Yukon" that I want to allow the assembly to access external resources.
There's also another version, WITH PERMISSION_SET = UNSAFE, for running code
that can't be verified; you should reserve this for very exceptional
circumstances because it could represent a large security hole.

The CREATE PROCEDURE statement is very similar to CREATE FUNCTION. After
creating the procedure, I can just run it, like any other stored procedure.

Looking Forward

As always with new code, there's the question of where you might actually
want to use this stuff. I've already discussed some of the reasons you might
want to move procedures to managed code: speed and complexity, for example, or
access to resources outside of SQL Server. What catches my eye in these examples
is the SqlPipe object, and its ability to return anything that implements
IDataReader. Implementing an interface is pretty simple in .NET (and it will get
even easier in Visual Studio .NET "Whidbey"), so this gives us the ability to
return just about any data as the results of a SQL Server stored procedure.
Imagine a result set of Registry keys, or Active Directory objects, or IIS log
file records, or...well...just about anything that you could represent in rows
and columns. I don't think anyone knows exactly what CLR code will be used for
in production deployments of SQL Server "Yukon," but with the flexibility and
power of this connection I'm sure the results will be interesting indeed.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his MCAD 70-305, MCAD
70-306, and MCAD 70-310 Training Guides from Que Publishing. When he's not
writing code, Mike putters in the garden on his farm in eastern Washington
state.

Please enable Javascript in your browser, before you post the comment! Now Javascript is disabled.