Visual Basic 2005: A Developer's Notebook

Collect Statistics on Your Data Connections

Most programmers like to look at statistics. Considered carefully, they can suggest the underlying cause of a long-standing problem, explain the performance problems of an application, or suggest possible optimization techniques. If you're using the SQL Server provider, you can make use of a new SqlConnection.RetrieveStatistics() method to get a hashtable with a slew of diagnostic details about your database connection.

Note

Want to find out what's really going on while you're connected to a database? In . NET 2.0, you can get ahold of much more information, but only if you're using SQL Server.

How do I do that?

Before you can call RetrieveStatistics( ), you need to instruct it to collect statistics by setting the SqlConnection.StatisticsEnabled property to True. Once you take this step, the SqlConnection class will gather statistics for every database command you execute over the connection. If you perform multiple operations with the same connection, the statistics will be cumulative, even if you close the connection between each operation.

To take a look at the statistics at any time, you call the RetrieveStatistics( ) method to retrieve a hashtable containing the accumulated data. The hashtable indexes its members with a descriptive name. For example, to retrieve the number of transactions you've performed, you'd write this code:

Dim Stats as Hashtable = con.RetrieveStatistics( )
Console.Writeline(Stats("Transactions"))

To get a good ...