DataReader has rows and data, trying to read from

Question

I haven't used DataReaders in ages (I prefer to use an ORM) but I'm forced to at work. I pull back the rows, and check that HasRows is true; debugging at this point and examining the reader shows that my data is there.

Now here's the issue: the moment I call reader.Read(), trying to expand the results says "The enumeration yielded no results" or whatever, and I get the "Invalid attempt to read when no data is present." error. I get the same thing if I don't call Read() (which is the default since the DataReader starts before the first record).

I cannot remember the proper way to handle this; the data is there when I check HasRows, but is gone the moment I either try to read from it right after or after I call Read, which makes no sense as if I don't call Read, the reader should still be before the first record, and if the property is set that starts it at the first record (SingleRow? I forget the name of it) is set, then I should be able to read rows without calling Read, however both ways seem to move past the row containing the data.

What am I forgetting? Code is fairly straightforward:

TemplateFile file = null;
using (DbDataReader reader = ExecuteDataReaderProc("GetTemplateByID", idParam)) 
{ 
    if (reader.HasRows) // reader has data at this point - verified with debugger
    { 
        reader.Read(); // loses data at this point if I call Read()
        template = new TemplateFile 
        {
            FileName = Convert.ToString(reader["FileName"]) // whether or not I call
                                                            // Read, says no data here
        };
    }
}

Answer 1

Just to clarify the answer, it was using the debugger since expanding the results view calls Read() and therefore it moves past the row. As Marc Gravell said in a comment: Debugger considered harmful

Answer 2

If you want to put the data into a file, start by loading a DataTable instead of using a DataReader. With the DataReader, as has been mentioned in the comments, you might want to iterate through the result set with a while loop

while (reader.Read())
{

}

The loop reads one row at a time and quits when all of the rows have been read. Once you move to the next row, the previous rows are no longer available unless you have put them into some other structure, like a list or DataTable.

But you can use a DataAdapater to fill a DataTable so there might not be a reason to use a DataReader. Then you can write to a file from the DataTable.

In any event, I don't see how this line could work.

FileName = Convert.ToString(reader["FileName"])

I can post additional code for either approach if you like.
HTH Harvey Sather