July 20, 2011, 3:13 a.m.
posted by pitbull
Retrieving Connection Statistics
A relatively simple technique that may be useful for monitoring the processes in progress across a connection to a data store has been added to ADO.NET 2.0 in the form of connection statistics retrieval. However, bear in mind that this produces a considerable performance hit, so it should be used only when necessary—for example, during page monitoring or debugging exercises. Note that the feature described here is enabled only for the SqlConnection class at present.
Before executing one or more queries, whether they return rows or update existing data, you can turn on statistics collection for the connection they use. Then, once the process is complete, you collect these statistics into any data structure that exposes the IDictionary interface. The simplest of these is a HashTable, as used in the example coming up shortly.
To support statistics retrieval, the SqlConnection class gains a new property and a couple of new methods in ADO.NET version 2.0 (see Figure).
A Simple Statistics Retrieval Example
Listing 2.13 shows the basic principle for collecting statistics information from a SqlConnection instance. After creating the Connection, the code sets the StatisticsEnabled property to True and calls the ResetStatistics method to zero the counters.
Then it opens the connection, executes a SQL UPDATE statement, and closes the connection afterward. The statistics are then retrieved into a HashTable, and the code iterates through all the values, displaying them in a Label control on the page.
' create connection to database to extract some rows Dim sConnectString As String = "your-connection-string" Dim oConn As New SqlConnection(sConnectString) ' turn on statistics collection for connection and reset buffer oConn.StatisticsEnabled = True oConn.ResetStatistics() ' fetch some rows from database into a DataReader oConn.Open() Dim oCmd As New SqlCommand("SELECT * FROM Products", oConn) Dim oReader As SqlDataReader = oCmd.ExecuteReader() oReader.Close() oConn.Close() ' retrieve Dictionary containing statistics Dim htStats As Hashtable = oConn.RetrieveStatistics() ' display values in Label control For Each oKey As String In htStats.Keys Label1.Text &= "Key: " & oKey & " = " _ & htStats(oKey) & "<br />" Next
The result is shown in Figure on the next page. You can see that there are plenty of details returned, though probably the most useful ones when you are testing applications are the BytesReceived and the ExecutionTime values.