What is the connection pool associated with?

Question

I'm reading about connection pooling in .net and i'm not sure if i'm getting it right. They say that a closed connection is returned to the pool so that it can be reused later. But what is the pool associated with? Is it a database or just the application environment (computer it is executed on)? If it's the latter, then if you have two users opening their applications at two different computers and requesting the same connection one after another, the connection pooling doesn't make the difference, am i right?

Edit: of course i know it will make a difference if the same app opens the same connection again, but what about two different instances of the same app?

Answer 1

Connection pools are limited to the client PC and are associated with the connection string and the authentication details.

So if a connection closes and returns to the pool it will only be reused if the connection string and authentication details match exactly.

Answer 2

Connection pool is just a collection on the client side, that gets filled with connections when you first try to open a connection to database. If you finished working with the connection object, it is not disposed, but returned to the collection of available connections.

This way you get a performance boost, as opening and closing database connections can be very expensive (think about network traffic that is needed to establish a connection).

This is a good startup tutorial on the topic:

http://www.eggheadcafe.com/tutorials/aspnet/df2b05d8-93ea-49e1-aeaa-d116a4d6ac3b/adonet-connection-poolin.aspx

Answer 3

The ConnectionPoolCollection is actually a singleton object that holds ConnectionPool objects.

You can test this by reflectoring the static functions on the DbConnection for clearing a single pool or clearing all pools.

ClearAllPools - Clear all the pools from a DbConnection in SQL Server.

Since the object is a singleton it does not work across process boundaries. You will end up with a pool per process.

There are some ADO.NET providers that can pool across application processes through the use of a COM object to hold the instance refs, but most do not do this.