Convert rows from a data reader into typed results

Question

I'm using a third party library which returns a data reader. I would like a simple way and as generic as possible to convert it into a List of objects.
For example, say I have a class 'Employee' with 2 properties EmployeeId and Name, I would like the data reader (which contains a list of employees) to be converted into List< Employee>.
I guess I have no choice but to iterate though the rows of the data reader and for each of them convert them into an Employee object that I will add to the List. Any better solution? I'm using C# 3.5 and ideally I would like it to be as generic as possible so that it works with any classes (the field names in the DataReader match the property names of the various objects).

Answer 1

You could build an extension method like:

public static List<T> ReadList<T>(this IDataReader reader, 
                                  Func<IDataRecord, T> generator) {
     var list = new List<T>();
     while (reader.Read())
         list.Add(generator(reader));
     return list;
}

and use it like:

var employeeList = reader.ReadList(x => new Employee {
                                               Name = x.GetString(0),
                                               Age = x.GetInt32(1)
                                        });

Joel's suggestion is a good one. You can choose to return IEnumerable<T>. It's easy to transform the above code:

public static IEnumerable<T> GetEnumerator<T>(this IDataReader reader, 
                                              Func<IDataRecord, T> generator) {
     while (reader.Read())
         yield return generator(reader);
}

If you want to automatically map the columns to properties, the code idea is the same. You can just replace the generator function in the above code with a function that interrogates typeof(T) and sets the properties on the object using reflection by reading the matched column. However, I personally prefer defining a factory method (like the one mentioned in Joel's answer) and passing a delegate of it into this function:

 var list = dataReader.GetEnumerator(Employee.Create).ToList();

Answer 2

Do you really need a list, or would IEnumerable be good enough?

I know you want it to be generic, but a much more common pattern is to have a static Factory method on the target object type that accepts a datarow (or IDataRecord). That would look something like this:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }

    public static Employee Create(IDataRecord record)
    {
        return new Employee
        {
           Id = record["id"];
           Name = record["name"];
        };
    }
}

.

public IEnumerable<Employee> GetEmployees()
{
    using (var rdr = YourLibraryFunction())
    {
       while (rdr.Read())
       {
           yield return Employee.Create(rdr);
       }
    }
}

Then if you really need a list rather than an IEnumerable you can call .ToList() on the results. I suppose you could also use generics + a delegate to make the code for this pattern more re-usable as well.

Update: I saw this again today and felt like writing the generic code:

public IEnumerable<T> GetData<T>(IDataReader reader, Func<IDataRecord, T> BuildObject)
{
    try
    {
        while (reader.Read())
        {
            yield return BuildObject(reader);
        }
    }
    finally
    {
         reader.Dispose();
    }
}

//call it like this:
var result = GetData<Employee>(YourLibraryFunction(), Employee.Create);

Answer 3

Whilst I wouldn't recommend this for production code, but you can do this automatically using reflection and generics:

public static class DataRecordHelper
{
 public static void CreateRecord<T>(IDataRecord record, T myClass)
 {
  PropertyInfo[] propertyInfos = typeof(T).GetProperties();

  for (int i = 0; i < record.FieldCount; i++)
  {
   foreach (PropertyInfo propertyInfo in propertyInfos)
   {
    if (propertyInfo.Name == record.GetName(i))
    {
     propertyInfo.SetValue(myClass, Convert.ChangeType(record.GetValue(i), record.GetFieldType(i)), null);
     break;
    }
   }
  }
 }
}

public class Employee
{
 public int Id { get; set; }
 public string LastName { get; set; }
 public DateTime? BirthDate { get; set; }

 public static IDataReader GetEmployeesReader()
 {
  SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

  conn.Open();
  using (SqlCommand cmd = new SqlCommand("SELECT EmployeeID As Id, LastName, BirthDate FROM Employees"))
  {
   cmd.Connection = conn;
   return cmd.ExecuteReader(CommandBehavior.CloseConnection);
  }
 }

 public static IEnumerable GetEmployees()
 {
  IDataReader rdr = GetEmployeesReader();
  while (rdr.Read())
  {
   Employee emp = new Employee();
   DataRecordHelper.CreateRecord<Employee>(rdr, emp);

   yield return emp;
  }
 }
}

You can then use CreateRecord<T>() to instantiate any class from the fields in a data reader.

<asp:GridView ID="GvEmps" runat="server" AutoGenerateColumns="true"></asp:GridView>

GvEmps.DataSource = Employee.GetEmployees();
GvEmps.DataBind();