Is closing / removing SqlDataReader necessary if you are already closing SqlConnection?

Question

I noticed this question , but my question is a bit more specific.

Are there any benefits when using

using (SqlConnection conn = new SqlConnection(conStr)) { using (SqlCommand command = new SqlCommand()) { // dostuff } } 

instead

 using (SqlConnection conn = new SqlConnection(conStr)) { SqlCommand command = new SqlCommand(); // dostuff } 

Obviously, it matters if you plan to run more than one command with the same connection, since closing SqlDataReader more efficient than closing and reopening the connection (calling conn.Close();conn.Open(); also free the connection).

I see that many people insist that refusing to close SqlDataReader means that open connection resources are around, but is this not applicable only if you do not close the connection?

Answer 1

In my opinion, there are two rules:

• Classes that implement IDisposable must be wrapped in a using block.
• You should not rely on the implementation of the IDisposable class to ignore rule 1.

That is, even if you know that deleting the connection object took care of disposing of the command object associated with it, you should not rely on this behavior.

By the way, you can use the nest blocks cleaner:

 using (SqlConnection conn = new SqlConnection(conStr)) using (SqlCommand command = new SqlCommand()) { // dostuff } 

and i would use

 SqlCommand command = conn.CreateCommand(); 

instead of creating a new SqlCommand, and then link it to the connection.

Answer 2

Technically, this is not necessary; closing SqlConnection should destroy any resources that SqlDataReader uses. The converse is also true; you do not need Dispose SqlConnection if you have the SqlDataReader that was created using CommandBehavior.CloseConnection .

Practically speaking, however, when a class implements IDisposable , you should Dispose it when you are done with it. Implementation details of infrastructure classes can be changed at any time, and if the documentation does not specifically indicate circumstances in which there is no need for a Dispose instance, there is always a chance that some future changes / updates will lead to a resource leak in your code.

Actually no extra effort - just wrap it in a using block.

Answer 3

In most cases, this may not be necessary, but it is best for some reason. There is no reason for resources to be preserved until they are no longer useful. Using using helps to ensure this.

Answer 4

Isn’t it a matter of freeing a resource and collecting garbage to free it later?

Answer 5

There is a difference. If you create 2 of these readers without opening / closing the connection, but do not use the first before using the second, you will get a conflict saying that the connection is already connected to the open reader.