Consuming IDataReader with LINQ.

Why?

• IDataReader is a very efficient way to retrieve data from database. Since it’s forward-only, the data cannot be manipulated directly (in opposite to DataSet, which is disconnected data source).

• LINQ (language-integrated query) is a new .NET approach to work with data in code. Very powerful, flexible and extendable.

Problem

LINQ is built on and relies on IEnumerable<T> interface, IDataReader does not implement this interface.

Solution

Extension method:

Code:

   1:  
   1:  public static class DataReaderExtension
   2:   {
   3:     public static IEnumerable<T> DataRecord<T>(this IDataReader source, Func<IDataRecord, T> objectBuilder)
   4:     {
   5:       if (source == null)
   6:         throw new ArgumentNullException("source");
   7:       while (source.Read())
   8:       {
   9:         yield return objectBuilder((IDataRecord) source);
  10:       }
  11:     }
  12:   }

Description:

Since this is extension method – it can be applied on each object implements IDataReader interface, for example db.ExecuteReader(dbCommand)).

Input:

Func<IDataRecord, T>  - this is actually strong type pointer to function that creates object of type T from reader row. You can read more about Generic Delegate Func here. .NET shipped with more predefined strong type delegates, as described here.

Output:

IEnumerable<T> – enumerable collection of elements that can be used as input to LINQ query.

Usage Example:

   1:  var res = db.ExecuteReader(dbCommand);

   2:  var result = res.DataRecord(rec => new LobbyGame

   3:        {

   4:             GameUrl = rec.GetString(rec.GetOrdinal("GameURL")),

   5:             Name = rec.GetString(rec.GetOrdinal("GameName")),

   6:             Id = rec.GetInt32(rec.GetOrdinal("GameID")),

   7:             Description = rec.GetString(rec.GetOrdinal("Description")),

   8:             ShortDescription = rec.GetString(rec.GetOrdinal("ShortDescription")),

   9:             SourceId = rec.GetInt32(rec.GetOrdinal("SourceID")),

  10:         });

In this example I create IDataReader on line 1, then on line 2 I use my extension method to create IEnumerable<LobbyGame>. Note that as input parameter to extension method I use lambda expression that defines creation of each LobbyGame instance.

del.icio.us Tags: LINQ,DataReader