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:
Imports System.Data.SqlServer Imports System.Data.Sql Namespace NorthwindExtras Public Class Products <SqlFunction(DataAccess:=DataAccessKind.Read)> _ Public Shared Function InventoryValue( _ ByVal ProductID As Integer) As Double ' Create a SqlCommand object pointing at the parent database Dim cmd As SqlCommand = SqlContext.GetCommand() cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT UnitsInStock * UnitPrice " & _ "FROM Products WHERE ProductID = " & CStr(ProductID) ' Execute the command and return the result InventoryValue = CDbl(cmd.ExecuteScalar()) End Function End Class End Namespace
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):
CREATE ASSEMBLY NorthwindExtras FROM 'C:\NorthwindExtras\bin\NorthwindExtras.dll' GO CREATE FUNCTION InventoryValue(@ProductID INT) RETURNS FLOAT EXTERNAL NAME NorthwindExtras:[NorthwindExtras.Products]::InventoryValue GO SELECT dbo.InventoryValue(1) GO ---------------------- 702 (1 row(s) affected)
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:
<SqlMethod()> _ Public Shared Sub GetProspects(ByVal State As String) ' Set up a pipeline for the stored procedure results Dim sp As SqlPipe = SqlContext.GetPipe() ' Connect to a different SQL Server database Dim cnn As System.Data.SqlClient.SqlConnection = _ New System.Data.SqlClient.SqlConnection cnn.ConnectionString = _ "Data Source=(local);Initial Catalog=pubs;Integrated Security=SSPI" cnn.Open() ' Retrieve some data Dim cmd As System.Data.SqlClient.SqlCommand = cnn.CreateCommand() cmd.CommandText = "SELECT au_fname + ' ' + au_lname AS Prospect " & _ "FROM authors WHERE state = '" & State & "'" Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader() ' And return the results sp.Send(dr) cnn.Close() End Sub
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.
Registering and using this stored procedure looks like this:
CREATE ASSEMBLY NorthwindExtras FROM 'C:\NorthwindExtras\bin\NorthwindExtras.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS GO CREATE PROCEDURE GetProspects (@State nvarchar(2)) AS EXTERNAL NAME NorthwindExtras:[NorthwindExtras.Products]::GetProspects GO GetProspects 'CA' GO varchar ------------------------------------------------------------- Johnson White Marjorie Green Cheryl Carson Michael O'Leary Dean Straight Abraham Bennet Ann Dull Burt Gringlesby Charlene Locksley Akiko Yokomoto Dirk Stringer Stearns MacFeather Livia Karsen Sheryl Hunter Heather McBadden (15 row(s) affected)
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.
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.