What is the use/advantage of using CommandBehavior.CloseConnection in ExecuteReader()

Can 开发者_如何学Pythonanyone tell me what is the CommandBehavior.CloseConnection and what is the use/benefit of passing this as a parameter in com.ExecuteReader(CommandBehavior.CloseConnection)?

---

You need an open connection while reading a data reader, and you want to close connections as soon as you can. By specifying CommandBehavior.CloseConnection when calling ExecuteReader, you ensure that your code will close the connection when it closes the data reader.

But you should already be disposing your connections immediately (not just closing them), in which case there's at best a marginal (almost certainly unmeasurable) benefit to doing this.

For example, this code closes its connection immediately (and performs whatever other work is required to dispose it), without specifying the command behavior:

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(commandText, connection))
{
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader()) 
    {
        while (reader.Read())
           // Do something with the rows
    }
}

---

Use CommandBehavior.CloseConnection in functions that create a connection but return a IDataReader. Be very careful to Dispose() of the IDbConnection on exceptions only before the reader is created:

IDataReader ReadAll()
{
    var connection = new SqlConnection(connectionString);
    try
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM mytable";
        var result = command.ExecuteReader(CommandBehavior.CloseConnection);
        connection = null; // Don't dispose the connection.
        return result;
    }
    finally
    {
        if (connection != null)
            connection.Dispose();
    }
}

---

If you don't, then when you use a connection over and over again in a loop, the connection will remain "open" until the Garbage collector picks it up, and only then will it be released back to the ADO.net Connection pool to be re-used. That means that each time through the loop, the code that "Opens" a connection will not be able to re-use the same one again (It hasn't been released back to the pool).
As a result, for each successive loop iteration, ADO will need to create another connection from scratch, and eventually, you may run out of available connections. Depending on how long it takes for the GC to get around to closing it, you might have gone through a large number of loop iterations, creating a new unecessary connection for each one, while all these unclosed and unused connections are just sitting there. If you use CommandBehavior.CloseConnection, then in each loop, you will release the connection back to the pool, and the next iteration can re-use it. As a result your process will run faster and can get away with many fewer connections.

---

Re : What is the advantage of CommandBehavior.CloseConnection?

Long lived data readers can be useful when you do not necessarily want to retrieve and materialize all data that the query would otherwise return, all-at-once. Although it is possible for your app to directly retain the reference to a long-lived Connection, this can lead to messy architecture, where Data Tier dependencies such as ISqlConnection 'bleeds' up into the business and presentation concerns of your application.

Lazy data retrieval (i.e. retrieving data only when it is needed) has the benefit that the caller can keep asking for more data until it has satisfied its data requirement - this is common in scenarios requiring data paging or take-while lazy evaluation, until some satisfying condition is met.

Long-lived connection / lazy data retrieval practices were arguably more commonplace in legacy architectures such as Fat-Client, where a user would scroll through data whilst keeping a connection open, however, there are still uses in modern code.

There is something of a trade off here: Although there are memory, and network resource overheads for both the App / Client side needed for the duration of the Reader (and Connection), and for keeping 'state' in the RDBMS database side (buffers, or even Cursors, if a PROC using Cursors is executed), there are also benefits:

• Network IO between consuming app and Database is reduced, as only data which is required is retrieved
• App memory overhead is reduced, because data which isn't going to be needed isn't retrieved (and if object abstractions are used on top of the DataReader, this will also potentially save unnecessary deserialization / materialization into Entity / DTO / POCOs)
• By not materializing all data in the query 'all at once', it allows the app to release memory (e.g. DTO's) no longer needed as it moves through the reader

Preventing IDataReader bleeding into your app

Nowadays, most apps wrap data access concerns into a Repository pattern or by using an ORM to abstract data access - this usually results in data retrieval returning Entity object(s) rather than working natively with low level IDataReader API's throughout your app.

Fortunately, it is still possible to use a lazy generator (i.e. method returning IEnumerable<Entity> AND still retain control over the DataReader (and thus Connection) lifespan, by using a pattern such as this (this is an async version, but obviously sync code would also work, albeit be more thread hungry)

public Task<IEnumerable<Foo>> LazyQueryAllFoos()
{
   var sqlConn = new SqlConnection(_connectionString);
   using (var cmd = new SqlCommand(
        "SELECT Id, Col2, ... FROM LargeFoos", sqlConn))
   {
      await mySqlConn.OpenAsync();
      var reader = cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
      // Return the IEnumerable, without actually materializing Foos yet
      // Reader and Connection remain open until caller is done with the enumerable
      return LazyGenerateFoos(reader);
   }
}

// Helper method to manage lifespan of foos
private static IEnumerable<Foo> GenerateFoos(IDataReader reader)
{
    // Lifespan of the reader is scoped to the IEnumerable
    using(reader)
    {
       while (reader.Read())
       {
          yield return new Foo
          {
              Id = Convert.ToInt32(reader["Id"]),
              ...
          };
       }
    } // Reader is Closed + Disposed here => Connection also Closed.
}

Notes

• Just as with SqlConnections and DataReaders, the code calling LazyQueryAllFoos still needs to be mindful not to hold onto the Enumerable (or an Iterator of it) for longer than needed, as this will keep the underlying Reader and Connection open.
• Jon Skeet analyses yield return generators in depth here - the takeaway is that the finally of using blocks will complete in yield iterator blocks once the enumerable has either run to completion, OR thrown an exception (e.g. network failure), OR even if the caller doesn't complete iterating the iterator and it goes out of scope.
• As at C#6, async code cannot currently also use yield return, hence the need to split the helper method out of the async query (The helper could I believe be moved into a Local Function, however). Things might change in future, e.g. IAsyncEnumerator

---

I suggest reading through the MSDN documentation for the CommandBehaviour Enumeration:

CloseConnection - When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

Compare this against the other enumeration items.

---

I've found the best use of CommandBehavior.CloseConnection is when you want to write code that is flexible enough to be used in a transaction (which implies a shared connection for all queries). Consider:

public DbDataReader GetReader(DbCommand cmd, bool close = true)
{
    if(close)
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    return cmd.ExecuteReader();
}

If you are running a read operation as part of a bigger transaction, you would want to pass false to this method. In either case, you should still use a using statement to do the actual read.

Not in a transaction:

using(var reader = GetReader(cmd, true))
{
    while(reader.Read())
        ...
}

In a transaction, maybe checking for the existence of a record:

bool exists = false;
using(var reader = GetReader(cmd, false))
{
    if(reader.Read())
        exists = reader.GetBoolean(0);
}

The first example will close the reader AND the connection. The second one (transactional) will still close the reader, but not the connection.