Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SqlCommand with using statement

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?

like image 655
Omer Avatar asked Apr 20 '14 18:04

Omer


People also ask

What is the use of SqlCommand?

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.

Why do we use SqlCommand in C#?

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.

Does using automatically close SqlConnection?

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.


2 Answers

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 the Dispose 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 as Dispose 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 calling Dispose in a finally 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):

NOTE:

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.

like image 152
Ehsan Sajjad Avatar answered Sep 22 '22 17:09

Ehsan Sajjad


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.

like image 33
Jason Evans Avatar answered Sep 25 '22 17:09

Jason Evans