How can I easily convert DataReader to List<T> ?

Question

I'm having the data in datareader which i want to be converted in list. What is possible simple solutions for this?

For e.g.In CustomerEntity class, I'm having customerId & CustomerName properties.If my datareader returns these two column data. then How can i convert it into List <>.

ohh amazing not able to write customerEntity in <>

Answer 1

hi

You cant simply (directly) convert the datareader to list.

You have to loop through all the elements in datareader and insert into list

below the sample code

using (drOutput)   
{
            System.Collections.Generic.List<CustomerEntity > arrObjects = new System.Collections.Generic.List<CustomerEntity >();        
            int customerId = drOutput.GetOrdinal("customerId ");
            int CustomerName = drOutput.GetOrdinal("CustomerName ");

        while (drOutput.Read())        
        {
            CustomerEntity obj=new CustomerEntity ();
            obj.customerId = (drOutput[customerId ] != Convert.DBNull) ? drOutput[customerId ].ToString() : null;
            obj.CustomerName = (drOutput[CustomerName ] != Convert.DBNull) ? drOutput[CustomerName ].ToString() : null;
            arrObjects .Add(obj);
        }

}

Cheers

RameshVel

Answer 2

I would suggest writing an extension method for this:

public static IEnumerable<T> Select<T>(this DataReader reader,
                                       Func<DataReader, T> projection)
{
    while (drOutput.Read())
    {
        yield return projection(reader);
    }
}

You can then use LINQ's ToList() method to convert that into a List<T> if you want, like this:

using (DataReader reader = ...)
{
    List<Customer> customers = reader.Select(reader => new Customer {
        CustomerId = reader["id"] is DBNull ? null : reader["id"].ToString(),
        CustomerName = reader["name"] is DBNull ? null : reader["name"].ToString() 
    }).ToList();
}

I would actually suggest putting a FromDataReader method in Customer (or somewhere else), which would leave:

using (DataReader reader = ...)
{
    List<Customer> customers = reader.Select<Customer>(Customer.FromDataReader)
                                     .ToList();
}

(I don't think type inference would work in this case, but I could be wrong...)

Answer 3

I have seen systems that use Reflection and attributes on Properties or fields to maps DataReaders to objects. (A bit like what LinqToSql does.) They save a bit of typing and may reduce the number of errors when coding for DBNull etc. Once you cache the generated code they can be faster then most hand written code as well, so do consider the “high road” if you are doing this a lot.

See "A Defense of Reflection in .NET" for one example of this.

You can then write code like

class CustomerDTO  
{
    [Field(“id”)]
    public int? CustomerId;

    [Field(“name”)]
    public string CustomerName;
}

...

using (DataReader reader = ...)
{    
   List<CustomerDTO> customers = reader.AutoMap<CustomerDTO>()
                                    .ToList();
}

(AutoMap(), is an extension method)

---

@Stilgar, thanks for a great comment

If are able to you are likely to be better of using NHibernate, EF or Link to Sql, etc However on old project (or for other (sometimes valid) reasons, e.g. “not invented here”, “love of stored procs” etc) It is not always possible to use a ORM, so a lighter weight system can be useful to have “up your sleeves”

If you every needed too write lots of IDataReader loops, you will see the benefit of reducing the coding (and errors) without having to change the architecture of the system you are working on. That is not to say it’s a good architecture to start with..

I am assuming that CustomerDTO will not get out of the data access layer and composite objects etc will be built up by the data access layer using the DTO objects.