Sql Connection Elimination

Question

Just wondering if SqlConnection will be dipodized / closed when this method is executed? Or do I need to explicitly call the close method at the end?

using (SqlCommand cmd = new SqlCommand(sql, GetConnection())) { SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { } } SqlConnection GetConnetion() { return new SqlConnection("connectionstring"); } 

I know I can do something like this:

 SqlConnection conn = GetConnetion(); SqlCommand cmd =new SqlCommand(sql, conn); //Do Something conn.Close() cmd.Dispose() 

But just curious how the used block will work in this case. Greetings

Answer 1

No, the connection object will not be automatically deleted in your example. The using block applies only to the SqlCommand object, and not to the connection.

To make sure the connection is established, make sure that the SqlConnection object is wrapped in its own using block:

 using (SqlConnection conn = GetConnection()) using (SqlCommand cmd = new SqlCommand(sql, conn)) { // don't forget to actually open the connection before using it conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { // do something } } } 

Answer 2

Luke's answer is correct in relation to what you specifically requested regarding the removal of the compound.

For completeness, you can also use the SqlCommand.ExecuteReader (CommandBehaviour) method instead of aimless, passing CommandBehvaiour.CloseConnection :

 using (SqlCommand cmd = new SqlCommand(sql, GetConnection())) { using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) {} } } 

This means that when the SqlDataReader closes (when it is deleted in the construct used), it, in turn, closes the connection that it uses.

I am not interested in this approach, because there is some implied logic, and it is not obvious what exactly closes the connection.

Answer 3

The usage statement will take care of this for you.

Answer 4

Unfortunately. You want to use use in your connection, not in your team.

Answer 5

Use using , but in a connection, not in SqlCommand. The Dispose method on the connection will close the connection (return it to the pool if the pool is enabled). Also put also around SqlDataReader:

 using(SqlConnection conn = GetConnection()) { SqlCommand cmd = new SqlCommand(sql, conn); using (SqlDataReader reader = cmd.ExecuteReader()) { do { while (reader.Read()) { } } while (reader.NextResult()); } } 

Answer 6

Here and Here is something that could help you understand what is happening.