Introduction:
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.
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 |