You probably saw some of the news coming out of Microsoft’s recent
Professional Developers Conference. Microsoft showed off three major
products that will ship over the next two to three years:
- “Longhorn”, the next major version of Windows.
- “Whidbey”, the next version of Visual Studio .NET.
- “Yukon”, the next version of SQL Server.
Of course, all three of these products can work together. In particular, SQL
Server “Yukon” can execute code written with Visual Studio .NET “Whidbey”. But
how does that work? In this article, I’ll show you the basics of calling a
Whidbey function from Yukon, and help you think about where this capability
might come in handy.
A: Writing the Code
Yukon can execute four different types of .NET routines:
- Scalar-valued user-defined functions
- Table-valued user-defined functions
- User-defined procedures
- User-defined triggers
The simplest of these .NET routines is the scalar-valued user-defined
function. “Scalar-valued” means that it returns a single scalar type such as an
integer or a string. The function can have zero or more inputs. Such functions
are implemented in .NET as static methods of a class. For example, here’s some
VB .NET code for a class that exposes two functions that can be used as
scalar-valued user-defined functions:
Namespace SimpleMath Public Class Conversions Public Shared Function MilesToKm(ByVal Miles As Double) As Double MilesToKm = 1.609344 * Miles End Function Public Shared Function KmToMiles(ByVal Kilometers As Double) As Double KmToMiles = 0.621371192 * Kilometers End Function End Class End Namespace
Remember, Visual Basic .NET uses the
Shared keyword to indicate
a static method.
As a first step, then, I saved this code in a Visual Basic .NET class library
named SimpleMath. I set the assembly name to SimpleMath and the root namespace
to an empty string (you can make both of these settings in the Assembly section
of the project's property pages). Compiling this assembly gives me code that I
can call from SQL Server...after a bit more work.
B: Registering the Functions
The next step is to tell SQL Server that these functions exist, and that it's
OK to call them. SQL Server can't just execute arbitrary code in any old .NET
asembly. With the advent of Yukon, you'll discover T-SQL extensions to handle
this task. First, you need to register the assembly:
CREATE ASSEMBLY SimpleMath FROM 'C:SimpleMath1SimpleMath1binSimpleMath.dll'
Next, you need to tell SQL Server about the particular functions within the
assembly. This requires one call to
CREATE FUNCTION for each
function in the assembly:
CREATE FUNCTION MilesToKm(@Miles FLOAT) RETURNS FLOAT EXTERNAL NAME SimpleMath:[SimpleMath.Conversions]::MilesToKm GO CREATE FUNCTION KmToMiles(@Kilometers FLOAT) RETURNS FLOAT EXTERNAL NAME SimpleMath:[SimpleMath.Conversions]::KmToMiles GO
CREATE FUNCTION call may be familiar from SQL Server 2000,
where you can
use it to create a T-SQL function. The new syntax is the
which specifies where to find the function in this format:
The assembly name here, of course, is the one that was registered with the
CREATE ASSEMBLY call.
C: Calling the Functions
All that's left is to call the functions. This uses the same syntax that
already exists for calling user-defined functions in T-SQL:
SELECT dbo.MilesToKm(27.5), dbo.KmToMiles(42)
And here are the results:
---------------------- ---------------------- 44.25696 26.097590064 (1 row(s) affected)
That's it! Although the internal plumbing to use CLR functions from T-SQL
must be quite complex, the SQL Server team has done a great job of making the
syntax easy to use.
So What Good is It?
Of course, implementing these simple conversion functions in VB .NET, while a
good demonstration of how to hook everything up, doesn't really make a lot of
sense. SQL Server is perfectly capable of doing multiplication itself, and
implementing these functions in T-SQL would involve less overhead. But there are
many things you can do with the .NET languages that are difficult or impossible
in T-SQL. Here are a few reasons that you might actually call .NET code from SQL
- To work with the event log, WMI objects, or other system resources
- To use the cryptographic functions built into the .NET Framework Class
- To perform complex mathematical calculations
- To move SQL Server data to Web pages or disk files
With the advent of .NET code in SQL Server, it's going to be possible to
perform a vast array of new tasks from within SQL Server applications. There's a
lot more to this capability than I've shown you here; in particular, I've
ignored both the well-designed security system and the ways in which .NET code
can call back into SQL Server to obtain more data. If you're a SQL Server
developer, you should definitely keep an eye on this stuff as "Yukon" progresses
from beta code to release.
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