Error reading IDataReader several times

Question

I'm trying to load data (4 columns from 1 table) from an SQL database into a list, and have this so far

List<string> FNameList = (from IDataRecord r in myReader
                          select (string)r["FirstName"]).ToList();

List<string> LNameList = (from IDataRecord r in myReader
                          select (string)r["LastName"]).ToList();

List<string> EmailList = (from IDataRecord r in myReader
                          select (string)r["Email"]).ToList();

List<string> PhoneList = (from IDataRecord r in myReader
                          select (string)r["PhoneNumber"]).ToList();

Now the database that I'm using has three rows of data, so the length of each of these should be 3. However only the first one returns a length of three; the others have a length of 0. It's even stranger, that if I comment out the first one, the second will work, but not the others. Likewise with the third and fourth.

It's hard to explain since I can't provide the database for testing, so I'm wondering if there is anything obvious in the above, or if this is the wrong approach for loading column data into an array/list format.

Answer 1

I assume you have a Select extension method similar to this:

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

So when the reader has been enumerated, it's at the end of the available data, and the only way to read the data again is to reissue the query. So you need to fetch all fields at once:

var records = (from IDataRecord r in myReader
               select new
               {
                   FirstName = (string)r["FirstName"],
                   LastName = (string)r["LastName"],
                   Email = (string)r["Email"],
                   PhoneNumber = (string)r["PhoneNumber"]
               }).ToList();