How to force a SqlConnection to physically close, while using connection pooling?
Maybe SqlConnection.ClearPool
?
Moe Sisko's answer (Call SqlConnection.ClearPool
) is correct.
Sometimes you need a connection to really close rather than return to the pool. As an example, I have a unit test that creates a scratch database, builds the schema, tests some stuff, then drops the scratch database if the tests all pass.
When connection pooling is active, the drop database command fails because there are still active connections. From the point of view of programmer all SQLConnections are closed, but as the pool still holds one open, SQL Server won't allow the drop.
The best documentation for how connection pooling is handled is this page on SQL Server Connection Pooling on MSDN. One doesn't want to turn connection pooling off entirely because it improves performance with repeated opens and closes, but sometimes you need to call a "force close" on an SQLConnection so that it will let go of the database.
This is done with ClearPool. If you call SqlConnection.ClearPool(connection)
before closing/disposing, when you do close/dispose it will really go away.