Do i need to close the connection when using parametrised connection

Question

I have a piece of code shown below where I open connection and execute a command on it. Do I have to close this connection or what is the best way to write this piece of code?

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

if (con.State == ConnectionState.Closed)
{
    con.Open();
}

string sql = "INSERT INTO RegisterUser(Name,LastName,email,Nationality,Country) VALUES (@param1,@param2,@param3,@param4,@param5)";

SqlCommand cmd = new SqlCommand(sql, con);

cmd.Parameters.Add("@param1", SqlDbType.NVarChar, 200).Value = txtName.Text;
cmd.Parameters.Add("@param2", SqlDbType.NVarChar, 100).Value = txtLastName.Text;
cmd.Parameters.Add("@param3", SqlDbType.NVarChar, 50).Value = txtEmail.Text;
cmd.Parameters.Add("@param4", SqlDbType.NVarChar, 50).Value = ddCountry.SelectedItem.Value.ToString();
cmd.Parameters.Add("@param5", SqlDbType.NVarChar, 50).Value = txtCountryCode.Text;

cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

Will the above code not release memory in case connection need to be closed?

Answer 1

Yes you need always to close the connection. Also you don't need the first if.

using(SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{

    con.Open();

    string sql = "INSERT INTO RegisterUser(Name,LastName,email,Nationality,Country) VALUES (@param1,@param2,@param3,@param4,@param5)";

    SqlCommand cmd = new SqlCommand(sql, con);
    cmd.Parameters.Add("@param1", SqlDbType.NVarChar, 200).Value = txtName.Text;
    cmd.Parameters.Add("@param2", SqlDbType.NVarChar, 100).Value = txtLastName.Text;
    cmd.Parameters.Add("@param3", SqlDbType.NVarChar, 50).Value = txtEmail.Text;
    cmd.Parameters.Add("@param4", SqlDbType.NVarChar, 50).Value = ddCountry.SelectedItem.Value.ToString();
    cmd.Parameters.Add("@param5", SqlDbType.NVarChar, 50).Value = txtCountryCode.Text;

    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
}

So using will automatically close your connection for you. This will happen even if your code throws an exception. using represents try/catch/finally block. In this way you are guaranteed that the connection is return to Connection Pool if error happens.

try
{
    Sqlconnection conn = new SqlConnection("your conn string");
}
catch(Exception ex)
{
    throw;
}
finally
{
    conn.Close();
}