How to manage the mysql connection in correct way without getting a error

Question

I am new to the C# and My method showing a error in try catch connection is already open code as follow, when I closed it from Class method then Form getting a error invalid connection. here if put all code in FORM it is working. but here I get MysqlDataReader as a return value. how can I solve this error.

CLASS

   //select all categories
    public MySqlDataReader SelectCategory() {

        try
        {
            MySqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT * FROM categories WHERE online = 1";

            connection.Open();
            MySqlDataReader categories = cmd.ExecuteReader();
            return categories;
        }

        catch (Exception ex) {
            MessageBox.Show(ex.Message);
            return null;
        }


    }

FORM

    public void show()
    {
        MySqlDataReader rd = db.SelectCategory();

        try
        {
            while (rd.Read())
            {
                listBox1.Items.Add(rd.GetString(1));
            }
        }

        catch (Exception ex) {
            MessageBox.Show(ex.Message);
        }

    }

You don't close the connection ever, so why do you open it each time? And returning readers is not a good thing. Return data. You never even close the reader either. — Sami Kuhmonen, Nov 29 '16 at 06:53
` public DBConnect() { Initialize(); ; } private void Initialize() { server = "localhost"; database = "pos3"; uid = "root"; password = ""; string connectionString; connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";"; connection = new MySqlConnection(connectionString); }` — KBK, Nov 29 '16 at 07:15

fubo · Accepted Answer · 2016-11-29T07:46:53.327

3

I would use using which cares about disposing variables and closing connections.

CLASS:

public List<string> SelectCategory()
{
    List<string> result = new List<string>();
    string Command = "SELECT * FROM categories WHERE online = 1";
    using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
    {
        mConnection.Open();
        using (MySqlCommand cmd = new MySqlCommand(Command, mConnection))
        {
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {                        
                    result.Add(reader.GetString(1));
                }
            }
        }
    }
    return result;
}

FORM:

public void show()
{
    try
    {
        foreach(string item in SelectCategory())
        {
            listBox1.Items.Add(item);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

edited Nov 29 '16 at 07:46

answered Nov 29 '16 at 07:00

fubo

44,811
17
103
137

Sidenote - to reduce overhead, I would select only the required column in your statement – fubo Nov 29 '16 at 07:37
I saw it in your answer also... what is that meaning `List` pls help – KBK Nov 29 '16 at 07:40
I didnt use `string Command = "SELECT * FROM categories WHERE online = 1"; using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))` but I close the connection before return .. it is working – KBK Nov 29 '16 at 08:01
you're welcome, `using` is closing the connection for your. Exceptionhandling inside the `show()` should be enough – fubo Nov 29 '16 at 08:04
got it... thanx – KBK Nov 29 '16 at 08:05

sujith karivelil · Answer 2 · 2016-11-29T07:23:50.153

The DataReader is an exceptional case used for retrieving data from the database, which always requires an open connection to get values from the DataReader. In your case you are passing the MySqlDataReader to the calling method, So you cannot close the connection from the called method since reader requires the opened connection. so the possibility for you is to Close the connection after closing the DataReader.

Another issue you may face is the connection problem(your current issue), From your code it is clear that you have not closed the connection, so when you call the method for first time, everything will be fine. when the second call triggers the connection's current state will be open so it will throws such exception when you tried to reopen the connection.

Don't worry you can use either one of the following to solve the issue(I'm not sure is such suggestions are valid answers for this community or not, forgive me if not)

Pass your UI control to the class method and Add items to the list

In this case the method is defined inside another class so its not possible to use the UI item there, So better option for is to pass the UI element, the ListBox to that method and make it filled using the reader. For that the code will be like this:

public MySqlDataReader SelectCategory(ListBox listBox1) 
{

  // fubo's answer here

}

Generate a method for closing connection after checking the connection state, and call that method when while completes its iterations.

The calling method will be like the following:

   try
    {
        using (MySqlDataReader reader = db.SelectCategory())
        {
            while (reader.Read())
            {
                listBox1.Items.Add(reader.GetString(1));
            }
        }
        db.CloseConnection(); // will be the method to close the connection
    }

use DataTable instead for DataReader : Get data to a DataTable and then bind the required list using that DataTable

@KBK : coool.. always happy to help you – sujith karivelil Nov 29 '16 at 08:06 — sujith karivelil, Nov 29 '16 at 08:06

score 0 · Answer 3 · answered Nov 29 '16 at 07:36

always use finally to close your connection, whatever happen you must close your connection after opening.

    public MySqlDataReader SelectCategory() {

            try
            {
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM categories WHERE online = 1";

                connection.Open();
                MySqlDataReader categories = cmd.ExecuteReader();
                return categories;
            }        
            catch (Exception ex) {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }       

        }

How to manage the mysql connection in correct way without getting a error

3 Answers3