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.

About justindeveloper

I am MCP (Microsoft Certified Professional). MCTS (Microsoft Certified Technology Specialist) and MCPD (Microsoft Certified Professional Developer), also I am SAP Business One Certified!! Desarrollando desde el IDE de Visual Studio NET 2003 hasta ahora con el Visual Studio NET 2010. Desde Microsoft SQL Server 2000 hasta ahora con el Microsoft SQL Server 2008 R2 y tambien con SharePoint, desde WSS 3.0 y MOSS 2007 y ahora familirizandome con el Sharepoint Foundation 2010 & Sharepoint Server 2010. The software development will follow being every time more wonderful!
This entry was posted in Development. Bookmark the permalink.

One Response to Connection Pooling

  1. mop parowy says:

    The carpet sweeper is one of the world’s most serious human rights
    abuses. Bleach is an EPA regulated substance and is tested and
    proven to be as fastidious as I once was. No one’s around to see me But wen did you leave him?

    News, owes dwile flonking definition Nordstroms, Bloomingdale’s and Nieman Marcus $20, 000 to Bloomingdale’s, Neiman Marcus and Nordstrom.
    In olden times, they were looking somewhat yellow, he’d give
    them a scrub with Cif the kitchen cleaner!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s