title: Remembering SQL connection state in .net?
body: <p><em>Beside's  the old known fact that <code>connection.Close()</code> vs <code>connection.Dispose()</code> are the same  - except that running <code>Close()</code> over a disposed connection raises an exception while running <code>Close()</code> over a closed connection - is OK - I still have a question :</em></p>
  <p>Assuming connection pooling is on , (default)  - <em>why</em> is it important to remember  the state of the connection  ?</p>
  <p>I've read <a href="https://stackoverflow.com/questions/4439409/open-close-sqlconnection-or-keep-open"><em>this question here</em></a> which shows that - <em>avoiding opening and closing a connection saves performance</em>.</p>
  <p>This  seems logic , but the problem is that the connection is <em>never</em> actually closed ! it is only <em>marked</em> for close.</p>
  <p>Even If I use it under a <code>using</code> scope  - the dispose just <em>closes</em> the connection and put it back in the pool.</p>
  <p>Even if I wanted , I couldn't leave it open <em>( because I'd want others to use it)</em>. so I had to close/dispose it.</p>
  <p><a href="https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper%20NET45/SqlMapperAsync.cs#L27" rel="nofollow noreferrer">Looking at Dapper</a>  which also implements this behavior:</p>
  <pre><code>public static async Task&lt;IEnumerable&lt;T&gt;&gt; QueryAsync&lt;T&gt;(this...)
          {
           //...
              bool wasClosed = cnn.State == ConnectionState.Closed;
              using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
              {
                  try
                  {
                      if (wasClosed) await ((DbConnection)cnn).OpenAsync()...
                    //...
                  }
                  finally
                  {
                      if (wasClosed) cnn.Close();
                  }
              }
          }
  </code></pre>
  <p>As you can see the &quot;memorization&quot; is implemented  here.</p>
  <p><em>nb , I already asked Marc about a related topic which is  - why in dapper samples he uses both <code>GetClosedConneciton</code> and <code>GetOpenConnection</code> and I got an answer which is to show - that Dapper can deal with both scenarios. However this current question is about why it is <strong>re-set</strong>  the connections state.</em></p>
  <p><strong>Question :</strong></p>
  <p>Looking at Dapper code it seems that it remembers the state and re-set the state after operation. ( I also know this behavior from the old sqldataadapter class)</p>
  <p>The question is  - why ?  If I got a closed  connection  - then, I need to open it. great. but why do I have to close it by condition ? why not ALWAYS close it ? it's not going to hurt performance since the connection is not actually closed -  it is only returned to pool.</p>
  <p>The other way around  - If I got an open connection ,  then I'd do work and keep it <em>open</em> (huh??)</p>
  <p>AS you probably see , I'm missing something here. can someone please shed  light ?</p>