Benefits of "using" block for SqlCommand initialization

Question

I understand the concept of using using block when making a connection to sql server from your application, as it will close the connection as soon as it goes out of scope and saving us time to write try catch finally blocks.

But my question is, Is there any benefits of using using when initialization a SqlCommand normally I would do something like:

string cs = ConfigurationManager.ConnectionStrings["CS1"].ConnectionString;

using(SqlConnection con = new SqlConnection(cs))
{
    SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.City", con);

        con.Open();

        DropDownList1.DataSource =  cmd.ExecuteReader();
        DropDownList1.DataTextField = "City";
        DropDownList1.DataValueField = "ID";
        DropDownList1.DataBind();
}

But what possible benefits I can get by putting SqlCommand initialization in a using block?

string cs = ConfigurationManager.ConnectionStrings["CS1"].ConnectionString;

using(SqlConnection con = new SqlConnection(cs))
{
    using(SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.City", con))
    { 
        con.Open();
        DropDownList1.DataSource =  cmd.ExecuteReader();
        DropDownList1.DataTextField = "City";
        DropDownList1.DataValueField = "ID";
        DropDownList1.DataBind();
    }
}

All the material I have searched online talks about the connection being closed as soon as it goes out of scope, Yes I understand that but putting SqlCommand in using block will it make it any more efficient or not?

Any advice or pointers are much appreciated thank you.

score 4 · Accepted Answer · edited May 23 '17 at 12:14

You can see this clearly when looking at the source code.

This is the implementation of SqlCommand.Dispose:

override protected void Dispose(bool disposing) 
{
    if (disposing) 
    {
        _cachedMetaData = null;
    }

    base.Dispose(disposing);
}

As you can see, this doesn't do much. But, when looking at base.Dispose:

public void Dispose() 
{
    Dispose(true);
    GC.SuppressFinalize(this);
}

SqlCommand inherits from DbCommand, which in turn inherits from Component. Component implements a finalizer, which means that the object will not get disposed once no one is referencing it, as it still has a reference to the finalizer queue.

When you wrap SqlCommand with a using statement, the most important part of it is that its base class (Component) calls GC.SupressFinialize, which removes the reference to the finalizer queue and lets the object be collected once the GC kicks in.

That is why SqlCommand implements IDisposable, and should be disposed.

Benefits of "using" block for SqlCommand initialization

1 Answers1