C# How to "verify" that new connections are actually getting reused from connection pool

Question

I have a simple c# code in which I am trying to open and close connections multiple times. How can I ensure that my new connections are coming from connection pool and that it is not hitting the database?

using System;
using System.Data;
using System.Data.Odbc;
using System.Collections.Generic;

namespace LBSService
{
    class MyClass    {
        public static OdbcConnection connection = null;       
        public void TestConnection()
        {
            string connectionstring = @"Dsn=my_database.IServer;Host=IServer;
                                        Database=my_database;Uid=informix;
                                        Pwd=Some@123";

            for (int i = 1; i <= 50; i++)
            {
                string StrQuery = "select * from capture_files";
                connection = new OdbcConnection(connectionstring);
                connection.Open();
                connection.Close();
            }
        }
    }
}

I have limitation that I have to open an ODBC connection so answer related to ODBC is preferred.

Is there any data memeber within my 'connection' object or something where I can actually see how many unused connections are there in pool and how many are used by my application.

Thanks in advance...

Answer 1

You may use timings in your function with adding and removing to the connection string this:

Pooling=false;

If timings vary - then without this string connections are reused

Like this:

public long TestConnection(bool usepooling)
{
  string connectionstring = @"Dsn=my_database.IServer;Host=IServer;
                                    Database=my_database;Uid=informix;
                                    Pwd=Some@123;Pooling="+usepooling.ToString;


  Stopwatch sw = new Stopwatch();
  for (int i = 1; i <= 50; i++)
  {
    string StrQuery = "select * from capture_files";
    connection = new OdbcConnection(connectionstring);
    sw.Start();
    connection.Open();
    connection.Close();
    sw.Stop();
  }

  return sw.ElapsedMilliseconds;

}

Answer 2

"How can I ensure that my new connections are coming from connection pool and that it is not hitting the database"

That is slightly erroneous logic. The connection pool is on the client side. Even if you reuse a connection from the pool, any command will still have to hit the database.

If you have exactly the same connection string (down to case even), then you will reuse a connection from the pool (provided it is turned on, which is the default).

The reason the connection pool exists is because setting up a connection requires some overhead.

SQL Server Connection Pooling:

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

Connection pooling reduces the number of times that new connections must 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 for 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 closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration. Connections are separated into pools by connection string, and by Windows identity when integrated security is used. Connections are also pooled based on whether they are enlisted in a transaction.

Answer 3

You can look at the ADO.net Performancecounters to ensure that there is only once active Database Connection