Hi, my name is Satalaj. Here, I'm going to explain what is connection pool with solid proof.
Connection pool as name suggest its a pool of Established connections.
When we are closing the connection in finally or in try catch block that doesn't mean we are closing Established connection with your db server.
Closing connection means notifying the application about that connection is free for future request.

Here, I will tell my connection string to use min pool size = 1 and max pool size = 2.

As soon as pool gets initialized it will establish only one connection with SQL server db.  

You can use below query to test how many connections are established with your MS SQL db server. I established the connection with asptest DB.

select db_name(dbid) as DataBaseName , count(dbid) as

NoOFConnections , loginame as LoginName

from sys.sysprocesses where dbid > 0 and db_name(dbid) = 'apitest'

group by dbid,loginame

 

Here is screen shot of Established connection.

 

 I fired a command netstat -b from my command prompt and found that WindowsApplication1.VShost.exe with process ID 6140
Established one connection with MS SQL server IP 192.168.1.1.28 on port 1433.

My application is running and it has processed his task and I have closed the connection. However, you can see the connection is Established
and it will stay established, still the life of application. If I colse the application, I will not see any connection with my DB APITEST.

   TCP    smartmirror:2096                      192.168.1.28:ms-sql-s  ESTABLISHED     6140
  [WindowsApplication1.vshost.exe]

In my application I have one established connection. What will happen If I get 2 requests simultaniouesly?

Ans. The application will establish another connection to DB server as my Max pool size is 2 and that new connection will serve the request.

What if I get 3 or 4 simultaneous requests?

If there is no free  connection available in connection pool the application will throw an error

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
 

What happens when I set Min pool size = 10 in my connection string ?
The application will establish 10 connection with DB.

You can see it by using query above and at client / server side, you can execute netstat -a command to know
which are the ports participates in this communication. You can see how my application is talking to sql server using ports ranging from 3979 to 3988.

 TCP    smartmirror:3979       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3980       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3981       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3982       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3983       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3984       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3985       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3986       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3987       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3988       192.168.1.28:ms-sql-s  ESTABLISHED

It proves that I have 10 Established connections with SQL server and my application.
when I tell the connection object to close the connection, It only notifies the application about that "free connection", which can be used to serve next request.
When I tell connection object to open a connection I get a free connection form connection pool.

To know more about, how clients talks to the server refer http://revenmerchantservices.com/post/2010/01/25/client-server-communication.aspx

You can have multiple pools in applications. Of Course, there is a limit as there is limits on available ports ranging from 0 to 65535.

Its best practice not to have connection string opened in infinite time to serve the request. If you let the connection opened for infinite loop
your application will not be able to serve other requests. you can try it yourself by setting min and max pool size of your connection string.

By default min pool size is 0 and max pool size is 100.

In IIS, if you configure you application pool to use more than one worker process then for every process you will get new instance of established connections.

if you set connection lifetime = 30 property of connection string in web.config your minimum establised connections will never get dead 
till the lifetime of your application pool.

To know how many worker processes are currently servicing your application pool. you need to execute below command on that IIS server.
It is always good practivce to set life time of connection forcefully. This ensures that connection is backed into the pool after the use of it.

IISapp.VBS

It will return details of worker process along with its process ID and name of application pool under whom it resides.


Conclusion: I told connection object to open a connection with SQL DB server means I'm telling connection object to setup actual connection with DB server? 
                No, I'm telling the connection object to get the already established free connection from connection pool.

I hope you understood the location of Connection Pool and its importance.

 Other usefull information:

Clearing the Pool
ADO.NET 2.0 introduces two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections in use at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.

Transaction Support
Connections are drawn from the pool and assigned based on transaction context. Unless Enlist=false is specified in the connection string, the connection pool ensures that the connection is enlisted in the Current context. When a connection is closed and returned to the pool with an enlisted System.Transactions transaction, it is set aside in such a way that the next request for that connection pool with the same System.Transactions transaction will return the same connection. If there is no connection available for that transaction, the connection is automatically enlisted when it is opened.

When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. Therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. This allows you to commit or abort the distributed transaction at a later time.

Controlling Connection Pooling with Connection String Keywords
The ConnectionString property of the SqlConnection object supports connection string key/value pairs that can be used to adjust the behavior of the connection pooling logic. For more information, see ConnectionString.

Pool Fragmentation
Pool fragmentation is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open while consuming memory, resulting in poor performance.

Pool Fragmentation Due to Integrated Security
Connections are pooled according to the connection string plus the user identity. Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server. This is a side effect of a particular Web application architecture that developers need to weigh against security and auditing requirements.

Pool Fragmentation Due to Many Databases
Many Internet service providers host several Web sites on a single server. They may use a single database to confirm a Forms authentication login and then open a connection to a specific database for that user or group of users. The connection to the authentication database gets pooled and used by everyone. However, there is a separate pool of connections to each database, thus increasing the number of connections to the server.

This is also a side effect of the application design. There is a relatively simple way to avoid this side effect, however, without compromising security when connecting to SQL Server. Instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the Transact-SQL USE statement to change to the desired database. The following code fragment demonstrates creating an initial connection to the master database and then switching to the desired database specified in the databaseName string variable.

 

Satalaj.