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.
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.
Quality Management ROI Calculator - Focus on Test Automation
IE 7
Firefox 2.0
