Would you like to know how many server round trips it took to retrieve your data using ADO.NET? Would about the number of bytes sent and received? Total connection time? Command execution time? Number of returned rows?
Welcome to the System.Data.SqlClient.SqlStatistics class. While the class itself is unavailable outside the .NET framework (it’s implemented using internal class modifier), we can still using it via RetrieveStatistics method on the connection class.
Here is the information you would get:
To get the statistics above, all you need to do is set StatisticsEnabled property to true prior to opening the connection:
cn.StatisticsEnabled = true;
and get the stats at any point you want to see the results (but before disposing the connection) by calling RetrieveStatistics method:
System.Collections.IDictionary stats = cn.RetrieveStatistics();
POINT FOR CONSIDERATION: think of creating a wrapper MySqlConnection class that encapsulates System.Data.SqlClient.SqlConnection class (unfortunately, the later on is sealed, so we can’t simply inherit from it)… In the Open() method, your MySqlConnection class would read the StatisticsEnabled value from a configuration file and set it accordingly (this gives you the flexibility to turn this type of tracing on when you are hunting down a database related performance problem). In the Dispose() method, if the statistics were collected, it would publish them, for example, by firing a DataStats event, so that “interested parties” could receive this event and do what they need with that data following the loosely-coupled event design model. You could have a subscriber of DataStats event that simply logs it to database for further analysis; and another one that looks at execution time and if it’s over a certain threshold, inform proper individuals immediately, etc. And yes, using WMI for this should certainly be considered…