There is already an open DataReader associated with this Connection which must be closed first. C# with mysql database

Question

Error Message: MySqlException
Exception Type: MySql.Data.MySqlClient.MySqlException
Error Location: There is already an open DataReader associated with this Connection which must be closed first.

public static DataTable SelectMethod(string _select)
        {
            DataTable _dtsel = new DataTable("tab1");
            try
            {
                ConOpen();
                using (MySqlCommand cmd = new MySqlCommand(_select, con))
                {
                    MySqlDataAdapter _mysel = new MySqlDataAdapter(cmd);
                    _mysel.Fill(_dtsel);
                }
            }
            catch (Exception ex) { SendErrorToText(ex); }
            return _dtsel;
        }

public static DataTable SelectMethod(string _select)
        {
            DataTable _dtsel = new DataTable("tab1");
            try
            {
                ConOpen();
                using (MySqlCommand cmd = new MySqlCommand(_select, con))
                {
                    MySqlDataAdapter _mysel = new MySqlDataAdapter(cmd);
                    _mysel.Fill(_dtsel);
                }
            }
            catch (Exception ex) { SendErrorToText(ex); }
            return _dtsel;
        }

Usually `DataAdapters` internally do use a DataReader because datareaders are fast. The problem is The connection can only have one opened datareader unless SQL Server MARS or similar is enabled (I know it is mysql, and I do not know if mysql has a MARS equivalent). You have some code you are using a DataReader but forgot to call `.Close()` after reading it. — Cleptus, Sep 06 '19 at 06:36
@er-sho He must not close the connection. He must close the opened datareader. — Cleptus, Sep 06 '19 at 06:39
Possible duplicate of [There is already an open DataReader associated with this Command which must be closed first](https://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c) — Cleptus, Sep 06 '19 at 06:40
The problem with "data helper" classes such as this is that they don't manage resources correctly. `con` is static, since the methods that use it are static, which means that any number of threads are trying to compete for use of the connection. — madreflection, Sep 06 '19 at 07:05
And also once you have open a connection the second thing you need to do is to close it first before opening a new connection. — Cyrille Con Morales, Sep 06 '19 at 07:09
It seems likely that you're sharing the `con` variable between multiple methods or threads. This isn't supported. Every request should open and use its own `MySqlConnection`. See https://mysqlconnector.net/troubleshooting/connection-reuse/ — Bradley Grainger, Sep 06 '19 at 14:34

Cyrille Con Morales · Answer 1 · 2019-09-06T07:04:39.357

0

You must close the connection and this is where the connection must be close

public static DataTable SelectMethod(string _select)
    {
        DataTable _dtsel = new DataTable("tab1");
        try
        {
            ConOpen();
            using (MySqlCommand cmd = new MySqlCommand(_select, con))
            {
                MySqlDataAdapter _mysel = new MySqlDataAdapter(cmd);
                _mysel.Fill(_dtsel);
            }

        }
        catch (Exception ex) 
        { SendErrorToText(ex); }
        finally
         {
          ConClose();
         }
        return _dtsel;
    }

  MysqlConnection conn = new MysqlConnection(connectionString);

  private void ConClose(){
  conn.Close();
  }

edited Sep 06 '19 at 07:04

answered Sep 06 '19 at 06:56

Cyrille Con Morales

918
1
6
21

1

The problem is that in a code he has not included in the question he opened a DataReader he did not close. This answer probably does not address the problem, but is a nice advice on connection closing. As a side note, I usually prefer closing the connection in the `finally` of the try-catch so if exceptions are raised, the connection is closed. – Cleptus Sep 06 '19 at 07:01
the above method is common and use in my all project with for lop – Yogesh Sabva Sep 07 '19 at 07:06

score 0 · Answer 2 · edited Sep 06 '19 at 07:14

0

You should close connection after executing your query every time when you are open the connection like this:

DataTable _dtsel = new DataTable("tab1");
try
{
    ConOpen();
    using (MySqlCommand cmd = new MySqlCommand(_select, con))
    {
         MySqlDataAdapter _mysel = new MySqlDataAdapter(cmd);
         _mysel.Fill(_dtsel);
         con.close();
    }
}
catch (Exception ex) { SendErrorToText(ex); }
return _dtsel;

edited Sep 06 '19 at 07:14

Cleptus

3,446
4
28
34

answered Sep 06 '19 at 06:56

Saeed Ahmad

63
5

the above mthod use in for loop – Yogesh Sabva Sep 07 '19 at 07:06

There is already an open DataReader associated with this Connection which must be closed first. C# with mysql database

2 Answers2