technical question about the internal functions

Question

Hello, 

 

Here is a demo of a program doing what it should do "Add", "Update","Delete" data. 

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;

namespace dss
{
    public partial class Form1 : Form

    {
        SqlConnection con = new SqlConnection("Data Source=JAKHAN-PC\\sqlexpress;Initial Catalog=JG_Test;Integrated Security=True");
        string connString = "Data Source=JAKHAN-PC\\sqlexpress;Initial Catalog=JG_Test;Integrated Security=True";
        public Form1()
        {
            InitializeComponent();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand();
            try
            {
                string sql = "SELECT * FROM Members WHERE MemberId = '"+ tbID.Text +"' ";
                cmd.Connection = con;
                cmd.CommandText = sql;
                con.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tbID.Text = reader["MemberId"].ToString();
                    tbName.Text = reader["Name"].ToString();
                    tbMobile.Text = reader["Mobile"].ToString();
                    tbEmail.Text = reader["Email"].ToString();
                    tbAddress.Text = reader["Address"].ToString();
                }
                con.Close();
                sql= "SELECT * FROM Payments WHERE MemberId  = '" + tbID.Text + "' ";
                cmd.Connection = con;
                cmd.CommandText = sql;
                con.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tbID.Text = reader["MemberId"].ToString();
                    tbYear.Text = reader["Year"].ToString();
                    tbAmount.Text = reader["Amount"].ToString();

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

        private void btnAdd_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                string Sql = "INSERT INTO Members ( MemberId, Name, Address, Mobile, Email ) VALUES " + " (@Id, @name, @address, @mobile, @email)";
                using (SqlCommand cmd = new SqlCommand(Sql, con))
                {
                    cmd.CommandText = Sql;
                    cmd.Parameters.AddWithValue("@Id", tbID.Text);
                    cmd.Parameters.AddWithValue("@name", tbName.Text);
                    cmd.Parameters.AddWithValue("@address", tbAddress.Text);
                    cmd.Parameters.AddWithValue("@mobile", tbMobile.Text);
                    cmd.Parameters.AddWithValue("@email", tbEmail.Text);
                    cmd.ExecuteNonQuery();

                    Sql = "INSERT INTO Payments (MemberId, [Year], [Amount] ) VALUES " + "(@id, @year, @amount)";
                    cmd.Parameters.Clear();
                    cmd.CommandText = Sql;
                    cmd.Parameters.AddWithValue("@Id", tbID.Text);
                    cmd.Parameters.AddWithValue("@year", tbYear.Text);
                    cmd.Parameters.AddWithValue("@amount", tbAmount.Text);
                    cmd.ExecuteNonQuery();

                    MessageBox.Show("Data Added");
                    tbID.Clear();tbName.Clear();tbAddress.Clear();tbMobile.Clear();tbEmail.Clear();tbYear.Clear();tbAmount.Clear();
                    con.Close();
                }
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                string Sql = "UPDATE Members SET MemberId = '" + tbID.Text + "', Name = '" + tbName.Text + "', Address = '" + tbAddress.Text + "', Mobile = '" + tbMobile.Text + "', Email = '" + tbEmail.Text + "' WHERE MemberId = '" + tbID.Text + "' ";
                cmd.CommandText = Sql;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

                Sql = "UPDATE Payments SET MemberId = '" + tbID.Text + "', Year = '" + tbYear.Text + "', Amount = '" + tbAmount.Text + "' WHERE MemberId = '" + tbID.Text + "' ";
                cmd.CommandText = Sql;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();

                MessageBox.Show("Data Updated");
                tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
                con.Close();
            }
            catch (Exception error)
            {
                MessageBox.Show(error.ToString());
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                string Sql = "DELETE FROM Members WHERE MemberId = '" + tbID.Text + "' ";
                cmd.CommandText = Sql;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

                Sql = "DELETE  FROM Payments WHERE MemberId = '" + tbID.Text + "' ";
                cmd.CommandText = Sql;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
                MessageBox.Show("Data Deleted");

                con.Close();
            }
            catch (Exception error)
            {
                MessageBox.Show(error.ToString());
            }

        }

        private void btnReset_Click(object sender, EventArgs e)
        {
             tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
        }


        private void btnExit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}

My question is "WHY" I have to declare the connection twice in line 11 & 12, ( remeber it's a learning and uderstanding process ) is it wrong to have it declared twice or is it correct ? or there is a more simple short and smarter way to do it ?