The Mailowl Scrolls

I’ve been trying to post this for a while but I’ve never liked how I’ve written it. I don’t want to come across sarcastic or superior but I do want bring across the importance of changing from poor coding. Now after almost a month I’m just going to say it.

Do not persist your SQLConnection objects! The SQLConnection object is really just a wrapper to a connection pooler (which you can think of as existing in the same memory space as the .NET garbage collector) and the connection pooler is already persisted so your SQLConnection object can go in and out of scope without experiencing a performance hit.

When you instantiate a new SQLConnection, .NET tries to match your connection string with a pool already in existence before creating a new pool. Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity.

I’m going to quote from an article I urge you to read if you, at all, use SQL; “Connection pooling reduces the number of times that new connections need to be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks to see if there is an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of actually closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.” – Using Connection Pooling

My last comment is that because the connection is actually owned by the pool when your SQLConnection object goes out of scope the connection is not closed. You must specifically call Close() or Dispose() to tell the pool you’re finished, even though it’ll decide how to handle the connection.

The best way to avoid leaving connections open is to use the Using block. The Using block guarantees that the objects it controls are disposed of correctly (this description of the Using statement is for Visual Basic but it’s the same for C# just more descriptive).  It’s even recommended in the MSDN help on the SQLConnection.

I think the problem is that this isn’t explained well in tutorials, especially for developers coming from VB6. While the object names are similar to the old ADO the underlying architecture has changed dramatically in ADO.NET and its worthwhile looking up how you’re supposed to use it besides just how to code with it.