Monitoring the Number of Connections
To test for orphaned connections and overflowing connection pools, I wrote a sample Web-form application. This application uses the same techniques you would typically use to return data from a query. (You can download a WinForms version of this code at http://www.sqlmag.com.)
I used the code in Listing 1 to open and close connections to the Web-form application. The routine at callout A creates, opens, and executes queries against 110 new SqlConnection objects—10 more than the default pool size. You must close and discard all these connections before leaving the routine. If you don't, the SqlConnection objects are orphaned along with the associated pooled connections. The ADO.NET pooling mechanism (aka the pooler) closes the database connections, but it doesn't close the pooled connections. I set the connection pool size to 10 to make the program fail faster—if it's going to. Typically, 10 connections are plenty for a query that runs as quickly as this one does. Many developers run busy Web sites that use fewer than five connections to handle hundreds of thousands of hits a day.
The routine at callout A creates SqlConnection and SqlCommand objects, sets the CommandText, and opens the connection. Then, the code at callout B determines whether to use CommandBehavior.CloseConnection when executing the DataReader, depending on which CheckBox controls the user selected on the Web form.
In callout C's code, I specify whether to bind the DataReader rowset to a DataGrid or loop through the rowset. Callout C's code tests what happens when you reach the end of the rowset that was passed back from the data provider through the DataReader.
Now, I use the code at callout D to specify whether to manually close the connection or let some other operation such as data binding do it. Frankly, it's usually safest to close the connection manually so that you're certain the connection won't be orphaned.
If the code works up to this point, I successfully opened and closed 110 connections. However, if something goes wrong, the exception handlers in the code at callout E will trap the exception (typically a Timeout) as an InvalidOperationException, which is how ADO.NET responds when the connection pool is full.
Table 1 summarizes how various options permit the routine to work or fail. Notice that if you don't set the CommandBehavior.CloseConnection option, your operations will eventually fail—even when you're using a bound control. The process fails even when you use that option if you don't either use a complex bound control or close the SqlDataAdapter or SqlConnection manually.
When I finished working with these sample applications, I'd generated more than 1000 pooled connections—all orphaned. Although the SQL Server User Connections count was 0, about 40 connection pools were left behind. The orphaned pools didn't disappear until I rebooted the system.
The sample applications that I used for this test include routines that use the DataAdapter to return rows. Unless you manually manage connections, the DataAdapter properly opens and closes the SqlConnection object, so you're not likely to encounter orphaned pool connections. However, if your application uses both a DataReader and a DataAdapter, you might find that the DataAdapter can't run a query against a connection if the connection is associated with an unclosed DataReader.
Prev. page
1
[2]
3
4
next page 
