Okay, it's 3 AM, and this was just too intersting and useful to put down.

Frequently, you run into a situation where your application is leaking open connections. Wouldn't it be nice, if somehow you could keep a tab on the # of open connections your application has maintained?

No, I don't mean SqlConnections, I mean the actual physical database connections open at a given time.

Well, get ready for some super-bad-ass-code !!

First, I wrote a simple SQL query to sniff how many connections I really have open.

SELECT SPID,

          STATUS,

          PROGRAM_NAME,

       LOGINAME=RTRIM(LOGINAME),

          HOSTNAME,

          CMD

FROM  MASTER.DBO.SYSPROCESSES

WHERE DB_NAME(DBID) = 'TEST' AND DBID != 0 

Okay good.  Next I created my test application which looks like this -

Great. Now the idea being, that the user is going to specify a certain # of connections to open and KEEP open UNTIL the Close connections button is hit.

Here's the code for that -

private List<SqlConnection> connections = new List<SqlConnection>();

private void button1_Click(object sender, EventArgs e)

{

    for (int i = 1; i <= numConns.Value; i++)

    {

        SqlConnection conn =

            new SqlConnection(ConfigurationManager.ConnectionStrings["HomePc"].ConnectionString);

        conn.Open();

        connections.Add(conn);

    }

}

private void button2_Click(object sender, EventArgs e)

{

    foreach (SqlConnection conn in connections)

    {

        conn.Close();

        conn.Dispose();

    }

}

Great!

Now, if you test the App, go ahead and run it - and run the TSQL query I showed ya at the beginning of this post, you should see results similar to this -

Now as the app is running, change the NumericUpDown to a finite countable number like "5" or "10". I'm gonna change it to 5. And click "Open Connections".

Run the TSQL Query again, here is what the output should look like -

Now, click "Close Connections", and without any delay, quickly run the same TSQL query again, your results will look like this -

Now, if you do understand connection pooling - you wouldn't be surprised at all here. Because, even though SqlConnection.ConnectionState is closed - the physical database connect is still open. Heck 5 of 'em! Because when you opened 5 connections initially, you kept opening them without closing them first - so the framework's pooling mechanism had no choice but to open new physical connections for you.
(Note: the 6th connection you see up there is me logged in via Sql Server Mgmt. Studio)

Now the SQL Query gives me a good # of physical db connections open. But,

 - In a production scenario, I may not be able to run this, because I might not have access to the master DB.
 - This only gives me a count of physical DB conns open at the database level.
 - This gives me no insight of how many conns are contained in which pool, and which pool is on which machine?

So ideally, what you need is, an ability to check and see, how many connection pools do you have, and how many physical database connections do you really have open in your connection pool.

Here is the code simplified assuming there is a single conn. pool in my app (which can be easily extended to as many pools as you want):

private void button3_Click(object sender, EventArgs e)

{

    SqlConnection guineaPigConnection =

        new SqlConnection(ConfigurationManager.ConnectionStrings["HomePc"].ConnectionString);

    Type sqlConnType = typeof(SqlConnection);

    FieldInfo _poolGroupFieldInfo =
      sqlConnType.GetField("_poolGroup", BindingFlags.NonPublic | BindingFlags.Instance);

    object dbConnectionPoolGroup =
      _poolGroupFieldInfo.GetValue(guineaPigConnection);

    FieldInfo _poolCollectionFieldInfo =
      dbConnectionPoolGroup.GetType().GetField("_poolCollection",
         BindingFlags.NonPublic | BindingFlags.Instance);

    HybridDictionary poolConnection =
      _poolCollectionFieldInfo.GetValue(dbConnectionPoolGroup) as HybridDictionary;

    foreach (DictionaryEntry poolEntry in poolConnection)

    {

        object foundPool = poolEntry.Value;

        FieldInfo _objectListFieldInfo =

           foundPool.GetType().GetField("_objectList",
              BindingFlags.NonPublic | BindingFlags.Instance);

        object listTDbConnectionInternal =
           _objectListFieldInfo.GetValue(foundPool);

        MethodInfo get_CountMethodInfo =  

            listTDbConnectionInternal.GetType().GetMethod("get_Count");

        object numConnex = get_CountMethodInfo.Invoke(listTDbConnectionInternal, null);

        MessageBox.Show(numConnex.ToString(), "Number of Physical DB Conns open");

    }

}

As expected, this tells me exactly the # of physical DB connections open in my connection pool. So even after I hit "Close Connections", the above correct reports 5 connections open as shown below -

Now that, my dear friends, is some SERIOUS BAD-ASS code!! (and heckuva useful too frankly).

Damn, gotta sleep. G'nite!