Converting a DataReader to IEnumerable Using Iterators and Yield in VB.NET

The yield keyword is incredibly useful when you need to convert collections into other types of collections. Unfortunately VB programmers were left out until late last year. Since the Async CTP 3 the yield keyword and Iterator functions have been available for Visual Basic .NET.

You can find lots of examples of this around for C# but some of us corporate contract programmers sometimes have to use VB.NET! 

Here's an example of how to create a list of objects from a DataReader using Yield. To run this you will need to download the Visual Studio Async CTP 3 or .NET 4.5 RC.

So our goal is to take a set of rows from a database, which we access via a DataReader. We want to convert each row into an object, and return all those objects as an IEnumerable - in this case a List(Of T).

Here's our class to store the data as objects. Notice it has a static method to create an instance of itself using a row from a DataReader.

Class StudentRecord
        Public StudentId
        Public UnitCode
        Public HoursPerWeek
        Public NumberOfWeeks
        Public TotalHours
 
        Public Shared Function Create(dr As IDataRecord) As StudentRecord
            Return New StudentRecord With {
                .StudentId = GetStringFromReader(dr, "studentId"),
                .UnitCode = GetStringFromReader(dr, "unitCode"),
                .HourPerWeek = GetDoubleFromReader(dr, "hoursPerWeek"),

                .NumberOfWeeks = GetIntFromReader(dr, "numWeeks"),
                .TotalHours = .HoursPerWeek * .NumberOfWeeks
                }
        End Function
End Class

(GetStringFromReader and similar are helper function from my common utility library. They just return the specified column's value as the required type.)

Next, we have our main loop where we retrieve the data from the database, and then pass it to the conversion method. Let's call it GetData. It queries the DB and returns the list of StudentRecord objects. The details of GetReader aren't important - it just creates a DataReader based on the SqlDatabase and SqlCommand objects.

Note: this code is just for example - we wouldn't normally necessarily have the SQL statement right here!

The interesting part is the call to GetEnumerator. We pass in the type (aka generic type T) we want to get back, as well as the data reader and a function parameter for the static method we saw above. Lastly we convert the IEnumerable it returns to a list.

Private Function GetData() As List(Of StudentRecord)

        Dim db As New SqlDatabase(CONN_STRING)
 
        Using cmd As SqlCommand =
            SqlDatabaseHelper.CreateSqlStringCommand(db,
                                   "SELECT * from students")
            ' Create data reader
            Using dr As DbDataReader = GetReader(db, cmd)
                Return GetEnumerator(Of StudentRecord)(dr, AddressOf StudentRecord.Create).ToList()
            End Using
 
        End Using
    End Function

Last but not least - here's where we convert each row from the DataReader into a StudentRecord. It's quite simple and this is the great bit about how Yield works. We simply pass each row from the reader into the function specifed in the generator function parameter, and it outputs a constructed StudentRecord object. The Yield functionality takes care of executing the StudentRecord.Create method, collecting up all the created objects and returning them as an IEnumerable object.

We can get rid of the reader here because once we've completely iterated we have all the data we need in the IEnumerable.

Public Iterator Function GetEnumerator (Of T)(reader As IDataReader, generator As Func(Of IDataRecord, T)) As IEnumerable(Of T)
        Try
            While (reader.Read())
                Yield generator(reader)
            End While
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        Finally
            reader.Dispose()
        End Try
End Function

So there you have it - a functional Yield scenario written in VB.NET. Personally I love these patterns as they allow you to write much cleaner and testable code Hope it helps!