Close Connection / MySqlDataReader doesn't work

Question

I have a class and my MysqlConnection is in there:

public class DB
{
    private static MySqlConnection _Connection;
    public static MySqlConnection Connection
    {
        get
        {
            if(_Connection == null)
            {
                string cs = string.Format("SERVER={0}; DATABASE={1}; UID={2}; PWD={3};", SERVER_ADRESS, DATABASE, UID, PWD);
                _Connection = new MySqlConnection(cs);
            }

            if(_Connection.State == System.Data.ConnectionState.Closed)
                try
                {
                    MessageBox.Show("MySQL Connection ist geschlossen. Öffne Sie");
                    _Connection.Open();
                }
                catch(MySqlException ex)
                {
                    switch (ex.Number)
                    {
                        case 0:
                            MessageBox.Show("Verbindung zum Server konnte nicht hergestellt werden.");
                            break;
                        case 1045:
                            MessageBox.Show("Ungültiger Benutzername/Passwort.");
                            break;
                        default:
                            MessageBox.Show(ex.Message);
                            break;
                    }
                }
            return _Connection;
        }
    }
}

So i can use this connection in all the other classes with DB.Connection.

But now I get "DataReader is already open". But all my DataReader's are in usings.

We start at my login page:

using (loginreader = cmd.ExecuteReader())
            {
                if (loginreader.Read())
                {
                    DB.Connection.Close();
                    return true;
                }
                else
                {
                    DB.Connection.Close();
                    return false;
                }
               loginreader.Close();
            }

I guess this doesn't work. But the first Error Message after log in i get on another class on line 83:

DataTable schema = null;

            using (var schemaCommand = new MySqlCommand("SELECT * FROM " + firmCustomerTablename, connection))
            {
                using (var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                    schema = reader.GetSchemaTable();
                }
            }

which is in a using too. So I don't understand why I get this Error. I guess closing the connections / the DataReaders dont work.

Before this change, i had a connection for every site. But my program had no good performance. So I decided to make 1 connection which is always Open and just call querys to this open connection. And now I get DataReader Errors.

Can someone explain me, why using is not closing the DataReader? And line 83 isn't a DataReader it's a var so i don't know why I get this Error at this line.

Having one static connection for your whole instance is not a good solution. You should be opening connections for every query or update you do. Doing it this way will lead to many more problems beyond what you are describing. If that is causing poor performance then solve that issue (make sure connections are pooling properly, that you dispose/close them in a timely manner, etc.) — stephen.vakil, Sep 08 '17 at 17:07
It kills the performance to open connection each 5 seconds. Because all things will read from database. a click on button opens a window with database content. Another click on another button too etc. — O Jean, Sep 08 '17 at 17:09
It shouldn't kill performance to create connections every 5 seconds. If it is, something is not set up correctly. Have you actually verified that it is happening or just assuming? See: https://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060 and https://stackoverflow.com/questions/26089420/c-sharp-mysql-connection-pooling — stephen.vakil, Sep 08 '17 at 17:15

score 1 · Answer 1 · answered Sep 08 '17 at 17:51

It sounds like your issues are regarding connection state management? I may not be fully understanding what you're asking but by design using statements within the context of connections will close the connection. They are syntactic sugar for try {} catch {} finally. Far too often I see examples of Connection objects, Command Objects, etc. not utilizing IDisposable and not being properly disposed/closed.

In this code, I don't see a connection being opened again for the command to execute.

 DataTable schema = null;

        using (var schemaCommand = new MySqlCommand("SELECT * FROM " + firmCustomerTablename, connection))
        {
            using (var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly))
            {
                schema = reader.GetSchemaTable();
            }
        }

Here is a basic idea:

        using (var conn = new SqlConnection(connectionString: ""))
        {
            conn.Open();

            using (var cmd = new SqlCommand(cmdText: "cmdText", connection: conn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        //
                    }
                }
            }

        }

Documentation: MSDN SqlConnection Class

i would suggest showing how to safely close the connections also as that was his initial issue. You can start by adding `cmd.ExecuteReader(CommandBehavior.CloseConnection)`. This will automatically close the reader which the read loop is finished. Therefore you wont need to check the readers state. You can simple use null propergation and close the the connection afterwards. — thanatorr, Sep 08 '17 at 18:01
I KNOW that your using statements nigate this, but id say it may be worth getting an understandign of whats happening first? before you dive into syntax sugar. — thanatorr, Sep 08 '17 at 18:04

Close Connection / MySqlDataReader doesn't work

1 Answers1