Retrieving Statistics Using SqlConnection

Statistics are always useful to keep track of the application performance. Database statistics are always the main interest of the developer since they need to track that how many times the application has accessed the database. In this article I will show you that how you can use SqlConnection class to retrieve the statistics.

Introduction:

Using SqlConnection.RetrieveStatistics Method:

You can easily retrieve some useful statistics about the database by using the RetrieveStatistics method of the SqlConnection object. This method is not available for the OleDbConnection and OracleConnection. First thing you need to do is to tell the SqlConnection that we are keeping track of everything you are doing. For this we have to set the myConnection.StatisticsEnabled = true.

Now, you can perform your database operations by opening the connection and executing the command object as shown in the code below:

private const string SEL_GET_ALL_CATEGORIES = "SELECT * FROM Categories SELECT * FROM Products SELECT * FROM Orders ";

SqlConnection myConnection = new SqlConnection(ConnectionString);

SqlCommand myCommand = new SqlCommand(SEL_GET_ALL_CATEGORIES , myConnection);

// Records the statistics

myConnection.StatisticsEnabled = true;

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

Now, let's see how we can retrieve the Statistics from the Connection.

IDictionary stats = myConnection.RetrieveStatistics();

foreach (DictionaryEntry entry in stats)

{

Response.Write("Key is " + entry.Key.ToString() + "Value:" + entry.Value.ToString());

Response.Write("<BR>");

}

myConnection.RetrieveStatistics returns the IDictionary object which contains the information in the form of key-value pairs. After that I simply iterated through the collection and printed out the key and the corresponding value on the screen.

Here is the output:

Some of the important values are discussed below:

SumResultsSetsValue: This represents the number of queries that I have performed.

ConnectionTimeValue: This represents the time the connection has been open.

BytesSendValue: This represents the bytes sent from the SQL SERVER database based on the query performed.

If you use SqlDataReader to select the values then it will be much faster. Take a look at the code below and the result:

SqlConnection myConnection = new SqlConnection(ConnectionString);

SqlCommand myCommand = new SqlCommand(SEL_GET_ALL_CATEGORIES, myConnection);

// Records the statistics

myConnection.StatisticsEnabled = true;

myConnection.Open();

SqlDataReader reader = myCommand.ExecuteReader();

while (reader.Read())

{

// fill the list

}

myConnection.Close();

reader.Close();

myCommand.Dispose();

IDictionary stats = myConnection.RetrieveStatistics();

foreach (DictionaryEntry entry in stats)

{

Response.Write("Key is " + entry.Key.ToString() + "Value:" + entry.Value.ToString());

Response.Write("<BR>");

}

And here is the result:

See the difference!

SqlConnection.RetrieveStatistics method is a very useful method which can easily be used to find the insights of the calls made to the database.

I hope you liked this article, happy coding!

If you are one of the thousands that visit GridViewGuy for your .NET articles and resources, you might be interested in making a donation. Extra cash helps pay for the hosting services and speed things up around here, and makes this website possible.

Make a Donation

Once, again thank you very much and remember its because of you FINE people that this website is up and running.

Export Button is a custom control that let's you export your DataGrid or TextBox data to several different formats. The control is extremely easy to use and also exposes design time features. In this article I will discuss some of the features of the Export Button and how it benefits the developer.

BUY IT NOW