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 ?
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.