I saw that in most samples SqlCommand
was used like this
using (SqlConnection con = new SqlConnection(CNN_STRING)) { using (SqlCommand cmd = new SqlCommand("Select ID,Name From Person", con)) { SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); return ds; } }
I know why we are using the using
statement. But SqlCommand
doesn't include a Close()
method, so should we really use it within a using
statement?
Executes commands such as Transact-SQL INSERT, DELETE, UPDATE, and SET statements. Retrieves a single value (for example, an aggregate value) from a database. Sends the CommandText to the Connection and builds an XmlReader object. You can reset the CommandText property and reuse the SqlCommand object.
A SqlCommand object allows you to specify what type of interaction you want to perform with a database. For example, you can do select, insert, modify, and delete commands on rows of data in a database table.
CloseConnection to close your Connection object automatically when your DataReader closes. (Thanks for the lead in Wolfgang!) The using statement will close all of your objects and dispose of them for you.
Because it also implements IDisposable
.
The purpose of Using statement is that when control will reach end of using it will dispose that object of using block and free up memory. its purpose is not only for auto connection close, basically it will dispose connection object and obviously connection also closed due to it.
Its purpose is to free up the resources that we used inside the Using statement.
According to MSDN:
As a rule, when you use an
IDisposable
object, you should declare and instantiate it in a using statement. The using statement calls theDispose
method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon asDispose
is called. Within the using block, the object is read-only and cannot be modified or reassigned.The using statement ensures that
Dispose
is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then callingDispose
in afinally
block; in fact, this is how the using statement is translated by the compiler. The code example earlier expands to the following code at compile time (note the extra curly braces to create the limited scope for the object):
You can instantiate the resource object and then pass the variable to the using statement, but this is not a best practice. In this case, the object remains in scope after control leaves the using block even though it will probably no longer have access to its unmanaged resources. In other words, it will no longer be fully initialized. If you try to use the object outside the using block, you risk causing an exception to be thrown. For this reason, it is generally better to instantiate the object in the using statement and limit its scope to the using block.
SqlCommand
does implement IDisposable
which a using
statement will call .Dispose()
on before the using block is finished. I'm not sure what SqlCommand.Dispose()
does, but it's a good idea to call .Dispose()
on an instance you are finished with i.e. it will clear up the database connection perhaps.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With