Shulou(Shulou.com)05/31 Report--

Net Framework SQL Server data how to provide program connection pool, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Creation and allocation of pools

When a connection is opened, a connection pool is created based on a precise matching algorithm that associates the connection pool with a string in the connection. Each connection pool is associated with a different connection string. When a new connection is opened, if the connection string does not exactly match the existing pool, a new pool is created.

In the following example, three new SqlConnection objects are created, but only two connection pools are needed to manage them. Note that the difference between the first and second connection strings is the value assigned to Initial Catalog.

SqlConnection conn = new SqlConnection (); conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind"; conn.Open (); / / Pool An is created.SqlConnection conn = new SqlConnection (); conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=pubs"; conn.Open (); / / Pool B is created because the connection strings differ.SqlConnection conn = new SqlConnection (); conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind"; conn.Open (); / / The connection string matches pool A.

Once a connection pool is created, it is not destroyed until the active process terminates. Maintenance of inactive or empty pools requires minimal system overhead.

Addition of connection

Connection pooling is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool to meet the minimum pool size. Connections are added to the pool as needed until the maximum pool size is reached.

When a SqlConnection object is requested, it will be fetched from the pool if a connection is available. To be available, the connection must be currently unused, have a matching transaction context or is not associated with any transaction context, and have a valid link to the server.

If the maximum pool size has been reached and no connection is available, the request will be queued. When the connection is released back into the pool, the connection pool manager satisfies these requests by reassigning the connection. When Close or Dispose is called on Connection, the connection is released back into the pool.

Warning it is recommended that you always close Connection after you have finished using it so that the connection can be returned to the pool. This can be done using the Close or Dispose methods of the Connection object. Connections that are not explicitly closed may not be added or returned to the pool. For example, if a connection is out of range but not explicitly closed, the connection is returned to the connection pool only if the maximum pool size is reached and the connection is still valid. Be careful not to call Close or Dispose on Connection, DataReader, or any other managed object in the Finalize method of the class. In the finalizer, only unmanaged resources that are directly owned by the class are released. If the class does not own any unmanaged resources, do not include the Finalize method in the class definition. For more information, see garbage Collection programming. Removal of connection

If the connection lifetime has expired, or if the connection pool manager detects that the connection to the server has been disconnected, the connection pool manager removes the connection from the pool. Note that this condition can only be detected after attempting to communicate with the server. If a connection is found to be no longer connected to the server, it is marked as invalid. The connection pool manager periodically scans the connection pool for objects that have been released into the pool and marked as invalid. When found, these connections will be permanently removed.

If there is a connection to a server that has disappeared, it is possible to remove the connection from the pool even if the connection pool manager does not detect the disconnected connection and marks it as invalid. When this happens, an exception is generated. However, in order to release the connection back into the pool, it must still be closed.

Transaction support

Connections are taken out of the pool and allocated based on the transaction context. The context of the request thread and the assigned connection must match. Therefore, each connection pool is actually divided into connections that do not have an associated transaction context and N subparts that each contain connections to a particular transaction context.

When the connection is closed, it is released back into the pool and placed into the appropriate subsection according to its transaction context. Therefore, even if the distributed transaction is still suspended, the connection can still be closed without generating an error. This allows you to commit or abort the distributed transaction later.

Use the connection string keyword to control connection pooling

The ConnectionString property of the SqlConnection object supports connection string key / value pairs that can be used to adjust the behavior of connection pooling logic.

The following table describes the ConnectionString values that can be used to adjust connection pooling behavior.

Connection Lifetime0 when a connection returns to the pool, its creation time is compared with the current time, and if the interval exceeds the value specified by Connection Lifetime in seconds, the connection is destroyed. It can be used in the aggregation configuration to force load balancing between the running server and the server that is just online.

If the value is zero (0), the pooled connection has the maximum timeout period.

Connection Reset'true' determines whether to reset the database connection when it is removed from the pool. For Microsoft SQL Server version 7. 0, if set to false, an additional round trip is avoided when obtaining a connection, but it is important to note that the connection state, such as the database context, is not reset. When Enlist'true' is true, if a transaction context exists, the pool manager automatically enlists the connection in the current transaction context of the creation thread. The maximum number of connections allowed in the Max Pool Size100 pool. The minimum number of connections maintained in the Min Pool Size0 pool. When Pooling'true' is true, the connection is removed from the appropriate pool or, if necessary, a connection is created and added to the appropriate pool.

Performance counter for connection pool

The SQL Server .NET Framework data provider adds several performance counters that will enable you to fine-tune connection pooling features, detect intermittent problems related to failed connection attempts, and detect problems related to timeout requests to SQL Server.

The following table lists the connection pool counters that can be accessed in performance Monitor under the .NET CLR data performance object.

SqlClient: the current number of pooled or non-pooled connections in Current # pooled and non pooled connections. SqlClient: the number of connections associated with a particular process in all the current pools of Current # pooled connections. SqlClient: the number of pools that Current # connection pools is currently associated with a particular process. SqlClient: Peak # pooled connections the peak number of connections in all pools since the start of a particular process. Note that this counter is only available when associated with a specific process instance. The _ Global instance always returns 0. SqlClient: the total number of attempts by Total # failed connects to open a connection that failed for any reason.

Note that when using the SQL Server .NET Framework data provider performance counters with ASP.NET applications, only the _ Global instance is available. Therefore, the value returned by the performance counter is the sum of the counter values for all ASP.NET applications. The name default value indicates that the counter indicates whether it is helpful for you to read the above content. If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.