SqlConnection and the Pool, is keeping an open connection kung-foo or foo-bar?

Question

I had thought I was clever. But in light of recent discoveries, I'm not so sure any more. During the page life cycle, there could by any number of database interactions. Some back to back, others spread out. So I invented an object that keeps an instance of an SQL connection alive in the HttpContext.Items dictionary. Every db request then uses this connection and when the http request has ended, I properly dispose of the connection. We're looking at a couple hundred milliseconds the connection would be open, and with some heavy http caching, running out of available connections isn't a concern.

The point was to prevent additional round trips due to the establishing of new connections. But when I stumbled upon the knowledge of connection pooling, I think it pretty invalidates the usefulness of preserving the SqlConnection. Or does it?

Is scenario A the same as Scenario B, performance wise? Which would you recommend? Does scenario B provide no performance gains, and even possibly hinders it because of some edge cases where the connection might not disposed of properly? Forgive the pseudo-ness in the examples, I don't want to clutter them with barf.

A

using (var connection = new SqlConnection(connectionString))
{
   using (var command = new SqlCommand("...", connection))
   {
      ... doing database stuff ...
   }
}

... traversing the stack ...

using (var connection = new SqlConnection(connectionString))
{
   using (var command = new SqlCommand("...", connection))
   {
      ... doing database stuff ...
   }
}

B

   var connectionKeeper = new ConnectionKeeper();

   // Add to the context items so it can be used anywhere
   Context.Items.Add("Connection", connectionKeeper);

   ... traversing the stack ...

   using (var command = new SqlCommand("...", connectionKeeper.Connection))
   {
      ... doing database stuff
   }

   ... traversing the stack ...

   using (var command = new SqlCommand("...", connectionKeeper.Connection))
   {
      ... doing database stuff
   }

   ... traversing the stack ...

   // The end of the request
   sqlKeeper.Dispose();

Answer 1

Use your code from section A. Please let the connection pool do it's job. Avoid keeping a static SqlConnection around at all costs. The connection pool was designed for this.

Here's an MSDN article for your reference.

SQL Server Connection Pooling (ADO.NET)

Answer 2

No point in doing that in code unless you turn connection pooling off.

And you should have a real serious think before doing that, that's extreme circumstances.

Connection pooling was invented to address the situation you are trying to address with this "permanent" connection, so it will actually interfere with the built in optimisations and increase the volume, complexity, and fragility of your code.