Zoltán Király Zoltán Király - 4 months ago 17
MySQL Question

c#, I keep catching a multiple MySQL reader exception when there is only 1 reader

I have a Windows Forms application in C# that has one of the buttons run a code like this:

private void aCertainButton_Click(object sender, EventArgs e)
{
if (folderFinder.ShowDialog() == DialogResult.OK)
{
if (openConnection())
{
foreach (Thingy stuff in aCertainCollection)
{
string sqlCommandString = "SELECT COUNT(*) FROM thatTable WHERE someField = " + stuff.property + ";";
try
{
MySqlCommand count = new MySqlCommand(sqlCommandString, connection);
int rowCount = 0;
object o = count.ExecuteScalar();
if (o != null)
{
rowCount = int.Parse(o.ToString());
}

if (rowCount == 1)
{
MySqlCommand cmd = new MySqlCommand("SELECT * FROM thatTable WHERE someField = " + stuff.property + ";", connection);
MySqlDataReader reader = cmd.ExecuteReader();

//Processing stuff from the reader here

reader.Close();
reader.Dispose();
}
}
catch (MySqlException mex)
{
Console.WriteLine(mex.Message);
}
}
closeConnection();
}
}
}


Any time I run the application I catch the following exception: "There is already an open DataReader associated with this Connection which must be closed first."

But there shouldn't be. I use 1 reader in this code and there is nothing else running asynchronously. As you can see I close the reader and open a new one after each iteration in the loop. I use a reader associated to the same connection in the initialization of the application, but when data processing from it is done I close it and dispose of it.

I would appreciate of anybody could help me resolve this mystery, thank you.

Answer Source

In your code, if something generates an exception on the part named "processing stuff" then you will leave the reader open.

When you need to ensure something is disposed after it's use even in the case of an exception then use the using clausule:

if (rowCount == 1)
{
    MySqlCommand cmd = new MySqlCommand("SELECT * FROM thatTable WHERE someField = " + stuff.property +  ";", connection);
    using(MySqlDataReader reader = cmd.ExecuteReader())
    {

        //Processing stuff from the reader here

    }
}