Difference in declaring IDisposable member in using block or at using block declaration?

Question

Hi,

I have the code below:

    using (SqlCommand command = new SqlCommand())
    {

        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Connection = new SqlConnection();
        command.CommandText = "";

        command.Parameters.Add(new SqlParameter("@ExperienceLevel", 3).Direction = System.Data.ParameterDirection.Input);

        SqlDataReader dataReader = command.ExecuteReader();
   }

Is there any functional impact in declaring the SqlConnection where I currently am declaring it as opposed to like so?:

using (SqlCommand command = new SqlCommand())
using (SqlConnection connection = new SqlConnection())

Thanks

Answer 1

Yes, the below code will dispose the SqlConnection correctly, the above won't. A using block (implemented internally as try...finally) ensures that the object will be disposed no matter how you exit the block.

Answer 2

Yes, there's a difference. Disposing the SqlCommand does not automatically dispose the SqlConnection it's associated with. You can leak connections that way, and it will interfere with ADO.NET connection pooling; if you take a look at the database server's activity while this code runs, you'll see new connections being opened and not closed.

You should always be using the second version. In fact, the SqlConnection object is the one that you really need to Dispose. You should always dispose anything that implements IDisposable as soon as possible, but failing to dispose a SqlConnection is particularly dangerous.

Answer 3

Yes, preferably use 2 using blocks, 1 per resource.

In this case you could use just 1 but it should be around the Connection, not around the Command.

But you really don't want to know or care about such details. If a class implements the IDispsoable interface then use its instances in a using() { } block unless there is a special reason not to.

Answer 4

I use the following pattern:

using(var connection = new SqlConnection("ConnectionName"))
using(var command = new SqlCommand())
{
   command.Connection = connection;
   // setup command
   var reader = command.ExecuteReader();
   // read from the reader
   reader.Close();
}