Development

Connection Pooling

What Is Connection Pooling?

Connection pooling enables the reuse of existing connections to reduce the overhead of continuously creating and disposing of connections that have the same configuration. In other words, opening and closing connections that use the same connection string and credentials can reuse a connection that is available in the pool. Typical applications use the same connection objects to continuously fetch and update data from a database. Connection pooling provides a much higher level of performance by eliminating the need for the database to constantly create and dispose of connections. Connection pools are separated by process, application domain, and connection string. For connection strings that use integrated security, a separate pool is created for each unique identity.

Controlling Connection Pooling Options

Connection pooling is enabled by default when creating ADO.NET connection objects. You can control connection pooling behavior (or disable pooling altogether) by setting connection string keywords specific to connection pooling. For example, to specifically disable connection pooling, you set Pooling=False in your connection string. Table 5-7 provides a list of connection string keywords that can be used to control how a specific connection interacts with the connection pool. Not all keywords are available for every provider. For example the OLE DB provider controls connection pooling (also known as resource or session pooling) based on the value set for the OLE DB Services keyword in the connection string.

Table Connection Pooling Connection StringIn addition to connection string properties that control connection pooling behavior, there are also methods available on connection objects that can affect the pool as well. The available methods are typically used when you are closing connections in your application and you know they will not be used again. This clears the connection pool by disposing of the connections instead of returning them to the pool when they are closed. Any connections that are already in the pool and open will be disposed of the next time they are closed. Table 5-8 lists the available methods for interacting with connection pools.

Configuring Connections to Use Connection Pooling

By default, all .NET Framework Data Providers available in ADO.NET have connection pooling turned on, but the level of control available for working with connection pooling varies  based on the provider being used.

Configuring Connection Pooling with SQL Server Connections

By default, the SqlConnection object automatically uses connection pooling. Each time you call Sqlconnection.Open with a unique connection string, a new pool is created. Control connection pooling behavior by setting the connection pool keywords in the connection string as described earlier in Table 5-7. For example, consider a connection where you want to set the minimum pooi size. By assigning a value greater than zero to the Mîn Pool Size keyword you ensure the pool will not be destroyed until after the application ends. To set the minimum pooi size to 5, use a connection string similar to the following:

Data Source=SqlServerName;Initial Catalog=DatabaseName; Integrated Security=True;Min Pool Size=5

The minimum pool size is 0 by default, which means each connection needs to be created and initialized as they are requested, by increasing the minimum pool size in the connection string the indicated number of connections are created and ready to use, which can reduce the time it takes to establish the connection on those initial connections.

Configuring Connection Pooling with Oracle Connections

Connections that use the .NET Framework Data Provider for Oracle automatically use connection pooling by default. You can control how the connection uses pooling by setting connection string keywords. Table 5-10 details the connection string keywords available for altering connection pooling activities.

Handling Connection Errors

When SQL Server returns a warning or an error, the .NET Framework Data Provider for SQL Server creates and throws a SqlException that you can catch in your application to deal with the problem. When SqlException is thrown, inspect the SqlException.Errors property to access the collection of errors that are returned from the SQL server. The SqlException.Errors property is a SqlErrorCollectíon class (a collection of SqlError classes) that always contains at least one SqlError object.

MORE INFO SQL Server errors

SqlConnection will remain open for messages with a severity level of 19 and below, but it will typically close automatically when the severity is 20 or greater.

Summary

  • Connection pooling is enabled by default.
  • Connection pooling options are set in the connection string except for the ODBC provider, which uses the ODBC Data Source Administrator dialog box in Windows.
  • A SqlException object is created when an error is detected on the SQL server.
  • Every instance of a SqlException exception contains at least one SqlError warning that contains the actual error information from the server.
  • Windows Authentication (also called Integrated Security) is the suggested method for connecting to data securely.
  • Store connection strings that contain sensitive information in the application configuration file and encrypt all settings that contain confidential information.
Advertisements