C# – How to force a SqlConnection to physically close, while using connection pooling

ado.netcconnection-poolingdatabase connectionsql

I understand that if I instantiate a SqlConnection object, I am really grabbing a connection from a connection pool. When I call Open(), it will open the connection. If I call the Close() or Dispose() method on that SqlConnection object, it is returned to the connection pool.

However, that doesn't really tell me if it's really closed, or if I still have an active connection to the database.

How can I force a SqlConnection to close at the network level, or at least tell when it closes?

Example:

using(SqlConnection conn = new SqlConnection(DBConnString)) {

   conn.Open();
   SqlCommand cmd = conn.CreateCommand();
   ...
   cmd.ExecuteReader(CommandBehavior.CloseConnection);
   ...
}
  • First run: 300 ms
  • Second run: 100 ms
  • Third run: 100 ms
  • After waiting a long time (30 minutes): 300 ms

If the connection was TRULY closing, the second and third runs should also be 300 ms. But I know that the connection is not truly closed for those runs (I checked the SQL Server's activity monitor). It doesn't take the extra 200ms to perform authentication/etc.

How do I force the connection to truly close?

Ideas

  • Does CommandBehavior.CloseConnection work? (apparently not?)
  • Does setting "Max Pool Size = 0" in the connection string work? (this would be a pyrrhic solution)
  • Does Dispose() work?

References

Best Answer

Related Topic