C# mysqlreader on same connection error

Question

Hi,

I must find a way to do this in C#, if possible...

I must loop on my folder list (mysql table), and for each folder I instanciate I must do another query, but when I do this it says : "There is already an open DataReader associated with this Connection" and I am inside a mysqlreader loop already.

Note that I have oversimplified the code just to show you, the fact is that I must do queries inside a mysqlreader loop, and it looks to be impossible as they are on the same connection?

MySqlConnection cnx = new MySqlConnection(connexionString);

        cnx.Open();

        MySqlCommand command= new MySqlCommand("SELECT * FROM folder WHERE  folder_id = " + id, cnx);

        MySqlDataReader reader= commande.ExecuteReader();

        while (reader.Read())
        {
            this.folderList[this.folderList.Length] =
                   new CFolder(reader.GetInt32"folder_id"),                                                                             cnx);
        }
        reader.Close();

        cnx.Close();

Answer 1

Well you could get all the data down localy first, maybe a data table or some other collection, then loop through that making you second sql calls inside that loop.

However, making a sql call inside a loop isn't usually a good path to take. Is there anyway you can get all your data in the first call?

Answer 2

You have a couple options here, either would work fine:

1. Create a new connection and use the second connection for the second reader.

2. Iterate over the results of the first data reader, store the folder_id values in a collection, then close the reader and open a new one.

Answer 3

When you have a DataReader open, you cannot use that connection for any other things. You must either close the open reader first, or open a new connection. Database connections are valuable resources, so I don't recommend opening new connections in a loop unless absolutely necessary.

I didn't quite get the code you sent though.. The connection is not used for anything other than a datareader there.