You need to use a pool of database connections to improve application performance and scalability.
Configure the connection pool using settings in the connection string of a connection object.
Connection pooling significantly reduces the overhead associated with creating and destroying database connections. Connection pooling also improves the scalability of solutions by reducing the number of concurrent connections a database must maintain ”many of which sit idle for a significant portion of their lifetimes. With connection pooling, instead of creating and opening a new connection object whenever you need one, you take an already open connection from the connection pool. When you have finished using the connection, instead of closing it, you return it to the pool and allow other code to use it.
The SQL Server and Oracle data providers encapsulate connection- pooling functionality that they enable by default. One connection pool exists for each unique connection string you specify when you open a new connection. Each time you open a new connection with a connection string that you have used previously, the connection is taken from the existing pool. Only if you specify a different connection string will the data provider create a new connection pool. You can control some characteristics of your pool using the connection string settings described in Table 10.2.
Important | Once created, a pool exists until your process terminates.
|
This code excerpt from the sample code for this recipe demonstrates the configuration of a connection pool that contains a minimum of 5 and a maximum of 15 connections. Connections expire after 10 minutes (600 seconds) and are reset each time a connection is obtained from the pool.
// Obtain a pooled connection. using (SqlConnection con = new SqlConnection()) { // Configure the SqlConnection object's connection string. con.ConnectionString = "Data Source = localhost;" + // local SQL Server instance "Database = Northwind;" + // the sample Northwind DB "Integrated Security = SSPI;" + // integrated Windows security "Min Pool Size = 5;" + // configure minimum pool size "Max Pool Size = 15;" + // configure maximum pool size "Connection Reset = True;" + // reset connections each use "Connection Lifetime = 600"; // set maximum connection lifetime // Open the Database connection. con.Open(); // Access the database... // At the end of the using block, the Dispose calls Close, which // returns the connection to the pool for reuse. }
This code excerpt demonstrates how to use the Pooling setting to obtain a connection object that isn't from a pool. This is useful if your application uses a single long-lived connection to a database.
// Obtain a non-pooled connection. using (SqlConnection con = new SqlConnection()) { // Configure the SqlConnection object's connection string. con.ConnectionString = "Data Source = localhost;" + // local SQL Server instance "Database = Northwind;" + // the sample Northwind DB "Integrated Security = SSPI;" + // integrated Windows security "Pooling = False"; // specify non-pooled connection // Open the Database connection. con.Open(); // Access the database... // At the end of the using block, the Dispose calls Close, which // closes the non-pooled connection. }
The ODBC and OLE DB data providers also support connection pooling, but they don't implement connection pooling within managed .NET classes and you don't configure the pool in the same way as for the SQL Server or Oracle data providers. ODBC connection pooling is managed by the ODBC Driver Manager and configured using the ODBC Data Source Administrator tool in the Control Panel. OLE DB connection pooling is managed by the native OLE DB implementation; the most you can do is disable pooling by including the setting "OLE DB Services=-4;" in your connection string. The SQL Server CE data provider doesn't support connection pooling because SQL Server CE supports only a single concurrent connection.