CODEWITHSUNDEEP
c#
Amine Fellous
Amine Fellous

Reputation: 83

MySQL connection doesnt close

I'm hitting a wall right now in my project. I have a form, with some listboxes, and add button. When I click on the add button, I got a small dialog form with a textbox, an OK and a Cancel button. The app is connected to a MySQL database. So whenever the text change, the program checks if the name exists in the database, disable the OK button and the textbox turn red if the name exists, otherwise it turns them back to normal. Works without a problem when I'm writing and the name doesn't exist, and when it does, it turns red, like it should. And here is the problem. After turning red, it doesn't go back to normal, even when I enter a valid name.

Here is the code :

private void DialogTxb_TextChanged(object sender, EventArgs e)
{
    //ConnexionData class where i do all the SQL manipulation
    MySqlDataReader selection = ConexionData.CheckSectionName(DialogTxb.Text);
    while (selection.Read())
    {
        if (selection.HasRows)
        {
            DialogOk.Enabled = false;
            toolTip1.Show("La section existe", TooltibLb);
            DialogTxb.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffaaaa");
        }
        else
        {
            toolTip1.Hide(TooltibLb);
            DialogTxb.BackColor = Color.White;
            DialogOk.Enabled = true;
        }
    }
    ConexionData.ConexionClose();//Method to close connection
}

I think I have an idea where the problem is but have don't know why it happens and how to solve it. If I simply exit the form and try to do anything else, like select another element from a listbox which will trigger some database interaction, the program close and visual studio provide me with info on the error:"The connexion is already open". I tried to close in other moments of the code, looked for some solutions on the internet, tried MysqlConection.ClearAllPools(), and still the same issue.

Connexion opens and closes properly in other parts of the application.

Thanks for your attention.

Edit

Here are the methods in ConexionData

class ConnexionData
{
    private static MySqlConnection Connexion;

    public static MySqlCommand Command;

    //Many other methods
    //.......

    public static void ConnexionClose()
    {
        Connexion.Close();
    }

    public static MySqlDataReader CheckSectionName(string name)
    {
        Connexion.Open();
        string checkSectionName = ("Select sectionName from section where sectionName = '" + name + "'");
        Command.CommandText = checkSectionName;
        Reader = Command.ExecuteReader();
        return Reader;
    }
}

I use the Connexion.Close() in many parts of the program. I have 2 Data Grid views, and some list box where i load data from the database. I open and close those DGV and change values in listbox and all work fine. Then i try my small form to insert a new values on those tables, test it and close it (actually i insert nothing, i simply close it and there is a ConexionData.Close() in the close event) and here where the problem of connection start.

Edit-Solved

I finally solved the problem. I turned Private MysqlConection to public, and directly closed the property after closing the dialog.

Upvotes: 0

Views: 172

Answers (2)

Akhilesh Pandey
Akhilesh Pandey

Reputation: 1

In case connection is not closing then you can try to call close() connection or sqldatareader before executing method "CheckSectionName()". FYR Below is some example Let me know, if it helps.

Approch 1:

    System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection();
     if(sqlConn.State!= System.Data.ConnectionState.Closed)
        {
           sqlConn.Close();
       }
 System.Data.SqlClient.SqlDataReader SqlReader= new System.Data.SqlClient.SqlDataReader();

                    if(!SqlReader.IsClosed)
                    {
                        SqlReader.Close();
                    }
    MySqlDataReader selection = ConexionData.CheckSectionName(DialogTxb.Text);

Approch 2: We can use "using" clause

using (MySqlDataReader selection = ConexionData.CheckSectionName(DialogTxb.Text))

Approch 3: Add close() and dispose() into finally block.

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

if selection.Read() returns true it means you have at least 1 record. It seems you are looking for

   private void DialogTxb_TextChanged(object sender, EventArgs e) { 
     try {
       //TODO: check ConexionData.CheckSectionName (re-)creates connection if required
       using (MySqlDataReader selection = ConexionData.CheckSectionName(DialogTxb.Text)) {
         if (selection.Read()) {
           // Name exists (we've read it)

           DialogOk.Enabled = false;
           toolTip1.Show("La section existe", TooltibLb);
           DialogTxb.BackColor = System.Drawing.ColorTranslator.FromHtml("#ffaaaa");
         }
         else {
           // Name doesn't exist: the cursor is empty (so we've failed to read it)

           toolTip1.Hide(TooltibLb);
           DialogTxb.BackColor = Color.White;
           DialogOk.Enabled = true;
         }
       }
     }
     finally { 
       // finally: rain or shine the connection should be closed
       ConexionData.ConexionClose(); // Method to close connection
     }
   }

Upvotes: 3

Related Questions