Ad

IDataReader Empties When Viewing For A Second Time

- 1 answer

I'm getting something pretty strange going on when trying to read some data using the MySql .net connector. Here's the code:

IDataReader reader = null;

using (MySqlConnection connection = new MySqlConnection(this.ConnectionString))
{
    String getSearch = "select * from organization";
    MySqlCommand cmd = new MySqlCommand(getSearch, connection);
    cmd.CommandType = CommandType.Text;

    connection.Open();
    reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        // response write some stuff to the screen (snipped for brevity)
    }
}

If I put a breakpoint after the ExecuteReader and expand the results view in Visual Studio (hovering over reader and expanding), I can see the rows returned by the query. If I then let that close and expand the results view again, I get the message 'Enumeration yielded no results'.

It seems as if the contents of the reader are getting reset as soon as they're viewed.

As for what we've tried:
- the SQL runs fine directly on to DB
- Binding the results of the query directly to a datagrid just returns an empty datagrid
- got the latest version of the .net connector
- tried on two different machines to rule out any local errors

So far nothing's worked.

If anyone could offer any ideas or suggestions they would be very much appreciated.

Ad

Answer

from what I understand the SqlDataReader is intended to be used for a one-time enumeration of the data you've returned. Once you've cycled through the results once, the object has done its duty. Here are a couple ideas for working around this, one or the other of which may solve this for you depending on your needs:

  1. Re-execute the query to generate another SqlDataReader when needed

  2. Instead of using the SqlDataReader, store the results of your original query into a System.Data.DataTable, where you can then re-read and manipulate the data however you like.

Hope this helps!

Adam

Ad
source: stackoverflow.com
Ad