title: .NET SqlConnection class, connection pooling and reconnection logic
body: <p>We have some client code which is using the SqlConnection class in .NET to talk to a SQLServer database. It is intermittently failing with this error:</p>
  
  <p>"ExecuteReader requires an open and available Connection. The connection's current state is Closed"</p>
  
  <p>The "temporary" solution is to reboot the process, after which everything works - however, that's obviously unsatisfactory.</p>
  
  <p>The code is keeping a cache of SqlConnection instances, one for each database.</p>
  
  <p>We'd like to re-write the code, but before I do, I need to know a few things:</p>
  
  <p>My first question is: Is it inefficient to repeatedly connect and disconnect SqlConnection objects, or does the underlying library perform connection pooling on our behalf?</p>
  
  <pre><code>// Is this bad/inefficient?
  for(many-times)
  {
      using(SQLConnection conn = new SQLConnection(connectionString))
      {
          // do stuff with conn
      }
  }
  </code></pre>
  
  <p>Because our code does <em>not</em> do the above, what seems the likely cause of the problem is that something happens to the underlying SQLServer database during the "lifetime" of the connection that causes the connection to be closed...</p>
  
  <p>If it turns out that it is worthwile to "cache" SqlConnection objects, what is the recommended way to handle all errors that could be resolved simply by "reconnecting" to the database. I'm talking about scenarios such as:</p>
  
  <ul>
  <li>The database is taken offline and brought back online, but the client process had no open transactions while this was happening</li>
  <li>The database was "disconnected", then "reconnected"</li>
  </ul>
  
  <p>I notice that there is a "State" property on SqlConnection... is there an appropriate way to query that?</p>
  
  <p>Finally, I have a test SQLServer instance set up with full access rights: how can I go about reproducing the exact error "ExecuteReader requires an open and available Connection. The connection's current state is Closed"</p>