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.

Suppose myReader is a SqlDataReader. Then this is forward only and after the first use cannot be restarted again — Steve, Aug 07 '12 at 09:12

Thomas Levesque · Accepted Answer · 2012-08-07T10:17:34.030

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();

score 2 · Answer 2 · answered Aug 07 '12 at 09:15

2

Your reader is advancing to the last record after the first query. You need to extract all rows and then build your lists:

var records = (from IDataRecord r in myReader select r).ToArray();

List<string> LNameList = (from IDataRecord r in records
                       select (string)r["LastName"]).ToList();
// Keep the last row for all fields

answered Aug 07 '12 at 09:15

Amiram Korach

13,056
3
28
30

That won't work... It will return the last record 3 times (unless `Select` copies the data records before yielding them, but that seems unlikely) – Thomas Levesque Aug 07 '12 at 09:17
The `ToArray` method is creating a .NET in memory collection. – Amiram Korach Aug 07 '12 at 09:19
Yes, but if `Select` is implemented like I think it is, it actually returns always the same instance of IDataRecord; at the end of the enumeration, that IDataRecord contains the data for the last record. You need to extract the content of each record *before* you advance to the next record. – Thomas Levesque Aug 07 '12 at 09:23
What you're saying sounds interesting. Going to try that. – Amiram Korach Aug 07 '12 at 09:32
I wonder how you tested it; I have also tested it, and I observed the result I described. Which Select implementation did you use ? – Thomas Levesque Aug 07 '12 at 10:14
you must have used something else... There is no Select method for IDataReader by default, so this code alone wouldn't compile – Thomas Levesque Aug 07 '12 at 11:49

Steve · Answer 3 · 2012-08-07T09:26:04.143

2

myReader is a SqlDataReader. The SqlDataReader provides a way of reading a forward-only stream of rows from a SQL Server database. After the first use cannot be restarted again.

You need to read all of your data in a single loop and then, if needed build your lists
However, I can not understand why you divide up the information in this way.

edited Aug 07 '12 at 09:26

answered Aug 07 '12 at 09:16

Steve

213,761
22
232
286

Thanks Steve, you're 100% correct. I'm quite new to data management, so I was just trying to get something working. Thomas's answer worked for me, so thanks all! – OSer Aug 07 '12 at 09:52

score 1 · Answer 4 · answered Aug 07 '12 at 09:16

First declare a poco to hold the properties:

class Person
{
    ...
}

Then a helper method:

private static IEnumerable<Person> ReadReader(IDataReader reader)
{
    using (reader)
    {
        while (reader.Read())
        {
            yield return new Person
            {
                FirstName = (string)reader["FirstName"],
                LastName = (string)reader["LastName"],
                Email = (string)reader["Email"],
                PhoneNumber = (string)reader["PhoneNumber"]
            }
        }
    }
}

Usage:

List<Person> list = RaderReader(command.ExecuteReader()).ToList();

Error reading IDataReader several times

4 Answers4