What happens if you close a SqlConnection before the SqlDataReader?

Question

What would happen if you call Close() on a SqlConnection object before you call Close() on a SqlDataReader using that connection?

Actually, what I really want to know is whether or not the order in which you Close them matters. Does calling SqlConnection.Close() completely close the connection, or will it remain open if you do not call Close() on a SqlDataReader using that connection?

Sorry for the multiple questions, but I don't think I really understand how connection closing works.

Answer 1

It'll close the connection (returns it to the pool) and SqlDataReader would throw an exception (System.InvalidOperationException) in case it's used afterward.

Answer 2

Rather than worrying about the order on how to close them, why not wrap them in using statements.

// the following code has parts left out for brevity...
using(var conn = new SqlConnection( ... ))
using(var cmd = new SqlCommand( ... ))
{
    conn.Open();

    using(var reader = cmd.ExecuteReader())
    {
        // do whatever with the reader here...
    }
}

the using statements ensure that your objects are closed, and in the correct order if you nest them accordingly. See http://msdn.microsoft.com/en-us/library/yh598w02.aspx for more info.

Answer 3

The actual database connection will be closed (returned to the pool) when you close/dispose the SqlConnection object, so the database resources are safe.

However, you should close/dispose the SqlDataReader also, otherwise it will keep a reference to the SqlConnection object, keeping them both in memory. Eventually the finalizer will dispose the SqlDataReader object if you don't do it, and then the SqlConnection object can also be collected, but you have no control over when that happens.

The SqlDataReader can be partially usable after you have closed the connection, but nothing that you really can rely on. It still has some data in it's buffer, so some operations may work, but anything that requires more data from the database will cause an exception.