Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to achieve ADO.NET connection testing". In daily operation, I believe many people have doubts about how to achieve ADO.NET connection testing. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "how to achieve ADO.NET connection testing". Next, please follow the editor to study!

To test orphaned and overflowed ADO.NET connections, I wrote a sample application for Web forms. This application uses the same method that you typically use to return data from a query.

I used the code in listing 1 to open and close the connection to the Web forms application. The routine in callout A creates, opens, and executes queries against 110 new SqlConnection objects-10 more connections than the default pool size. You must close and discard all these connections before leaving the routine. If you do not, the SqlConnection object will be isolated along with the associated pooled connection. The ADO.NET pooling mechanism (aka the pooler) closes database connections, but does not close pooled connections. I set the connection pool size to 10 to make the program fail faster-if it fails. In general, 10 connections is sufficient for a query that runs as fast as this query. Many developers run busy Web sites that use less than five connections to handle hundreds of thousands of clicks a day.

The routine in callout A creates SqlConnection objects and SqlCommand objects, sets the CommandText, and opens the connection. The code in callout B then determines whether to use CommandBehavior.CloseConnection when executing DataReader, depending on which CheckBox controls the user selects on the Web form.

In the code for callout C, I specify whether to bind the DataReader rowset to DataGrid or loop through the rowset. The code labeled C tests what happens when you reach the end of the rowset passed back from the data provider through DataReader.

Now, I use the code in callout D to specify whether to close the connection manually or let some other operation (for example, data binding) do this. Frankly, it is usually safest to close the connection manually, so you can be sure that the connection will not be isolated.

If the code successfully runs to this point, I have successfully opened and closed 110 connections. However, if something goes wrong, the exception handler in the code labeled E catches the exception (usually Timeout) as an InvalidOperationException, which is how the ADO.NET responds when the connection pool is full.

Summarizes how the various options make the routine run successfully or fail. Note that if you do not set the CommandBehavior.CloseConnection option, your operation will eventually fail-even if you are using bound controls. Even if you use this option, the process will fail if you do not use a complex bound control, or if you do not close SqlDataAdapter or SqlConnection manually.

By the time I finished running these sample applications, I had generated more than 1000 pooled connections-all isolated. Although the SQL Server user connections count is 0, approximately 40 connection pools are left. Orphaned pools will not disappear until I reboot the system.

The sample application I used for this test includes routines that use DataAdapter to return rows. Unless you manage connections manually, DataAdapter opens and closes SqlConnection objects correctly, so you are less likely to encounter orphaned pooled connections. 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 a query against that connection.

At this point, the study on "how to implement ADO.NET connection testing" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.