title: How to force a SqlConnection to physically close, while using connection pooling?
body: <p>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.</p>
  
  <p>However, that doesn't really tell me if it's really closed, or if I still have an active connection to the database. </p>
  
  <p>How can I force a SqlConnection to close at the network level, or at least tell when it closes?</p>
  
  <p><strong>Example:</strong></p>
  
  <pre><code>using(SqlConnection conn = new SqlConnection(DBConnString)) {
  
     conn.Open();
     SqlCommand cmd = conn.CreateCommand();
     ...
     cmd.ExecuteReader(CommandBehavior.CloseConnection);
     ...
  }
  </code></pre>
  
  <ul>
  <li>First run: 300 ms</li>
  <li>Second run: 100 ms</li>
  <li>Third run: 100 ms</li>
  <li>After waiting a long time (30 minutes): 300 ms</li>
  </ul>
  
  <p>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.</p>
  
  <p>How do I force the connection to truly close?</p>
  
  <p><strong>Ideas</strong></p>
  
  <ul>
  <li>Does CommandBehavior.CloseConnection work? (apparently not?)</li>
  <li>Does setting "Max Pool Size = 0" in the connection string work?  (this would be a pyrrhic solution)</li>
  <li>Does Dispose() work?</li>
  </ul>
  
  <p><strong>References</strong></p>
  
  <ul>
  <li>Article on <a href="http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx" rel="noreferrer">Connection Pooling</a></li>
  <li>Here's another one that tells us that <a href="http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx" rel="noreferrer">Close() doesn't really close</a> the connection.</li>
  <li>An article on <a href="http://betav.com/blog/billva/2007/05/managing_and_monitoring_net_co.html" rel="noreferrer">pros and cons connection pooling</a></li>
  </ul>