KBK KBK - 8 days ago 10
MySQL Question

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

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);
}

}

Answer

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);
    }
}