[ACCEPTED]-How to force a SqlConnection to physically close, while using connection pooling?-connection-pooling

Accepted answer
Score: 56
Score: 26

Moe Sisko's answer (Call SqlConnection.ClearPool) is correct.

Sometimes 21 you need a connection to really close rather 20 than return to the pool. As an example, I 19 have a unit test that creates a scratch 18 database, builds the schema, tests some 17 stuff, then drops the scratch database if 16 the tests all pass.

When connection pooling 15 is active, the drop database command fails 14 because there are still active connections. From 13 the point of view of programmer all SQLConnections 12 are closed, but as the pool still holds 11 one open, SQL Server won't allow the drop.

The 10 best documentation for how connection pooling 9 is handled is this page on SQL Server Connection Pooling on MSDN. One doesn't want 8 to turn connection pooling off entirely 7 because it improves performance with repeated 6 opens and closes, but sometimes you need 5 to call a "force close" on an 4 SQLConnection so that it will let go of 3 the database.

This is done with ClearPool. If 2 you call SqlConnection.ClearPool(connection) before closing/disposing, when 1 you do close/dispose it will really go away.

Score: 14

If you don't want to use the connection 4 pool you have to specify it in your SqlConnection.ConnectionString property. For 3 example

"Data Source=MSSQL1;Database=AdventureWorks;Integrated Security=true;Pooling=false;"

Disposing or closing the SqlConnection object 2 is just going to close the connection and 1 return it to the connection pool.

Score: 3

Generally, you want the connection pool 3 to do its job - you don't want the connection 2 to truly close.

Why specifically do you want 1 the connection not to return to the pool?

Score: 0

I see that you are using .net but as this 4 showed up in a google query allow me to 3 give a java response...

Use a DataSource 2 that implements Closeable() and call close 1 on the DataSource. Hikari supports Closeable.

More Related questions