Note line 6 where the poolGroup seems to be reset.
I do admit I was thrown off by his post as I first came to the same conclusion. Diving a bit deeper into the DbConnectionPoolGroup didn’t make me a wiser man. My entire belief of order in this galaxy crumbled (yes, it takes that little). This would mean that everyone who uses:
would not have been using connection pooling. :O
For years I had been claiming that Dispose is nothing more than Close plus the wiping of the connection string. This is certainly true for .NET 1.1. In the .NET Framework 1.1 the implementation was quite a bit different, but more explicit with the assumption of the small difference between Dispose and Close.
No removing from the pool group in .NET FX 1.1. So, what is this thing with the pool group in ADO.NET 2.0? Does 2.0 remove the connection from the pool on Dispose?
After discussing a bit with colleague Astrid Hackenberg (still no weblog) we figured it was all very logical. You see, connections are pooled by default (unless you specify Pooling=False in your connection string). At the client connection pools are created to pool connections that have the same characteristics. Connections can be pooled in the same pool provided that:
- The entire connection string is exactly the same.
Case-sensitive and the order of the connection string parameters is significant. To put it simply: your connection strings must match EXACTLY (meaning each character).
- The security context is the same. Especially important if you are using integrated security with SQL Server.
You can test the above by running this simple bit of code in a debug session.
You should notice that this all runs fine.
The connection strings 1 and 2 are semantically the same, but differ syntactically. The pool size is set to a maximum of 2. This means that we can at least see things happen when we create connections in the same or in another pool. To prove this, uncomment the line with connection3.Open() and run it again. You will get this exception:
OK, did you also conclude that the connection string is one factor in the determination of the pool that the SqlConnection object belongs to? Great.
The SqlConnection instance is a managed object that internally references a “physical” unmanaged connection. When you Dispose or Close your SqlConnection object you return the physical connection to the pool and release the reference to the physical connection. If the SqlConnection is reopened it will get a new reference to a physical connection. This might be a new or even the same connection from the pool, … provided we did not change the connection string of the SqlConnection object.
So, wouldn’t it be a good practice for Dispose to also reset the pool (group) when you reset the connection string (for .NET 2.0 this is encapsulated in a DbConnectionOptions object)? It just might happen that you assign a different connection string and open the same SqlConnection, which results in a different physical connection and connection pool. Resetting the reference to the pool group from the SqlConnection object has nothing to do with the physical connection being removed from the pool. Tempting to think so when looking at Reflector.
Yep, makes sense to me. Dispose == Close + connection string = null. Galaxy order is restored. Pfffeeeeww.