\nIn the new Feb CTP release, how your implement a table-valued function in SQLCLR has been re-architected. This is in the readme (CTPNotes) This was done because implementing ISqlReader was quite complicated and overkill for most scenarios. Chapter 3 of our book "A First Look at SQL Server 2005 for developers" contains a very simple TVF (Bernoulli) implemented using ISqlReader. It contains over 400 lines of code. Many of the methods are stubbed-out because they are never used, but must exist to satify the interface definition. Using the new implementation this method would be less than 15 lines of code.\n<\/p>\n
\nThe new TVF implementation requires three pieces:
\n1. The SqlFunction attribute with the new field FillRowMethodName.
\n2. This attribute is applied to a method that returns either IEnumerable or IEnumerator.
\n3. FillRowMethodName points to a DIFFERENT method (in the same class) that has a special signature.\n<\/p>\n
\nThe methods in steps #2 and #3 have to be public static. Many of the collection classes in the BCL (e.g System.Array) implement IEnumerable or IEnumeration already, or you can write your own implementation.\n<\/p>\n
\nThe FillRowMethodName method has the following signature:\n<\/p>\n
\npublic static void FillIt(Object o, out int col1 , out int col2…)
\n where the first arg is object returned by method in step #2
\n where the varargs arguments (col1, col2….) are the columns that will be returned.\n<\/p>\n
\nMoveNext is called on the underlying IEnumerator (in each case) until it returns false. Each time MoveNext returns a value, the FillRowMethod is called. This generates the rows. The number of columns is determined by the exact signature of the FillRowMethod. In this example, a 2-column table is returned.\n<\/p>\n
\nInterestingly, the 2-nth arguments in your FillRowMethohd must be declared as "out" variables in C#. In my cursory testing, if they are declared as "ref", the method failed with the error: "argument n cannot be NULL" when the TVF implementation calls your FillRowMethod. This is interesting for VB.NET programmers because there is no direct variable qualifier keyword that corresponds to C#'s out. Or is there?\n<\/p>\n
\nWhen .NET was first released a friend of mine, Jose Mojica, published "The C# & VB.NET Conversion Pocket Reference". And it names the following VB.NET equivalent for "out":\n<\/p>\n
\nImports System.Runtime.InteropServices
\n' signature of a FillRowMethod
\nShared Sub FillIt(o as Object, <Out()> ByRef col1 as Integer, <Out()> ByRef col2 as Integer…)\n<\/p>\n
\nWorks great, Jose, my VB.NET TVF is working fine. If you're doing cross language work in .NET, I highly recommend this book.<\/p>\n","protected":false},"excerpt":{"rendered":"
In the new Feb CTP release, how your implement a table-valued function in SQLCLR has been re-architected. This is in the readme (CTPNotes) This was done because implementing ISqlReader was quite complicated and overkill for most scenarios. Chapter 3 of our book "A First Look at SQL Server 2005 for developers" contains a very simple […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,38],"tags":[],"class_list":["post-960","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sqlclr"],"yoast_head":"\n