How can I easily convert DataReader to List? [d

Question

This question already has an answer here:

• How to improve data access layer select method Pattern 7 answers
• Convert rows from a data reader into typed results 7 answers

I have data in a DataReader which I want to be converted to a List<T>. What is a possible simple solution for this?

For e.g. in CustomerEntity class, I have CustomerId and CustomerName properties.If my DataReader returns these two columns as data, then how can I convert it into List<CustomerEntity>.

Answer 1

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 Linq 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.

Answer 2

I would suggest writing an extension method for this:

public static IEnumerable<T> Select<T>(this IDataReader reader,
                                       Func<IDataReader, T> projection)
{
    while (reader.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 (IDataReader reader = ...)
{
    List<Customer> customers = reader.Select(r => new Customer {
        CustomerId = r["id"] is DBNull ? null : r["id"].ToString(),
        CustomerName = r["name"] is DBNull ? null : r["name"].ToString() 
    }).ToList();
}

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

public static Customer FromDataReader(IDataReader reader) { ... }

That would leave:

using (IDataReader 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

Obviously @Ian Ringrose's central thesis that you should be using a library for this is the best single answer here (hence a +1), but for minimal throwaway or demo code here's a concrete illustration of @SLaks's subtle comment on @Jon Skeet's more granular (+1'd) answer:

public List<XXX> Load( <<args>> )
{
    using ( var connection = CreateConnection() )
    using ( var command = Create<<ListXXX>>Command( <<args>>, connection ) )
    {
        connection.Open();
        using ( var reader = command.ExecuteReader() )
            return reader.Cast<IDataRecord>()
                .Select( x => new XXX( x.GetString( 0 ), x.GetString( 1 ) ) )
                .ToList();
    }
}

---

As in @Jon Skeet's answer, the

            .Select( x => new XXX( x.GetString( 0 ), x.GetString( 1 ) ) )

bit can be extracted into a helper (I like to dump them in the query class):

    public static XXX FromDataRecord( this IDataRecord record)
    {
        return new XXX( record.GetString( 0 ), record.GetString( 1 ) );
    }

and used as:

            .Select( FromDataRecord )

---

UPDATE Mar 9 13: See also Some excellent further subtle coding techniques to split out the boilerplate in this answer

Answer 4

The simplest Solution :

var dt=new DataTable();
dt.Load(myDataReader);
list<DataRow> dr=dt.AsEnumerable().ToList();

Answer 5

I would (and have) started to use Dapper. To use your example would be like (written from memory):

public List<CustomerEntity> GetCustomerList()
{
    using (DbConnection connection = CreateConnection())
    {
        return connection.Query<CustomerEntity>("procToReturnCustomers", commandType: CommandType.StoredProcedure).ToList();
    }
}

CreateConnection() would handle accessing your db and returning a connection.

Dapper handles mapping datafields to properties automatically. It also supports multiple types and result sets and is very fast.

Query returns IEnumerable hence the ToList().

Answer 6

I've covered this in a pet project.. use what you want.

Note that the ListEx implements the IDataReader interface.

people = new ListExCommand(command)
.Map(p=> new ContactPerson()
{
  Age = p.GetInt32(p.GetOrdinal("Age")),
  FirstName = p.GetString(p.GetOrdinal("FirstName")),
  IdNumber = p.GetInt64(p.GetOrdinal("IdNumber")),
  Surname = p.GetString(p.GetOrdinal("Surname")),
  Email = "[e-mail]"
})
.ToListEx()
.Where("FirstName", "Peter");

Or use object mapping like in the following example.

people = new ListExAutoMap(personList)
.Map(p => new ContactPerson()
{
    Age = p.Age,
    FirstName = p.FirstName,
    IdNumber = p.IdNumber,
    Surname = p.Surname,
    Email = "[e-mail]"
})
.ToListEx()
.Where(contactPerson => contactPerson.FirstName == "Zack");

Have a look at http://caprisoft.codeplex.com