ado.net Closing Connection when using “using” stat

Question

I am doing my database access methods to SQL Server like this

  using (SqlConnection con = new SqlConnection(//connection string)
  {
    using (SqlCommand cmd = new SqlCommand(storedProcname, con))
     {
       try{
           con.open();
           //data reader code
       }
       catch
       {

       }
     }
  }

Do I need to be closing or disposing of SqlCommand, or will the using statement take care of that for me? I just don't want connection hanging open Thanks

Answer 1

As an aside, you can make the code more concise and readable as follows:

 using (SqlConnection con = new SqlConnection(/*connection string*/))
 using (SqlCommand cmd = new SqlCommand(storedProcname, con))
 {
    //...
 }

Answer 2

As Phil said, the using clause will take care of it for you. When compiled down it wraps the connection create in a try .. finally and places the connection disposal call inside the finally.

For more information you can see the using statement article at msdn.

Answer 3

Yes your code will close the connection, however that typcally means release back to the connection pool to be truely closed later.

If you execute this snippet of code, and then do an sp_who and observe that your connection is still there, that would be why.

If you absolutely need the connection truely closed (an edge case to be sure) then use the ClearAllPools static method of ths SqlConnection

Answer 4

Using keyword will automatically close the connection for you so you don't need to worry about calling connection.close() at the end every time.

Answer 5

I think "using" was not required for SqlCommand. "Using" for SqlConnection would have done the job for you alone. In fact you connection is submitted to Connection pool.

Answer 6

when the scope

using (SqlConnection con = new SqlConnection(//connection string) 
{
}

will over , connection will automatically be disposed by runtime. so don't worry