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

Question

Can anyone tell me what is the CommandBehavior.CloseConnection and what is the use/benefit of passing this as a parameter in com.ExecuteReader(CommandBehavior.CloseConnection)?

Answer 1

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
    }
}

Answer 2

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