Yukon Engine: CLR Integration IV

Article
01/26/2004

After a brief break for Christmas and New Year, it's time to continue our overview of the integration of the Common Language
Runtime into SQL Server "Yukon". Previous articles:

In previous instalments we've seen how Yukon allows you to build stored
procedures and functions in managed code and how to catalog .NET assemblies in
the database. We then looked at how you can use the in-proc data provider to
efficiently access data and return results through the
SqlPipe object.

Until now we've only looked at writing scalar functions (i.e. those that
return a single numerical or string value). SQL Server also supports
table-valued functions (TVFs), which as their name suggests return tables. In
Transact-SQL, you can use a table-valued function in many places where you'd use
a regular table, for example in a FROM clause of a
SELECT statement:

    SELECT *
   FROM fn_calculate_premiums(30000)

The managed function prototype for TVFs is as follows:

    public static System.Data.Sql.ISqlReader Foo();

ISqlReader is an interface implemented by the
SqlDataReader classes in both
System.Data.SqlServer and System.Data.SqlClient.

So how do you return your own data through this interface? If your function
is just doing a bit of data access itself, you might be able to simply do
something like SqlExecutionContext.ExecuteReader()
and return the result back directly. But most of the time you'll want to either
edit data (perhaps adding a column or changing some values) or even create some
brand new data to return. In this case, your only option is to build a new class
that implements ISqlReader and provides custom
implementations of the methods and properties, then returning this back to the
calling application.

ISqlReader itself isn't too bad - it only has a
few properties and methods to override, but it in turn implements
ISqlRecord and
ISqlGetTypedData, both of which require implementation a shed-load of
stuff (I counted sixty separate methods and properties). Suddenly this
seems like a load of work, doesn't it? (Little gripe: I'm hoping there will be a
helper class by the time of Yukon RTM that makes this whole process a good deal
simpler. If we don't write one officially by Beta 2, I'm going to be putting my
own one together since I'm getting tired of implementing this code over and over
again.)

For now, you'll be pleased to know that you can get away with only
implementing 7 of the 60 methods and properties (plus one or two more if you
want your table to contain multiple data types) in order for a TVF to work both
within the Yukon environment and for simple reader.Read()
style iterations elsewhere. If all your table contains is string columns, then
the following will do:

MySqlReader (constructor)
ISqlRecord.FieldCount (number of columns in the returned table)
ISqlGetTypedData.GetSqlMetaData(int FieldNo) (the schema of the
returned table)
ISqlReader.Read() (for use in constructs such as
reader.Read())
IGetTypedData.GetString(int FieldNo) (returns
the string in the current row and specified column)
ISqlGetTypedData.GetSqlCharsRef(int FieldNo)
(ditto, but returns a SqlChars)
ISqlGetTypedData.GetSqlChars(int FieldNo)
(ditto)

Make sure you implement both 6 and 7 -

the MSDN documentation sample is wrong here at present. Unless you're also
returning ints or other data types in which case you've got the appropriate
GetXXX() methods to implement also before your
work is done! For the other methods, you can simply throw a
NotImplementedException. (Incidentally, don't make the mistake I did the
first time round and simply return null following the default prototypes created
by VS.NET when you hit Tab after ISqlRecord -
you'll get spurious problems if any of the other methods are called for some
reason, and you won't be able to pin them down.)

For fun, I wrote a TVF that used the Amazon search service to find books
matching a given ISBN and then return the results in the form of a table. The
code is too long to include here inline, but you can
view it here. If
you want to use it, you'll need to modify the developer token required by Amazon
to one you've registered. Once compiled, you can catalogue this as follows:

    CREATE FUNCTION dbo.fn_get_book_info(@ISBN nvarchar(10))
   RETURNS @BookInfo TABLE 
      (Title nvarchar(200), Author nvarchar(200), Price nvarchar(30),
       Rating nvarchar(10), Rank nvarchar(10))
   AS EXTERNAL NAME YukonCLR:[BookInfo]::GetBookInfo
   GO

Calling the function with a statement such as:

    SELECT * FROM fn_get_book_info('073560505X')

should return a result set.

Playing around with early bits like this is sometimes frustrating and
unrewarding, but it certainly means you get to understand how the product works
and get ahead so that there's not so much to learn when it finally ships!

Yukon Engine: CLR Integration IV

Additional resources