MySQL Reader in C# - "There is already an open Data Reader associated with this connection which must be closed first."

Question

I have a problem with my MySQL Reader - I am running the reader in a loop, checking if a configured entry exists in my database. After the reader is applied, the reader is getting closed and set to null again. Anyhow, I always get this error message when I am running the "CheckExistEntry" - function in my code.

"07.04.2021 14:28:05 ERROR: There is already an open Data Reader associated with this connection which must be closed first."

The error does not occur in the following situations:

If I set a breakpoint at the relevant position in the code, the error does not occur. If I set a sleep for 1000 MS before the reader is executed, the error does not occur.

Is it possible that C# is running loops via multithreading without me knowing it?

Here's the code:

public bool CheckExistEntry(string iColumnName)
{
    MySqlDataReader reader = null;
    string query2 = "SHOW COLUMNS FROM defectdetection.defects_main";
    MySqlCommand cmd = new MySqlCommand(query2, MySqlConn);
    try
    {
        // SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA =[Database Name] AND TABLE_NAME =[Table Name];#
        // write_Log()
        write_Log(log_directory, log_file, "BEFORE ExecuteReader");
        //COMMENT FOR STACKOVERFLOW: cmd.ExecuteReader() triggers the Error.
        reader = cmd.ExecuteReader();
        write_Log(log_directory, log_file, "AFTER ExecuteReader");
        //now, communication with MySQL is finished. .. 
        List<string> ColumnNames = new List<string>();
        while (reader.Read())
        {
            ColumnNames.Add((string)reader[0]);
        }
        if (ColumnNames.Contains(iColumnName))
        {
            reader.Close();
            reader.Dispose();
            reader = null;
            return true;
        }
        else
        {
            reader.Close();
            reader.Dispose();
            reader = null;
            return false;
        }
    }
    catch (Exception ex)
    {
        if (reader != null)
        {
            reader.Close();
            reader.Dispose();
            reader = null;
        }
              
        exception = new ArgumentException(ex.Message);
        MessageBox.Show(ex.Message);
        //TODO handle exception
                
        write_Log(log_directory, log_file,"ERROR: There is already an open Data Reader associated with this connection which must be closed first.");
                
        progressBarForm.Invoke(new updatebar(progressBarForm.Close));
        return false;
    }
}

---

i tried it with a triple-using statement now (using MySqlConnection, using MySqlCommand, using MySqlDatareader). But it still does not work, i get another error now: "Connection must be valid and open."

                using (MySqlConnection MySqlConnLocal = new MySqlConnection()) {
            using (MySqlCommand cmd = new MySqlCommand(query2, MySqlConnLocal)) {
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {

                
                while (reader.Read())
                {
                    ColumnNames.Add((string)reader[0]);
                }
            }

            if (ColumnNames.Contains(iColumnName))
            {
                return true;
            }
            else
            {
                return false;
            }
            }
            }

Answer 1

FIXED IT NOW: This Code here was the solution:

                //System.Threading.Thread.Sleep(1);
            using (MySqlConnection MySqlConnLocal = new MySqlConnection(ConnString))
            {
                MySqlConnLocal.Open();
                using (MySqlCommand cmd2 = new MySqlCommand(query2, MySqlConnLocal))
                {

                    reader = cmd2.ExecuteReader();
                    while (reader.Read())
                    {


                        ColumnNames.Add((string)reader[0]);

                    }
                }
            }

Answer 2

You haven't dispose of "cmd". Try the following:

                   ...

public bool CheckExistEntry(string iColumnName)
{
    string query2 = "SHOW COLUMNS FROM defectdetection.defects_main";
    
    try
    {
        using (MySqlConnection mySqlConn = new MySqlConnection(ConnectionStr))
        {
            mySqlConn.Open();

            using (MySqlCommand cmd = new MySqlCommand(query2, mySqlConn))
            {
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if ((string)reader[0] == iColumnName)
                        {
                            return true;
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        var exception = new ArgumentException(ex.Message);
        MessageBox.Show(ex.Message);
        //TODO handle exception

        write_Log(log_directory, log_file, "ERROR: There is already an open Data Reader associated with this connection which must be closed first.");

        //ToDo: add any additional desired code
        
    }

    return false;
}