. NET database connection pool

You need to open the sqlconnection object, and, more importantly, close the object at the end of the query. If you forget to close the connection, the isolated connection will quickly accumulate.
Number of monitored connections
To test the isolated connection pool and the connection pool with overflow, I wrote a web form example application. Program . This application uses the same method as the one you normally use to return data from a query. (You can download this on a http://www.sqlmag.com Code .)
I used the code in Listing 1 to open and close the connection to the web form application. The routine in Annotation A creates, opens, and executes queries for 110 new sqlconnection objects-10 connections more than the default pool size. You must close and discard all these connections before leaving the routine. Otherwise, the sqlconnection object will be isolated together with the associated pool connection. Ado. Net pool mechanism (aka the pooler) closes the database connection, but does not close the pool connection. I set the connection pool size to 10 so that the program fails faster-if the program fails. Generally, 10 connections are sufficient for a query that is as fast as the query speed. Many developers run busy web sites that use less than five connections to process hundreds of thousands of clicks each day.
Mark the routine in A to create the sqlconnection object and sqlcommand object, set commandtext, and open the connection. Then, mark the code in B to determine whether commandbehavior. closeconnection is used when datareader is executed. This depends on which checkbox controls are selected on the web form.
In the Code labeled C, I specify whether to bind the datareader row set to the DataGrid, or whether to loop through the entire row set. Code test with annotation C: What happens when you reach the end of the row set passed back from the data provider through datareader.
Now, I use the code in Mark D to specify whether to manually close the connection or let another operation (for example, data binding) to complete this task. Frankly speaking, manually disabling a connection is usually the safest, so you can be sure that the connection will not be isolated.
If the code runs successfully in this step, it indicates that I have successfully opened and closed 110 connections. However, if a problem occurs, the exception handler in the Code marked E will capture the exception (usually timeout) as invalidoperationexception, which is the response method of ADO. net when the connection pool is full.
Table 1 summarizes the methods by which the routine runs successfully or fails. Note that if you do not set the commandbehavior. closeconnection option, your operation will eventually fail-even if you use the bound control. Even if you use this option, the process will still fail if you do not use complicated binding controls or do not manually close sqldataadapter or sqlconnection.
After I finish running these sample applications, I have generated more than 1000 pool connections-all connections are isolated. Although the "SQL Server user connection" count is 0, about 40 connection pools are left. The isolated pool will not disappear until I reboot the system.
Examples of applications I used for this test include routines that use dataadapter to return rows. Unless you manually manage the connection, dataadapter correctly opens and closes the sqlconnection object. Therefore, you are unlikely to encounter an isolated pool connection. However, if your application uses both datareader and dataadapter, you may find that if a connection is associated with an unclosed datareader, dataadapter cannot run queries on the connection.
Determine when the connection pool reaches the maximum number of connections
Just as I was in "Discovery Ming in. as discussed in the net connection pool article, when the connection pool reaches the maximum number of connections you specified through the "Max pool size connectionstring" option, ADO. net will block any subsequent attempts to open additional connections. If a connection becomes available before the time specified by you in the connectiontimeout option ,. . NET data provider will pass a pointer to the connection to your application to return the control to the application. However, if no connection is released in time, the connection request will cause an invalidoperationexception.
Now you must determine the measures to take. I do not recommend that you tell the user that you have used up all connections. Some applications will inform the user system that they are busy helping other customers and suggest users access them later. Other applications play an animation, notifying users that the system is not deadlocked, but is busy processing their requests. At the same time, your code will try again. In all circumstances, you should record these faults to help diagnose the crux of the problem and record that you have exhausted resources.