I Yield for Table-Value functions

One of the coolest set of complimentary features in SQL Server 2005 and Visual Studio 2005 can be found in the ability to code a Table-Value function using C#. In this exercise we will create a simple SQL Function in C# that returns a SQL table. Coding a Table-Value Function requires implementing two methods. The first method represents the entry point of the function. We decorate the method signature with some additional information defining the structure of the returned table, as well as naming the other method in the class that will return the columns for the table itself. Start by creating a new database project and connecting it to your sample database. We will not be using any data tables, so any database will do. Add a new function to the project and call it "SampleTable". All table value functions return IEnumerable. There are a number of framework classes that implement this interface in the System.Collections namespace. A new language enhancement in C# greatly simplifies the implementation of IEnumerable: the yield keyword. Yield allows you to return from the function in the middle of a loop. The internal state of the loop is maintained and the function can be called multiple times, with each successive call returning the next value in the loop until the loop is exhausted.

Here we see the fully coded function. Attributes define the column definition of the return table. It's a little unfortunate that this definition is a string, so there is no compile time error checking possible. Internal to the method we define one array for each column, fill it up with data and then loop thru each item. Notice that because we can only return a single value from the function, we package up each row to be return as an array of objects. The yield will return one row at a time. The other method attribute is the FillRowMethodName. This is the name of a method that SQL Server will call for each row fetched to redefine the columns. It will accept a generic object, and one out parameter for each column defined in the TableDefinition.

 

Our FillRow function gets passed the return value from SampleTable as an object, and one parameter for each column in the table. Its purpose is to explode the object into its constituent elements and fill in the needed columns. Notice how our two methods are tightly bound to each other. FillRow must have complete knowledge SampleTable implementation.

Go ahead and deploy your project from the build menu, then switch over to SQL Server Management Studio to test your work.

That was pretty darn easy if you ask me, and not that much code.