How does Connection pool work in Ado.net?

Question

I am connecting to a legacy rdbms system using System.Data.OdbcClient. I would like to know if the connection pool is supported by the legacy rdbms system? Suppose I use Connection as shown below

using (OdbcConnection con = new OdbcConnection("ConnStr"))
{
    con.Open();

    //perform db operation

}

Does it establishes a connection and disconnects each time it is called or it comes from connection pool.

Answer 1

It uses a connection pool, you can (ans should) have it in a using clause that will "soft close" the connection but next time you are likely to get an already established connection.

Answer 2

It all depends on the way connection pooling is configured on this machine. If connection pooling is effectively enabled on this computer, the snippet shown will use a connection from the pool (if available), and effectively not require the creation of a new [RDBMS] connection.

That's the nice thing about connection pooling: it is transparent to the application, i.e. you do not need to do anything different, to call a separate API etc.

There's a distinction to be made: connection pooling typically deals with connections to the DBMS server ("SQL sessions" if you will) not with the object which encapsulate such a connection. Consequently, the SQLsessions (which are, relatively, the most costly elements to produce) are effectively cached, but the ADO (or whatever objects) are created anew each time.

Also connection pooling ensures that connections to the SQL server are use efficiently, but doesn't warranty that a new connection doesn't get created (for example after a period of relative idle, some connections may time out, and are then dropped and re-created).

Edit (on the support for legacy RDBMS etc. [Comment from Amitabh])
With ODBC, connection pooling is a feature of the ODBC layer, not of the various drivers ODBC uses to connect to the underlying storages. Therefore, so long as you have ODBC version 3.0 or later (and so long the underlying driver is accessible to ODBC), ODBC can manage a connection pool for you (provided you supply it the necessary configuration details).
With ODBC, it appears that the connection pool can be configured/enabled programmatically. This doesn't invalidate the statement that connection pooling is transparent to the program, it's just that you may need, in the initialization section of the program, make a few calls to setup the pooling, the remainder of the logic actually using connections being kept unchanged.
see for example this MSDN article