It is time-consuming to open or close a database connection. Therefore, ADO. Net automatically stores database connections in the connection pool. The connection pool can greatly improve data reading performance, because you do not have to wait for a new database connection, but can directly use the existing database connection in the connection pool. When the connection is closed, the connection is not actually closed. Instead, the connection is marked as unused and placed in the connection pool for the next enable. If the same details (the same database, user name, password, etc.) are provided in the connection string, the connection in the pool can be obtained and returned. Then you can use this connection to access the database. When using the sqlconnection object, you can specify the Max pool size in the connection string to indicate the maximum number of connections allowed in the connection pool. The default value is 100. You can also specify the min pool size to indicate the minimum number of connections allowed in the connection pool, the default value is 0.
Set in connection stringCodeAs follows: String CSTR = "server =\\ Audi \ Server; database = OA; uid = sa; Pwd = sa; Max pool size = 10; min pool size = 5; connection timeout = 10"
Sqlconnection con = new sqlconnection (@ CSTR ); The connection pool setting in the connection string above indicates that five sqlconnection objects are generated in the connection pool at first, and a maximum of 10 sqlconnection objects can be stored in the connection pool. If you want to open a new sqlconnection object and all the objects in the connection pool are in use, the request will wait until a sqlconnection object is closed before returning the objectProgram. If the request wait time exceeds the number of seconds specified by the connectiontimeout attribute, an exception is thrown.
Monitoring link pooling
To monitor applications with pooled links, you can use the profiler tool released with SQL Server or performance monitor released with Microsoft Windows 2000.
To use SQL Server Profiler to monitor link pooling, perform the following operations:
- ClickStart, PointingProgram, PointingMicrosoft SQL ServerAnd then clickProfilerRun profiler.
- InFileIn the menu, pointNewAnd then clickTracking.
- Provide the link content, and then clickOK.
- InTrace attributesIn the dialog box, clickEventLabel.
- InSelected event categoryList, make sure thatAudit LogonAndReview logoutThe event is displayed inSecurity ReviewBelow.
- ClickRunStart tracing. When the link is created, you will seeAudit LogonEvent. You can see it when the link is closed.Review logoutEvent.
To pooled through the performance monitor monitoring link, perform the following operations:
- Click Start, point to program, point to administrative tools, and then click performance Running Performance Monitor.
- Right-click the chart background and click Add counter.
- In the performance object drop-down list box, click SQL SERVER: General Statistics.
- In the displayed list, click the user link.
- Click Add, and then click Close.
Note:. The RTM version of the. NET Framework will also contain a set of ADO.. NET performance counters (these counters can be used together with performance monitors), which are used for SQL Server.. NET data provider monitors and accumulates the Connection Pooling status.