This post originated from an RSS feed registered with .NET Buzz by Tim Sneath. Original Post: Yukon Engine: CLR Integration IV Feed Title: Tim Sneath's Blog Feed URL: /msdnerror.htm?aspxerrorpath=/tims/Rss.aspx Feed Description: Random mumblings on Microsoft, .NET, and other topics. Latest .NET Buzz Posts Latest .NET Buzz Posts by Tim Sneath Latest Posts From Tim Sneath's Blog

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:

• Part I: Overview of CLR Integration
• Part II: Creating and Hosting Managed Code in Yukon
• Part III: Accessing and Returning Data from Yukon Managed Code

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:

1. MySqlReader (constructor)
2. ISqlRecord.FieldCount (number of columns in the returned table)
3. ISqlGetTypedData.GetSqlMetaData(int FieldNo) (the schema of the returned table)
4. ISqlReader.Read() (for use in constructs such as reader.Read())
5. IGetTypedData.GetString(int FieldNo) (returns the string in the current row and specified column)
6. ISqlGetTypedData.GetSqlCharsRef(int FieldNo) (ditto, but returns a SqlChars)
7. 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!