There is already an open DataReader associated with this Connection which must be closed first

Question

I am using Visual Studio 2010 (C#) with mysqlConnector and everything seems to be fine. However, when I try to request something from the server I get this error:

"There is already an open DataReader associated with this Connection which must be closed first."

This is my code:

gc.connect();

List<Conseiller> conseillers = gc.getAllConseillers();

--

public void connect() 
{
    string connStr = "SERVER=localhost;UID=root;DATABASE=Projet;Password=root";
    oConn = new MySqlConnection(connStr);

    try 
    {
        oConn.Open();
        Console.WriteLine("Successfully connected to the data base");
    } 
    catch (OdbcException caugth) 
    {
        /* Traitement de l'erreur */
        Console.WriteLine(caugth.Message);
    }
}

-- 

public List<Conseiller> getAllConseillers()
{
    MySqlCommand oComm = oConn.CreateCommand();

    oComm = oConn.CreateCommand();

    Console.WriteLine("SELECT * FROM conseillers");
    oComm.CommandText = "SELECT * FROM conseillers";

    MySqlDataReader oReader = oComm.ExecuteReader(); // Error here
}

Where I am wrong ?

Answer 1

A few suggestions that may help:

First, in your code above you have called CreateCommand twice and don't need to.

Secon, you can instantiate your Command a little different to make this easier to read:

MySqlCommand oComm = new MySqlCommand("Select * from conseillers", oConn);

then call the ExecuteReader.

Third, your code above doesn't show when the connection is open. Are you sure it's open? Are you sure you haven't already called a data reader with the open connection and didn't close it?

Fourth, you should always open the connection as late as possible and close it as early as possible. The code you have seems like you are going to open the connection and leave it open but I'm not really sure of your intent.

I would suggest using the Using syntax:

Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        Try
            While reader.Read()
                Console.WriteLine(String.Format("{0}, {1}", _
                    reader(0), reader(1)))
            End While
        Finally
            ' Always call Close when done reading.
            reader.Close()
        End Try
    End Using

Modify the above code for your situation....

Answer 2

You're not disposing of your objects, which basically means that your previous call to getAllConseillers, or a similar method, opened a data reader that is still open.

The following objects in your question are disposable (ie. implements IDisposable), you should dispose of them all:

1. MySqlConnection
2. MySqlCommand
3. MySqlDataReader

Basically, I would change the code as shown to this:

using (var gc = new Whatever())
{
    gc.connect();
    List<Conseiller> conseillers = gc.getAllConseillers();
}

--

public void connect()
{
    string connStr = "SERVER=localhost;UID=root;DATABASE=Projet;Password=root";
    oConn = new MySqlConnection(connStr);
    try
    {
        oConn.Open();
        Console.WriteLine("Successfully connected to the data base");
    }
    catch (OdbcException ex)
    {
        /* Traitement de l'erreur */
        Console.WriteLine(ex.Message);
        oConn.Dispose();
        oConn = null;
        // optional: throw;
    }
}

-- 

public List<Conseiller> getAllConseillers()
{
    using (MySqlCommand oComm = oConn.CreateCommand())
    {
        Console.WriteLine("SELECT * FROM conseillers");
        oComm.CommandText = "SELECT * FROM conseillers";
        using (MySqlDataReader oReader = oComm.ExecuteReader()) // No error here
        {
            // process the result in oReader here
            return ...;
        }
        ...
    }
    ...
}

Answer 3

don't try to separate the connect with the get data. The call to Open may in-fact not go to the database at all and you will not detect issues at that point. Note the using statement to close the connection. add SEH as required

List<Conseiller> conseillers = gc.getAllConseillers();

public void getAll() {
  string connStr = "SERVER=localhost;UID=root;DATABASE=Projet;Password=root";
  using (oConn = new MySqlConnection(connStr))
  using (MySqlCommand oComm = oConn.CreateCommand())
  {
    oConn.Open();
    oComm.CommandText = "SELECT * FROM conseillers";

    MySqlDataReader oReader = oComm.ExecuteReader(); // no Error here
    // user reader here
    } catch (OdbcException caugth) {
        /* Traitement de l'erreur */
        Console.WriteLine(caugth.Message);
    }
 }