Never-ending story of Close versus Dispose

Erno de Weerd recently posted a blog entry on how SqlConnection.Dispose removes the connection from the connection pool. He concluded this after looking in Reflector and seeing this:

    1 protected override void Dispose(bool disposing)

    2 {

    3   if (disposing)

    4   {

    5     this._userConnectionOptions = null;

    6     this._poolGroup = null;

    7     this.Close();

    8   }

    9   this.DisposeMe(disposing);

   10   base.Dispose(disposing);

   11 }

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:

using (SqlConnection con = new SqlConnection(connectionString1))

{

  // Do work on connection

  // using block will expand in finally that will call Dispose

}

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.

    1 /// <summary>

    2 /// <para>Releases the unmanaged resources used by the <see cref=”T:System.Data.SqlClient.SqlConnection” /> and optionally releases the managed

    3 /// resources.</para>

    4 /// </summary>

    5 /// <param name=”disposing”>

    6 /// <see langword=”true” /> to release both managed and unmanaged resources; <see langword=”false” /> to release only unmanaged resources. </param>

    7 protected override void Dispose(bool disposing)

    8 {

    9   if (disposing)

   10   {

   11     switch (this._objectState)

   12     {

   13       case ConnectionState.Open:

   14         this.Close();

   15         break;

   16     }

   17     this._constr = null;

   18   }

   19   base.Dispose(disposing);

   20 }

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.

public const string connectionString1 =

  @”DATA SOURCE=(local);INITIAL CATALOG=AdventureWorks;INTEGRATED SECURITY=SSPI;Max Pool Size=2″;

public const string connectionString2 =

  @”DATA SOURCE=(local);INTEGRATED SECURITY=SSPI;INITIAL CATALOG=AdventureWorks;Max Pool Size=2″

 

static void Main(string[] args)

{

  SqlConnection connection1, connection2, connection3, connection4, connection5;

  connection1 = new SqlConnection(connectionString1);

  connection2 = new SqlConnection(connectionString1);

  connection3 = new SqlConnection(connectionString1);

  connection4 = new SqlConnection(connectionString2);

  connection5 = new SqlConnection(connectionString2);

 

  connection1.Open();

  connection2.Open();

  //connection3.Open();

 

  connection4.Open();

  connection5.Open();

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:

More on this exception at Angel Saenz-Badillos blog. This means that connections 4 and 5 are created in a different pool.

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.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s