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?

yes, or you could use the using statement - see [link](https://msdn.microsoft.com/en-us/library/dw70f090(v=vs.110).aspx) and [link](http://stackoverflow.com/questions/4389506/ado-net-closing-connection-when-using-using-statement) — phooey, Dec 04 '16 at 09:40
So now there are 4 people giving you the exact same answer. If you get 3 answers and 1 comment within 2 minutes with the exact same answer, maybe you did not search at all before asking this question? — Tony_KiloPapaMikeGolf, Dec 04 '16 at 09:44
As an aside, you probably meant to say "parameterised query", not "parameterised connection". — stakx - no longer contributing, Dec 04 '16 at 09:56

mybirthname · Accepted Answer · 2016-12-04T09:45:31.560

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();
}

Does .net automatically release resources if they are not released as in my case of example.. I mean after sometime — Learning, Dec 04 '16 at 09:43
Generally yes, but you don't know when that is going to happen. Also, it might never happen, e.g. in case of killing a process. — Zoran Horvat, Dec 04 '16 at 09:45
@Learning Just read https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx about Connection Pool. I think this will make your questions cleared. — mybirthname, Dec 04 '16 at 10:03

score 1 · Answer 2 · answered Dec 04 '16 at 09:40

You should close it or otherwise underlying resources would remain used until the finalizer is executed.

The best option is to actually dispose the connection (which is equal to closing it in particular case of database connection object):

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();
}

Object will be disposed when it leaves the `using` block. If `using` is inside the `try` block, and an exception occurs, then the connection would be disposed *before* execution moves to `catch`/`finally`. — Zoran Horvat, Dec 04 '16 at 09:43

score 0 · Answer 3 · answered Dec 04 '16 at 09:41

You could use 'using'

using(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();
}

Do i need to close the connection when using parametrised connection

3 Answers3