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



Question:

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


Solution:1

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.


Solution:2

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.


Solution:3

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();  }  


Solution:4

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »