Ado. NET Connection Pooling FAQ

ADO Summary



Connection pooling allows an application to obtain a connection from the connection pool and use the connection without having to re-establish a connection for each connection request. Once a new connection is created and placed in a connection pool, the application can reuse the connection without having to implement the entire database connection creation process.



When an application requests a connection, the connection pool assigns a connection to the application rather than re-establish a connection, and when the application finishes using the connection, the connection is returned to the connection pool instead of being released directly.



How to implement a connection pool



Make sure that you use the same connection string (same as the connection pool) for each connection, and that only the connection string will work together. If the connection string is not the same, the application does not use the connection pool but instead creates a new connection.



Advantages



The main advantage of using connection pooling is performance. The time it takes to create a new database connection depends primarily on the speed of the network and the distance between the application and the database server (the network), and this process is often a time-consuming process. With the database connection pool, the database connection request can be satisfied directly through the connection pool without having to reconnect the request and authenticate to the database server, thus saving time.



Disadvantages



There may be a number of unused connections in the database connection pool that have been linked to the database (which means waste of resources).



Tips and Hints



1. Create a connection pool when you need a database connection instead of building it in advance. Once you have finished using the connection, close it immediately, and don't wait for the garbage collector to handle it.

2. Ensure that all user-defined transactions are closed before you close the database connection.

3. Do not close all connections in the database, at least one of the connections in the connection pool is available. If memory and other resources are issues that you must consider first, you can turn off all connections and then create a connection pool when the next request arrives.



Connection Pooling FAQ



1. When do I create a connection pool?

Create a connection pool when the first connection request arrives; The connection pool's establishment is determined by the connection character Fu Shilai the database connection. Each connection pool is associated with a different connection string. When a new connection request arrives, a connection is removed from the connection pool if the connection string is the same as the string used by the connection pool, or if it is not the same, a new connection pool is created.



2. When is the connection pool closed?

Closes the connection pool when all connections in the connection pool have been closed.



3. What happens when a connection in a connection pool is exhausted and a new connection request arrives?

When a connection pool has reached its maximum number of connections, a new connection request is placed in the connection queue when a new connection request arrives. When a connection is released to the connection pool, the connection pool assigns the newly freed connection to the queued connection request in the queue. You can call close and Dispose to return the connection to the connection pool.



4. How should I allow connection pooling?

For. NET application, the default is to allow connection pooling. (This means you don't have to do anything for this matter) of course, if you can add Pooling=true to the SqlConnection object's connection string, make sure your application allows the connection pool to be used.



5. How should I prohibit connection pooling?

Ado. NET defaults to allow database connection pooling, if you want to prohibit connection pooling, you can use the following methods:

1 when using the SqlConnection object, add the following content to the connection string: Pooling=false;

2 When using the OleDbConnection object, add the following content to the connection string: OLE DB services=-4;