Connecting to a database is the single slowest operation performed by a data-centric application. This document describes how reusing pooled connections, instead of creating new connections, can improve .NET application performance.
You can control connection pooling behavior by using the connection string options set for your ADO.NET data provider. For example, connection string options can define the following settings for the DataDirect Connect® for ADO.NET data providers:
Number of connection pools (Max Number of Pools)
Maximum connection pool size (Max Pool Size)
Minimum number of connections in a connection pool (Min Pool Size)
Number of seconds to keep connections in a connection pool (Connection Lifetime)
Whether to enable Windows Integration authentication for the SQL Server data provider (Integrated Security)
This document also describes the performance advantages of DataDirect's technique of handling dead connections in a connection pool, as well as tips on opening and closing connections. In addition, C# code examples illustrate how to create connection pools and how to handle distributed transactions when using a connection pool.
NOTE: Code examples in this document use the ADO.NET 2.0 Common Programming Model and MetaData capabilities introduced in the Microsoft .NET 2.0 Framework. If you are using the .NET Framework 1.x or DataDirect Connect for .NET 2.2 data providers, refer to Connection Pooling in .NET Applications.
Connecting to a database is the single slowest operation performed by a data-centric application. This document describes how reusing pooled connections, instead of creating new connections, can improve .NET application performance.
You can control connection pooling behavior by using the connection string options set for your ADO.NET data provider. For example, connection string options can define the following settings for the DataDirect Connect® for ADO.NET data providers:
Number of connection pools (Max Number of Pools)
Maximum connection pool size (Max Pool Size)
Minimum number of connections in a connection pool (Min Pool Size)
Number of seconds to keep connections in a connection pool (Connection Lifetime)
Whether to enable Windows Integration authentication for the SQL Server data provider (Integrated Security)
This document also describes the performance advantages of DataDirect's technique of handling dead connections in a connection pool, as well as tips on opening and closing connections. In addition, C# code examples illustrate how to create connection pools and how to handle distributed transactions when using a connection pool.
Back to top
Managing Connection Pools
Connecting to a database is the single slowest operation inside a data-centric application. With connection pooling, you deliver the optimal environment for database-driven applications. Connection pooling allows you to reuse connections rather than create a new one every time the ADO.NET data provider needs to establish a connection to the underlying database.
Connection pooling behavior can be controlled by using connection string options (see the documentation for your data provider). For example, for most ADO.NET data providers, connection string options can define the number of connection pools, the number of connections in a pool, and the lifetime of pooled connections used by each process.
Connection pooling in ADO.NET is not provided by the core components of the Microsoft .NET Framework. It must be implemented in the ADO.NET data provider itself. All DataDirect ADO.NET data providers provide the same connection pooling functionality. By thoughtfully planning for connection management before implementation, you can improve application performance and maintainability.
Back to top
Creating Connection Pooling
Each connection pool is associated with a specific connection string. By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.
The pool remains active as long as any connections remain open, either in the pool or used by an application with a reference to a Connection object that has an open connection.
If a new connection is opened and the connection string does not exactly match an existing pool, a new pool must be created. By using the same connection string, you can enhance the performance and scalability of your application.
In the following C# code fragment, three new DbConnection objects are created, but only two connection pools are required to manage them. Note that the connection strings for conn1 and conn2 differ by the values assigned for User ID, Password, and Min Pool Size connection string options.
