What is connection pooling?
Connection pooling allows you to reuse existing connections to reduce the overhead of continually creating and dropping connections that have the same configuration. In other words, opening and closing connections that use the same connection string and credentials can reuse an available connection in the pool. Typical applications use the same connection objects to continually 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 drop 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 connection pooling completely) by setting specific connection string keywords for connection pooling. For example, to specifically disable connection pooling, 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 to all providers. For example, the OLE DB provider controls the connection pool (also known as the resource or session pool) based on the value set for the OLE DB Services keyword in the connection string.
In addition to connection string properties that control connection pooling behavior, there are also methods available on connection objects that can also affect connection pooling. Available methods are typically used when you are closing connections in your application and you know they won't be used again. This cleans up the connection pool by discarding connections rather than returning them to the pool when they are closed. Any connections that are already in the pool and open will be discarded 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 enabled, but the level of control available for working with connection pooling varies depending 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 pool behavior by setting connection pool keywords in the connection string as previously described in Table 5-7. For example, consider a connection where you want to set the minimum pool size. By assigning a value greater than zero to the Mîn Pool Size keyword, you ensure that the pool will not be destroyed until the application terminates. To set the minimum pool size to 5, use a connection string similar to the following:
Data Source=SqlServerName;Initial Catalog=DatabaseName; Integrated security=True;Minimum pool size=5
The minimum pool size is 0 by default, which means that each connection needs to be created and initialized as 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 these 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 the pool by setting connection string keywords. Table 5-10 details the connection string keywords available for changing connection pool activities.
Connection error handling
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 handle 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 SqlErrorCollection class (a collection of SqlError classes) that always contains at least one SqlError object.
MORE INFORMATION SQL Server Errors
SqlConnection will remain open for messages with a severity level of 19 or less, but will typically close automatically when the severity is 20 or greater.
Summary
- Connection pooling is enabled by default.
- Connection pooling options are defined in the connection string, except for the ODBC provider, which uses the ODBC Data Source Administrator dialog box on Windows.
- A SqlException object is created when an error is detected in the SQL server.
- Each instance of a SqlException exception contains at least one SqlError warning that contains the actual server error information.
- 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 configurations that contain sensitive information.