. Net connection pool

Ado. Net opens the connection pool by default during database operations without manual configuration. This feature can improve the database operation efficiency, but it also requires code cooperation to truly improve the program efficiency.

1. connection string

The size of the Connection Pool in ADO. Net can be controlled by the database connection string, for example:

String cs =

"Server =.; uid = sa; Pwd = tcaccp; database = pubs; pooling = true; min pool size = 5; Max pool size = 10"

Pooling indicates whether to enable the connection pool. By default, pooling = false is required when the connection pool is disabled;

Min pool size indicates that the connection pool saves at least a few connection objects;

Max pool size indicates that the connection pool can store up to several connection objects. (The maximum value cannot be 0 or smaller than the minimum value)

After configuration, you can use sqlconnection con = new sqlconnection (CS); to obtain a connection object belonging to the connection pool.

However, you must note that any changes to the connection string will be considered as another completely different database connection, and a new connection pool will be created, which will inevitably cause greater system overhead. Therefore, in order to ensure that some connection objects belong to a connection pool, the connection string cannot be changed, including case and space.

2. Changes in the program

Common Database Operations:

Sqlconnection con = new sqlconnection (CS );

Try

{

Con. open ();

// Perform various database operations

}

Catch (exception ex) {console. writeline (ex. Message );}

Finally

{

Con. Close ();

Con. Dispose ();

}

This process is cumbersome. After each operation, you must ensure that the connection object is closed and resources are released. After the connection pool feature is enabled, the finally content actually sets the status of the connection object to closed and then places it back in the connection pool. Since the system knows to put the connection pool back, is there any better way?

Using (sqlconnection con = new sqlconnection (CS ))

{

Try

{

Con. open ();

// Do something ......

}

Catch (exception ex) {console. writeline (ex. Message );}
}

The Using statement in. Net can not only import namespaces, but also use an object in the program. As in the above Code, con only has the Large arc corresponding to using. What's more amazing is that using can automatically call con when the object scope ends. dispose () releases the object, so no con in the above Code. close () and con. dispose () can also release resources and put them back into the connection pool, saving the trouble of finally and manual shutdown. However, you must also note that, since using automatically calls the dispose () method of the object at the end of the scope, it means that not all types of objects can be automatically released in the form of using, you must implement the idispose interface.

3. Monitor the connection pool status

The connection pool is completely controlled by the system when it is working. How can we monitor the connection object information in the connection pool?

Start from two aspects: database server and database driver.

The database server is the destination of the connection object. by querying the server status, you can see the access information of the connection pool to the server. Take SQL Server 2005 as an example. Execute sp_who or sp_who2 to view the list of all database access connections. For example, if the min pool size is set to 5, at least five database objects must be maintained in the connection pool. After the program is started, execute exec sp_who2 to find that the database has five connections with corresponding database names, if no operation is performed, the operation is in sleeping state.

On the other hand, database drivers are required for database operations. Since the connection pool can be automatically managed in ADO. net, the driver must play a very important role. The driver is monitored based on the "performance counter" function in windows.

Right-click my computer (right-click) --> Manage --> System Tools --> Performance Logs and alarms --> right-click the counter log, create a log setting, and name it ABC at will. The Settings dialog box appears, "Add object", select ". NET data provider for sqlserver ", add, close.

Below is the data sampling interval, for example, once every 1 second. Switch to the "Log Files" tab and set the log file type to "text files (separated by commas)". logs are recorded in "C: \ perflogs \ abc_000001.csv" by default, click OK to close the form.

Return to the "counter log" window to ensure that the ABC you just set is in the green running state. If it is red, you can click ABC and then click the play button (black triangle) on the toolbar to start it.

After setting, you can have the program run, its database connection pool status, and the number of connected objects will be recorded in "C: \ perflogs \ abc_000001.csv". Double-click the program to view it, open it in Excel. The opening time of the file is recorded on the left. We are concerned about the time above:

Numberofpooledconnections: Number of connection objects in the pool

Numberofactiveconnectionpoolgroups: Number of active connection pool groups

Numberofinactiveconnectionpoolgroups: number of inactive connection pool groups

Numberofactiveconnectionpools: Number of active connection pools

Numberofinactiveconnectionpools: number of inactive connection pools

Based on the log data, we can monitor the connection pool status when the program is running, and adjust the connection pool settings based on the data.