Retrieving Connection Statistics





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).

New Property and Methods for the SqlConnection Class

Property

Description

StatisticsEnabled

Sets or returns a Boolean value indicating whether statistics retrieval is currently enabled for this Connection. The default is False.

Methods

ResetStatistics()

Removes any cached statistics for this Connection so that the next process will be recorded with values starting at 0 or the default. No return value.

RetrieveStatistics()

Retrieves the statistics from this Connection and returns them as a data structure, such as a HashTable, that supports the IDictionary interface.

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.

Retrieving Connection Statistics
' 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.

8. Retrieving database connection statistics

graphics/02fig08.gif


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows