SqlDataReader Never Returning False

Question

I'm using the SqlDataReader to write an Excel workbook with several worksheets. Each worksheet has a header, a body and a footer so I'm using a while loop inside a while loop.

The problem is that reader.Read() never returns false for me so eof is never set to false. At the end of the file, I get an error when I try to write the header because the reader is empty.

The specific error message is:

Invalid attempt to read when no data is present.

Please look at my code and help if you can.

reader = cmd.ExecuteReader();
bool eof = false;
bool first = true;

while (!eof)
{
    // write a header 
    // set newHeaderCondition from the Reader -- error occurs here
    if (first)
    {
        reader.Read();
        first = false;
    }

    do
    {
        // write row onto spreadsheet
        eof = reader.Read();   ---- THIS IS NEVER FALSE
    } while (!eof && (reader[0] == newHeaderCondition ));

    // write footer that doesn't contain any reader data
    if (!eof )
    {
        // create a new worksheet
    }
}

reader.Close();

Answer 1

SqlDataReader.Read advances reader to the next record and returns true as long as there are more rows; otherwise false.

The problem is on your loop condition, it only execute once (even if there are more rows), revise your while condition and modify it.

Answer 2

I don't fully understand the behavior you describe, but I suspect you are misinterpreting your observations.

One obvious mistake is that you are assigning the wrong value to eof. You are assigning true to eof when there is still data, and false, when there isn't any.

You probably meant:

eof = !reader.Read();