Benefits of "Using" a Block to Initialize a SqlCommand
I understand the concept of using a block using
when connecting to the sql server from your application as it will close the connection as soon as it goes out of scope and save us time writing the blocks try catch finally
.
But my question is, are there any advantages to using using
when initializing 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 are the possible benefits I can get by putting SqlCommand initialization on use 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 stuff I have searched the web is about closing a connection as soon as it goes out of scope. Yes, I understand that, but including the SqlCommand in the block use will make it more efficient or not?
Any advice or pointers are greatly appreciated.
source to share
You can see this clearly by looking at the source code.
This is the implementation SqlCommand.Dispose
:
override protected void Dispose(bool disposing)
{
if (disposing)
{
_cachedMetaData = null;
}
base.Dispose(disposing);
}
As you can see, this means little. But 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 be disposed of as soon as nobody references it, since it still has a reference to the finalizer queue .
When you complete with a SqlCommand
statement using
, the most important part of it is that its base class (component) calls GC.SupressFinialize
, which removes the reference to the finalizer queue and allows the object to be collected after GC starts.
This is why SqlCommand
implements IDisposable
and should be removed.
source to share