Jeff Meatball Yang Jeff Meatball Yang - 1 month ago 9
SQL Question

How to force a SqlConnection to physically close, while using connection pooling?

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


Answer