Remembering SQL connection state in .net?

Question

Beside's the old known fact that connection.Close() vs connection.Dispose() are the same - except that running Close() over a disposed connection raises an exception while running Close() over a closed connection - is OK - I still have a question :

Assuming connection pooling is on , (default) - why is it important to remember the state of the connection ?

I've read this question here which shows that - avoiding opening and closing a connection saves performance.

This seems logic , but the problem is that the connection is never actually closed ! it is only marked for close.

Even If I use it under a using scope - the dispose just closes the connection and put it back in the pool.

Even if I wanted , I couldn't leave it open ( because I'd want others to use it). so I had to close/dispose it.

Looking at Dapper which also implements this behavior:

public static async Task<IEnumerable<T>> QueryAsync<T>(this...)
        {
         //...
            bool wasClosed = cnn.State == ConnectionState.Closed;
            using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
            {
                try
                {
                    if (wasClosed) await ((DbConnection)cnn).OpenAsync()...
                  //...
                }
                finally
                {
                    if (wasClosed) cnn.Close();
                }
            }
        }

As you can see the "memorization" is implemented here.

nb , I already asked Marc about a related topic which is - why in dapper samples he uses both GetClosedConneciton and GetOpenConnection and I got an answer which is to show - that Dapper can deal with both scenarios. However this current question is about why it is re-set the connections state.

Question :

Looking at Dapper code it seems that it remembers the state and re-set the state after operation. ( I also know this behavior from the old sqldataadapter class)

The question is - why ? If I got a closed connection - then, I need to open it. great. but why do I have to close it by condition ? why not ALWAYS close it ? it's not going to hurt performance since the connection is not actually closed - it is only returned to pool.

The other way around - If I got an open connection , then I'd do work and keep it open (huh??)

AS you probably see , I'm missing something here. can someone please shed light ?

score 6 · Answer 1 · answered May 30 '14 at 07:09

If you're writing a library function where it makes sense for the consumers to pass you a connection object (of whatever flavour) then the safest thing to do is to respect that connection:

if you're passed an open connection object, make no assumptions about it and certainly don't close it after you've completed your work - you don't know what your consumers have done with it or will do with it.
if you're passed a closed connection object, then you'd better open it before you attempt to use it, and you ought to close it after you're done - you know that your consumer can't have e.g. a transaction open against a closed connection.

If your code creates a connection object, then I'd always recommend that the creation be placed in a using statement and so it will always be closed when you're done.

So, the only remaining thing I can think to say is to think carefully about writing your functions and work out whether it makes sense for your consumers to be passing you connection objects - if your work should always be performed in isolation, it would make more sense to ask for e.g. a connection string and for your code to entirely control the connection.

score 6 · Accepted Answer · answered May 30 '14 at 08:21

why not ALWAYS close it ?

The user could be doing lots of work on that connection. It could be associated with a transaction (closing would orphan it). It could have temporary tables (closing would destroy them), or other connection-preserved state (SET options, impersonation, etc).

Closing a connection here (if it was open originally) would be an unusual and unexpected thing with multiple nasty side-effects.

Remembering SQL connection state in .net?

2 Answers2