Let’s all jump into the pool..The SQL Connection pool that is..

I was out swimming in the data pool and I started thinking to myself, how many people are sharing the same pool without me even knowing they are there? How did I even get into the pool? And why is the water so cold?

Well, if you are using ADO.NET with SqlConnection , you are playing in the pool to. ADO.NET is a pretty smart animal. It realizes that if you are going to be connecting to a database a lot, then its probably a good idea to keep the connection to the database server open awhile. It does this behind the scenes, so when you create multiple data connections, it only really grabs one from the available pool of connections and uses those.

So….with that in mind, I started thinking about how I can tell what data connections an application has used and who is swimming in my pool. Coupling that with the brainstorming involved in .NET Illuminator, I came up with a very nice and simple piece of code that grabs all the connection strings from the Pool and throws them in rafts for you to see.

Why might this be handy for .NET Illuminator, well…Its always interesting to see what data connections an app has made and what the connection string is for them 🙂

Here is my simplistic example of using C# with .NET to walk the SQL pooled connections and return their connection strings.

         /// <summary>
        /// Loads all pooled connection strings.
        /// </summary>
        /// <returns></returns>
        public static List<string> LoadAllPooledConnectionStrings()
        {
            try
            {
                SqlConnection connection = new SqlConnection();

                FieldInfo connectionFactory = connection.GetType().GetField("_connectionFactory", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Static);

                object cFactory = connectionFactory.GetValue(connection);
                FieldInfo connectionPoolDictionary = connectionFactory.FieldType.GetField("_connectionPoolGroups", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Instance);

                object dictPools = connectionPoolDictionary.GetValue(cFactory);

                Type poolDictionaryType = dictPools.GetType();
                PropertyInfo pooledKeys = poolDictionaryType.GetProperty("Keys", BindingFlags.Public | BindingFlags.Instance);

                object keys = pooledKeys.GetValue(dictPools, null);

                List<string> allKeys = new List<string>();
                foreach (object key in (IEnumerable)keys)
                    allKeys.Add(key.ToString());
                return allKeys;
            }catch(Exception err)
            {
                throw err;
            }
        }

So…What did we learn? Just because you don’t see the pool….smell the chlorine…or even get wet, you open a data connection and you have jumped all the way in.. and left your stench behind in the water..

Happy swimming!

Leave a Reply

Your email address will not be published. Required fields are marked *