C # detailed analysis of the Connection Pool)

Source: http://www.25175.comAuthor: onrd

 

Use connection pool

Connecting to the database server usually consists of several steps that require a long soft time. A physical channel (such as a socket or named pipe) must be established, the connection must be established with the server for the first time, the connection string information must be analyzed, and the server must authenticate the connection.

In fact, most applicationsProgramUse one or several different connection configurations. When the application's data volume and traffic volume are large, this means that many of the same connections will be opened and closed repeatedly while the application is running, this may cause low Database Server efficiency or even cause program crashes. To ensure application stability and reduce performance costs, we can use an optimization method called connection pool in ADO. Net to manage and maintain connections.

Www.25175.net

The connection pool can reduce the number of times a connection is created. Defines the minimum number of connections (fixed connections). When you call open on the connection, the connection pool checks whether there are available connections in the pool. If a connection is available, the connection is returned to the caller instead of creating a new connection. When an application calls close on the connection, the connection pool determines whether the connection is within the minimum number of connections. If yes, the connection pool recycles the connection to the active connection pool instead of closing the connection, otherwise, the connection will be burned out. After the connection is returned to the pool, it can be reused in the next open call.

Create a connection pool
The following example uses C # to connect to the SQL database:

Class dbconn
{
// Using system. Data;
// Using system. Data. sqlclient;
Private const int maxpool = 10; // maximum number of connections
Private const int minpool = 5; // minimum number of connections
Private const bool asyn_process = true; // sets asynchronous Database Access
Private const bool Mars = true; // obtain and manage multiple result sets with forward reference only and read-only attributes on a single connection (ADO. net2.0)
Private const int conn_timeout = 15; // sets the connection wait time.
Private const int conn_lifetime = 15; // sets the connection lifecycle.
Private string connstring = ""; // connection string
Private sqlconnection sqldrconn = NULL; // connection object

Public dbconn () // Constructor
{
Connstring = getconnstring ();
Sqldrconn = new sqlconnection (connstring );
}

Private string getconnstring ()
{
Return "Server = localhost ;"
+ "Integrated Security = sspi ;"
+ "Database = pubs ;"
+ "Max pool size =" + maxpool + ";"
+ "Min pool size =" + minpool + ";"
+ "Connect timeout =" + conn_timeout + ";"
+ "Connection lifetime =" + conn_lifetime + ";"
+ "Asynchronous Processing =" + asyn_process + ";";
// + "Multipleactiveresultsets =" + MARS + ";";
}

Public datatable getdatareader (string strsql) // Data Query
{
// Close the connection when it is enabled, and then enable it again to avoid the timely update of data.
If (sqldrconn. State = connectionstate. open)
{
Sqldrconn. Close ();
}
Try
{
Sqldrconn. open ();
Sqlcommand sqlcmd = new sqlcommand (strsql, sqldrconn );
Sqldatareader sqldr = sqlcmd. executereader ();
If (sqldr. hasrows)
{
Datatable dt = new datatable ();
// Read content in sqldatareader
DT. Load (sqldr );
// Close the object and Connection
Sqldr. Close ();
Sqldrconn. Close ();
Return DT;
}
Return NULL;
}
Catch (exception ex)
{
System. Windows. Forms. MessageBox. Show (ex. Message );
Return NULL;
}
Finally
{
Sqldrconn. Close ();
}
}
}
By calling the sqldrconn. open () method to open a connection, the connection pool will initialize and set the minimum number of connections. To learn more about the connection pool status, run the storage process sp_who through the SQL query analyzer, which lists the current database process, and view loginname and dbname to differentiate user connection information, however, it should be noted that the login query analyzer itself uses two connections, so it is best to log on to the query analyzer with another user name. Another trouble to use this method is that you should always perform a query to update process information. There is another method I personally think is better, through the control panel → management tools → performance, right-click to add a calculator, performance object select sqlserver: General Statistics (General Statistics) then, the calculator selects user connections (User connection) And then presses "add" to view the current number of connections in real time.

By now, the connection pool has been implemented, but problems often occur during running. What should I do if the number of connections in the connection pool is full? Here we should reasonably set the connect timeout attribute in the connection string and the connection lifetime attribute (explained above) to extend the wait time, and try to call the close method to close the connection after each connection is used. However, this cannot be avoided. When the number of connections is full and the request connection time exceeds the set connection wait time, the program will cause an invalidoperationexceptio exception, by capturing this exception, we can prompt the user interface "the system is busy. Please connect later ......" And so on. In addition, there is another way to solve this problem, that is, using ADO. NET 2.0 new feature "Asynchronous process", asynchronous operations on the database, ensure that the connection can be promptly called close method to close the connection, this can greatly reduce the number of connections in use.

Usage: Add asynchronous Processing = true to the connection string to use Asynchronous processing.

When the application no longer needs the connection pool, you can use clearpool or clearallpools to clear the connection pool or reset the connection pool. The method is as follows:

Sqlconnection. clearpool (sqlconnection connection) clears the associated connection pool
Sqlconnection. clearallpools () Clear all connection pools
Call the above method. If the connection is in use, the connection pool will be marked accordingly and will be burned out automatically when the connection is closed.

Summary C # connection pool
Advantage: When database operations and accesses are frequent, it reduces the time required to create and open connections, improving the performance of the database server.

Disadvantages: There may be multiple unused connections in the database connection pool that have been connected to the database, which means a waste of resources.