Question 190285 in Mysql Question posted in
The official MySQL documentation can be found here.

MySqlDataReader : I close the connection but get : There is already an open DataReader associated with this Connection which must be closed first

    private void startPoll()
    {

        System.Diagnostics.Debug.WriteLine("dbForm created");
         timer = new Timer(int.Parse(Properties.Settings.Default.DbPoll) * 1000);
        // ElapsedEventHandler OnEventExecution = null;
        timer.Elapsed += OnEventExecution;
        timer.Start();
        System.Diagnostics.Debug.WriteLine("Timer Started " + Properties.Settings.Default.DbPoll + " secs");

        string connectionString;
        // MySqlConnection conn;
        connectionString = "server=" + Properties.Settings.Default.DbServer + ";database=digital_passports;uid=" + Properties.Settings.Default.DbUser + ";pwd=" + Properties.Settings.Default.DbPass ;
        try
        {
            using (conn = new MySqlConnection(connectionString))
            {
                try
                {

                    conn.Open();
                    System.Diagnostics.Debug.WriteLine("Database Connection Connected");

                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine("Database Connection Failed..." + ex);
                }

                var stm = "SELECT * from vtokenpool limit 10";


                using (var cmd = new MySqlCommand(stm, conn))
                {

                    // var version = cmd.ExecuteScalar().ToString();
                    //  System.Diagnostics.Debug.WriteLine($"MySQL version: {version}");
                    stateText.Text = "Running";
                    stateText.ForeColor = System.Drawing.Color.Green; ;

                    try
                    {
                        using (MySqlDataReader rdr = cmd.ExecuteReader())
                        {

                            while (rdr.Read())
                            {

                               System.Diagnostics.Debug.WriteLine(rdr[0] + " -- " + rdr[1]);

                                /*
                                object[] tempRow = new object[rdr.FieldCount];
                                for (int i = 0; i < rdr.FieldCount; i++)
                                {
                                    tempRow[i] = rdr[i];
                                }
                                dataList.Add(tempRow);
                                */
                            }
                            rdr.Close();
                            System.Diagnostics.Debug.WriteLine("Reader Closed");
                            conn.Close();
                            System.Diagnostics.Debug.WriteLine("Connection Closed");

                        }
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debug.WriteLine(ex);
                    }

                }
            }
        }
        catch(Exception ex)
        {
            System.Diagnostics.Debug.WriteLine(ex);
        }

    }

Hi

Im polling a database view and Im getting this error along with
The ReadAsync method cannot be called when another read operation is pending.

Im closing the datareader and the connection in my code and using using statements but Im getting these errors on repeat queries.

Regards
Aidan

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Hi Thanks this fixed the error messages thanks. But another issue with this is the outputting of messages goes out of sync like below any idea why after a few rounds ? Out put below

       Database Connection Connected
60 -- q=7f76a8712d5d5507e9b4b2ce126051b6ee310246cc7499fa0acf2c386cfc90c1
51 -- q=5c5bdccc0545b4e7b6541c2ab32c92d1e2632205fc89ddeb037e27cbcb111d2b
     Reader Closed
52 -- q=acde8e3015b8c15e66fe0be128718355a3c6fe438732b719f51adb79ca46d7f2
     Timer Ended
51 -- q=5c5bdccc0545b4e7b6541c2ab32c92d1e2632205fc89ddeb037e27cbcb111d2b
      Timer Started 5 secs
52 -- q=acde8e3015b8c15e66fe0be128718355a3c6fe438732b719f51adb79ca46d7f2
53 -- q=0c5bf67caf9e5b73de261364b8c79f87b3b27c37613ab7f6a117e91e3454947a
53 -- q=0c5bf67caf9e5b73de261364b8c79f87b3b27c37613ab7f6a117e91e3454947a
     Database Connection Connected
54 -- q=06189a2e74841588d0b8a49bbc13f97f20170e2b4d1077382c5cff4a4c12940a
54 -- q=06189a2e74841588d0b8a49bbc13f97f20170e2b4d1077382c5cff4a4c12940a
55 -- q=0d1c26e055e45aa89c2dd24c0d17da6c069ef429da3e03fa2af30be36a115f89

  2. 0

    The (or at least "a") problem is here:

    using (conn = new MySqlConnection(connectionString))

    This creates a new connection object, but it doesn’t create a new variable. This code just assigns over top of an existing conn variable declared elsewhere, at a higher scope that is potentially shared with other code. The ReadAsync remark also indicates we have asynchronous code in play (even if it’s not part of this sample), which means the other code could run interspersed with and at the same time as this code — all looking at the same conn variable, but with the referred connection object changing out from under us.

    Worse, the orphaned connection objects could be left hanging open until the GC catches up with them at some undetermined time in the future.

    Instead, you should create a new variable each time:

    using (var conn = new MySqlConnection(connectionString))

    And when this is done, you don’t even need to call conn.Close() manually. The using block will take care of it for you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search