Connection pool management: For systems with high access, each time you create a connection, you can consume a certain resource, we can create a certain number of connected connections in advance to use to use it to the user, and return the connection after using the user. The pool, here I will talk about the management of the connection pool.
First, let's take a look at the concept of the connection pool: 1. The connection pool allows the application to get a connection from the connection pool and use this connection without having to re-establish a connection for each connection request. Once a new connection is created and placed in the connection pool, the application can reuse this connection without having to implement the entire database connection creation process. 2. When the application requests a connection, the connection pool assigns a connection to the application instead of re-establishing a connection; when the application is connected, the connection is returned to the connection pool instead of direct release.
Advantages: 1. The most important advantage of using the connection pool is performance. 2. Creating a new database connection is mainly dependent on the speed of the network and the distance from the application and database server, and this process is usually a very time consuming process. After using the database connection pool, the database connection request can be renewed directly by the connection pool without need to reconnect, authenticate to the database server, which saves time.
Disadvantages: 1. There may be multiple connections that have not been used in the database connection pool (this means waste of resources). PS: This must require developers to estimate the maximum number of this system when developing.
Creating and assigning the pool When the connection is open, the connection pool will be created according to a precise matching algorithm, which will associate the connection pool from the string in the connection. Each connection pool is associated with a different connection string. When a new connection is turned on, a new pool will be created if the connection string does not exactly match the pool.
In the following example, three new SQLCONNECTION objects will be created, but only two connection pools are required to manage these objects. Note that the difference between the first and second connection strings is the value assigned to the Initial Catalog.
SqlConnection conn = new sqlconnection (); conn.connectionstring = "integrated security = sspi; initial catalog = northwind"; conn.open (); // Connect pool A creation
SqlConnection conn = new sqlConnection (); conn.connectionstring = "integrated security = SSPI; Initial Catalog = PUBS"; Conn.Open (); // Connecting pool B created because Connectionstring does not match A
SqlConnection conn = new sqlConnection (); conn.connectionstring = "integrated security = sspi; initial catalog = northwind"; conn.open (); // Using A connection pool, because Connectionstring matches A
Once the connection pool is created until the active process is terminated, it will be destroyed. Maintenance of non-active or empty pools requires only the least system overhead.
The connection to the connection pool is created for each unique connection string. When you create a pool, you will create multiple connection objects and add it to the pool to meet the requirements of the minimum pool size. The connection will be added to the pool as needed until the maximum pool size is reached.
When requesting a SqlConnection object, if there is a available connection, the object will be obtained from the pool. To be available, the connection must not be used, with a matching transaction context or not associated with any transaction context, and has a valid link to the server. If the maximum pool size is reached, the request will line up. When the connection is released in the pond, the connection pool management program satisfies these requests by reassigning the connection. When CONNECTION calls Close or Dispose, the connection is released in the back bank.
Warning It is recommended to turn it off after using the connection so that the connection can be returned to the pool. This can be implemented using the Close or Dispose method of the Connection object. Connections that are not explicitly closed may not be added or returned to the pool. For example, if the connection has exceeded the range but no explicitly shutdown, the connection is only valid when the connection is reached, and the connection is still valid, and the connection is returned to the connection pool. Note Do not call Close or Dispose to CONNECTION, DATAREADER, or any other managed object in the finalize method. In the terminator, only the non-hosting resources that are released directly. If the class does not have any non-managed resources, don't include the Finalize method in the class definition. For more information, see Garbage Recycling Programming.
Removal of the connection If the connection life has expired, or the connection pool management program detects that the connection to the server has been disconnected, the connection pool management program will remove the connection from the pool. Please note that this can only be detected only after the attempt is communicated with the server. If a connection is found to be connected to the server, it will be marked as invalid. The connection pool administrator regularly scans the connection pool and finds an object that is released into the pool and marked as invalid. These connections will be permanently removed after finding.
If there is a connection with the disappeared server, even if the connection pool management program does not detect the disconnected connection and it is not valid, it is possible to remove this connection from the pool. An exception is generated when this happens. However, in order to release the connection back in the pool, it must be closed.
String Keyword: Connection Lifetime 0 When the connection returns to the pool, the creation time and the current time are compared. If the time interval exceeds the value specified by the Connection Lifetime (in seconds), the connection is destroyed. . It can be used to enforce load balancing between running servers and just online servers. If the value is zero (0), the pool connection will have the largest timeout period. Connection RESET 'TRUE' is determined whether it will be reset when the database connection is removed from the pool. For Microsoft SQL Server Version 7.0, if set to false, avoid experience an additional round-trip process when the connection is acquired, but must be noted that the connection status (such as the database context) will not be reset. Enlist 'True' When True, if there is a transaction context, the pool management program will automatically register in the current transaction context of the thread. Max Pool Size 100 The maximum number of connections allowed in the pool. Min Pool Size 0 The minimum number of connections maintained in the pool. Pooling 'True' When True, the connection will be removed from the corresponding pool, or create a connection if necessary and add it to the corresponding pool.
Instance code: Namespace howto.samples.adonet {
Using system; using system.data.sqlclient;
public class connectionpooling {public static void Main () {connectionpooling myconnectionpooling = new connectionpooling (); myconnectionpooling.Run ();} public void Run () {try {String connString;
// Specification in the connection string: // Please note: Pooling is implicit, you automatically get it unless you disable it // Therefore, "true" is the default for the pooling keyword (pooling = true) // Connection Reset.. : False // Connection Lifetime: 5 // Enlist: True // MIN POOL SIZE: 1 // Max Pool Size: 50 Connstring = "Server = (local) // NetSDK; trusted_connection = yes; data = northwind;" " Connection Reset = false; " " Connection Lifetime = 5; " " MIN POOL SIZE = 1; " " Max Pool Size = 50 ";
SqlConnection myconnection1 = new sqlconnection; sqlConnection myconnection2 = new sqlConnection; sqlConnection myconnection3 = new SqlConnection (ConnString);
// Open TWO Connections. Console.writeline ("Opens two connections."); Myconnection1.open (); myconnection2.open ();
// NOW There TWO Connections In The pool That Matches The Connection String. // Return The Both Connections to the pool. Console.writeline ("Two connections are returned to the pool."); Myconnection1.close (); MyConnection2.close ();
// Get a connection out of the pool. Console.writeline ("Opens a connection from the pool."); Myconnection1.open ();
// Get A Second Connection Out of the pool. Console.writeline ("Opens the second connection from the pool."); Myconnection2.open (); // Open a third connection. Console.writeline ("Open the third Connection. "); Myconnection3.open ();
// Return the all connections to the pool. Console.writeline ("All three connections are returned to the pool."); Myconnection1.close (); myconnection2.close (); myconnection3.close ();} catch Exception E) {// Display the error. Console.writeLine (e.tostring ());}}}
}
