SqlConnection.RetrieveStatistics Method

The SqlConnection object has a method RetrieveStatistics which returns a name value pair collection of statistics(IDictionary Type) at the point in time the method is called.

This statistics will be very useful in debugging purposes. Let us consider an example of populating the datatable with 1 lac records.

We may want to check how many times the server round trip has happened, number of bytes received, connection time etc.

We can retrieve those statistics using the method RetrieveStatistics which was introduced in .NET 2.0.

There are totally 18 statistics information.

In this c# example we can see some statistics details (Connection Time, ServerRoundTrip and Bytes Received)

[CODE]
using System.Data.SqlClient;

private void populateGrid()

{
String ConnStr = "User ID=sa;Password=sa;Initial Catalog=OrderDB; Data Source=local";

SqlConnection connection = new SqlConnection(ConnStr);

//set StatisticsEnabled = true in order for the SqlConnection object to begin collecting statistics.

connection.StatisticsEnabled = true;

Try {

if (connection.State == ConnectionState.Closed)

{
connection.Open();

}

var strSql = "SELECT * FROM Orders";

SqlCommand cmd = new SqlCommand(strSql, connection);

cmd.CommandTimeout = 999999;


dataAdapter = new SqlDataAdapter(cmd);

lblStatus.Text = "Loading........Please Wait";

dataAdapter.Fill(dt);


//Retrieve the statistics in IDictionary

IDictionary statistics = connection.RetrieveStatistics();


//Some of the statistics

long serverRoundtrips = (long) statistics["ServerRoundtrips"];
long connectionTime = (long) statistics["ConnectionTime"];
long bytesReceived = (long) statistics["BytesReceived"];

dataAdapter.Dispose();

}

catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

}


[/CODE]

If you want to get all the statistics details we can iterate through statistics(IDictionary) in a loop and all details