Does .net SqlCommand.ExecuteReader close connection?

Question

In this sentence:

myCommand.ExecuteReader(CommandBehavior.CloseConnection)

does it close connection in case of exception?

Answer 1

There are plenty of ways that the command can go wrong.

Ultimately it is the Close method of the data reader that will close the connection, provided nothing has gone wrong before.

If there is an exception that occurs inside ExecuteReader or any of its called methods, before the actual DataReader object is constructed, then no, the connection will not be closed.

In case of an exception, I wouldn't trust it to close the connection.

Answer 2

It depends where the exception occurs!

If you structure your try catch correctly, it will be fine.

For example:

SqlCommand myCommand = new SqlCommand();

try
{
   myCommand.dostuff();
}
catch(Exception ex)
{
  // display error message
}
finally
{
  myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}

If the line: myCommand.ExecuteReader(CommandBehavior.CloseConnection) fails (perhaps the database has gone down?) then the connection cannot be closed programmatically.

Answer 3

The safest way to do a "normal" query is

using (var conn = new SqlConnection("..."))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "...";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                // ...
            }
        }
    }
}

Exceptions can be caught outside this code.