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