C# mysqlreader on same connection error

Question

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();

Does the inner connection use lie in the fact that "new CFolder(int)" calls this same function? — Reddog
– Reddog, Commented Jun 10, 2010 at 16:45

Gratzy · Accepted Answer · 2010-06-10 16:47:26Z

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?

answered Jun 10, 2010 at 16:47

Gratzy

9,4174 gold badges32 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eric Petroelje · Accepted Answer · 2010-06-10 16:47:56Z

0

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

Create a new connection and use the second connection for the second reader.
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.

answered Jun 10, 2010 at 16:47

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Comments

simendsjo · Accepted Answer · 2010-06-10 16:48:07Z

0

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.

answered Jun 10, 2010 at 16:48

simendsjo

4,7693 gold badges29 silver badges55 bronze badges

Collectives™ on Stack Overflow

C# mysqlreader on same connection error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related