When DataReader become empty?

Question

I have the below code :

if (reader.HasRows)
{
    while (reader.Read())
    {
        DataRow searchedDR = ds.Tables[dr["name"].ToString()].Rows.Find(reader["ID"].ToString());
        if (searchedDR != null)
            ds.Tables[dr["name"].ToString()].Rows.Remove(searchedDR);
    }
}

When this block of code done successfully the data reader (reader) become empty? What is the reason? I need to work with this reader afterward. What is the solution?

Answer 1

The IDataReader defines a readonly, forward only data provider. Once you call the Read() method, there is no way to go back to the previous row. As the interface provides no Reset() method, the only way to start over, is to execute the database command again. Think of the reader as a stream of data - you can ask for the next row, but once read, it's gone.

If you need to traverse the results of a database query several times, you will need to cache the results in application memory. You could do this manually doing the initial pass on the reader, but it would be a lot easier to use an IDataAdapter instead.

Answer 2

A data-reader is a hose of data. It becomes "empty" (or a better analogy: runs dry) when you consume all the data it is reading.

It is not a storage device (it isn't a bucket). If you want the data afterwards, store it somewhere - a DataTable or a class model, for example.

Answer 3

Depends on how much data is going to be produced. If it's not much you could use the reader to read the data into a temporary data structure then iterate over the data structure for both of your operations. If there is too much data to keep it all in memory efficiently, then you've no choice but to create a new data reader and re-execute the command.

Answer 4

The DataReader is forward-only, you cannot take a step back. It is intended for quick, one-directional datareading operations.

If you need to work with the data afterwards, you will have to use the DataTable you have copied the data into, or reinitliaize it.

Answer 5

A DataReader is a stream of data that is returned from a database query. When the query is executed, the first row is returned to the DataReader via the stream. The stream then remains connected to the database, poised to retrieve the next record. The DataReader reads one row at a time from the database and can only move forward, one record at a time. As the DataReader reads the rows from the database, the values of the columns in each row can be read and evaluated, but they cannot be edited.

Generally we use datareader ado.net control where we don't need go to previous row like binding a dropdown or data repeater control. To keep it light weighted microsoft support forward only functionality in datareader control.

To use it afterward either use DataTable or make a custom class that persist the contents.

Source: http://www.techbaba.com/q/2758-why+datareader+forward+only.aspx