Use of connection pooling, what is the right way?

Question

seen question about that today but I not sure whether I understand it well. Also using the same connection string causes that they will "share" connections (if free), right? So is it OK to have class like that, pass it as a reference to each client in multithread app and just call its methods. Anyway, would not there be a short lag in the case the first one gets connected?

 public int(string commandText)
    {
      SqlConnection con=new SqlConnection(SOME CONSTRING with pooling ON)
    ...

    }

    public string(string commandText)
    {
      SqlConnection con=new SqlConnection(the same CONSTRING with pooling ON)
    ...
    }

Answer 1

You should always dispose of connections when you no longer need them. Disposing them doesn't close the underlying connection in the connection pool, rather it releases it back to the pool. Otherwise the connection will remain unavailable until the Garbage Collector gets around to clean the orphaned SqlConnection objects.

In order to utilize the connection pool to the max you should always include your connections in a using statement:

using (SqlConnection con = new SqlConnection(someConnectionString)
{
...
}

There is no penalty from frequently disposing the SqlConnection objects as the underlying connection remains open. Next time you create a SqlConnection it will pick one of the connections available in the pool.

Passing the connection around in a multithreaded app is a bad idea, as multiple threads will try to use it at the same time with unpredictable results. For one thing, SqlConnection instance methods are NOT thread-safe. Worse, transactions are specific to connections and you can end up with one thread messing another's transaction. Opening a new connection in each thread is much easier and safe.

As far as the overhead is concerned, you will always have an overhead when opening a new connection, even with a connection pool. A pool saves you the overhead of creating a new connection when there an unused open connection is already available. If you make sure you release your connections as soon as possible, you will not have to create too many connections.

You may be surprised how few connections your application really needs once you start using a connection pool.

Answer 2

The SQL client, whether; ODBC, JDBC, ADO.Net etc. will control the pooling based on the connection string used to initiate a connection. The connection strings have a number of parameters that control how those connections are managed including pooling. Although .Net specific the concepts discussed here apply across databases and programming languages: Connection Pooling for the .NET Framework Data Provider for SQL Server