SQL Connection

[moone009]

In my form I have 5 text boxes where the user will be updating roughly 21 tables in the DB. Do I have to keep building a new sql connection for each event? Or is there a way for me to only have to write the connection code once?

Basically for this button I only want to build the sql connection once. But since Im new to this Im building it for each event in the code.

string strconn = "SERVER=44.6.53.34;User=sa;password=56fgq3546fd5qFDGF;Database=addressstagingtable";
SqlConnection conn = new SqlConnection(strconn);

private void xmlBTN_Click(object sender, EventArgs e)
        {
string strconn6 = "SERVER=44.6.53.34;User=sa;password=56fgq3546fd5qFDGF;Database=addressstagingtable";
SqlConnection conn6 = new SqlConnection(strconn6);

    try
    {
        string sqlQuery6 = @"Update Lalocation Set State = @State where state = 'XX'";
        SqlCommand cmd6 = new SqlCommand(sqlQuery6,conn6);
        cmd6.Parameters.Add("@State",SqlDbType.NVarChar,2).Value = statetxt.Text;
        conn6.Open();
        cmd6.ExecuteNonQuery();
    }
    catch (Exception)
    {
        MessageBox.Show("Table: LALOCATION FIELD: State did not update, please contact your C.A.R.T.S System Administrator", "MPL");
    }

string strconn7 = "SERVER=44.6.53.34;User=sa;password=56fgq3546fd5qFDGF;Database=addressstagingtable";
SqlConnection conn7 = new SqlConnection(strconn7);

    try
    {
      string sqlQuery7= @"Update Lalocation Set zip = @Zip WHERE Zip = 'XXXXX'";
        SqlCommand cmd7 = new SqlCommand(sqlQuery7,conn7);
        cmd7.Parameters.Add("@Zip",SqlDbType.NVarChar,2).Value = ziptxt.Text;
        conn7.Open();
        cmd7.ExecuteNonQuery();
    }
    catch (Exception)
    {
        MessageBox.Show("Table: LALOCATION FIELD: State did not update, please contact your C.A.R.T.S System Administrator", "MPL");
    }


        }


        }

    }

[Mitja Bonca]

You can have (use) only one connection through all the code, and ONLY one connection string.
It would be even better to create connection string as global (too seeable in the whole class and static):
Take into yours changed code:

static string connString = "SERVER=44.6.53.34;User=sa;password=56fgq3546fd5qFDGF;Database=addressstagingtable";
        private void xmlBTN_Click(object sender, EventArgs e)
        {            
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                sqlConn.Open();
                string sqlQuery6 = @"Update Lalocation Set State = @State where state = 'XX'";
                SqlCommand cmd6 = new SqlCommand(sqlQuery6, sqlConn);
                cmd6.Parameters.Add("@State", SqlDbType.NVarChar, 2).Value = statetxt.Text;
                try
                {
                    cmd6.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    MessageBox.Show("Table: LALOCATION FIELD: State did not update, please contact your C.A.R.T.S System Administrator", "MPL");
                }
                string sqlQuery7 = @"Update Lalocation Set zip = @Zip WHERE Zip = 'XXXXX'";
                SqlCommand cmd7 = new SqlCommand(sqlQuery7, sqlConn);
                cmd7.Parameters.Add("@Zip", SqlDbType.NVarChar, 2).Value = ziptxt.Text;
                try
                {
                    cmd7.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    MessageBox.Show("Table: LALOCATION FIELD: State did not update, please contact your C.A.R.T.S System Administrator", "MPL");
                }
            }
        }