I have c# class that make me to connect the database and make some operations on it.

#region Var
        private readonly string DbPath;
        public MySqlConnection DbConn;

        #endregion

        #region Constructor

        public ClsDb()
        {
            DbPath = "SERVER= "   Db.Default.ServerName   "; "  
                    "DATABASE= "   Db.Default.DbName   "; "  
                    "UID= "   Db.Default.UserName   "; "  
                    "PWD= "   Db.Default.UserPass   "; "  
                    "PORT= "   Db.Default.ThePort "; sslmode=none";
            DbConn = new MySqlConnection(DbPath);
        }
        #endregion

as you see I declared (mysqlconnection) :

DbConn = new MySqlConnection(DbPath);

so after that I check the connection:

public bool CheckConn()
{
    try
    {
        if (DbConn.State == ConnectionState.Open)
        {
            DbConn.Close();
        }

        if (DbConn.State == ConnectionState.Closed)
        {
            DbConn.Open();
            return true;
        }
        return false;
    }
    catch
    {
        MessageBox.Show(Msgs.Default.ConnErr,
                          Settings.Default.ComName,
                          MessageBoxButtons.OK,
                          MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
        return false;
    }

}

and after that I'm trying to execute SQL statement by method:

public void RunSql(string xSql, MySqlParameter[] xPar)
        {
            //...
            MySqlCommand xCmd = new MySqlCommand(xSql, DbConn);

            // ....
            if (xPar != null)
            {
                xCmd.Parameters.AddRange(xPar);
            }

            //...
            xCmd.ExecuteNonQuery();
        }

but it gives me the following error:

"Connection must be valid and open."

when I checked the code by the breakpoints I got that everything is ok and it opens the connection very well but when it execute (public void RunSql) and exactly on the line:

MySqlCommand xCmd = new MySqlCommand(xSql, DbConn);

when I tried to solve it I changed (public MySqlConnection DbConn) to (public Static MySqlConnection DbConn) and it worked but I needed to know why that happened although I declared the variable as public?? and why it worked when I changed to Static??

CodePudding user response：

I'm using my own class for this. In it isn't necessary to close the connection because uses the 'using'.

The class:

Connection (return open connection)

public static MySqlConnection GetConn()
    {
        MySqlConnection conn = new MySqlConnection(ConnString);

        try
        {
            conn.Open();
        }
        catch (MySqlException)
        {
            throw;
        }
        
        return conn;
    }

Insert Commands (accept params)

public static int InsertSqlCommand(string db, params (string param, object value)[] listParams)
    {
        using (MySqlConnection conn = GetConn())
        {
            using (MySqlCommand cmd = new MySqlCommand(db, conn))
            {
                
                foreach ((string dbLocal, object incremento) in listParams)
                    cmd.Parameters.AddWithValue(dbLocal, incremento);
                

                int linhasAfetadas = cmd.ExecuteNonQuery();

                return linhasAfetadas;
            }
        }
    }

Select Command (accept params)

 public static DataTable SelectSqlCommand(string db, params (string param, object value)[] listParams)
    {
        DataTable dttoken = new DataTable();

        using (MySqlConnection conn = GetConn())
        {
            using (MySqlCommand cmd = new MySqlCommand(db, conn))
            {
                
                foreach ((string dbLocal, object incremento) in listParams)
                     cmd.Parameters.AddWithValue(dbLocal, incremento);
                

                using (MySqlDataAdapter sqlDA = new MySqlDataAdapter(cmd))
                {
                    sqlDA.Fill(dttoken);
                    return dttoken;
                }
            }
        }
    }