ADO.NET 3.5 Cookbook, 2nd Edition

10.21. Retrieving SQL Server Runtime Statistics

Problem

You want to get information about your connection to SQL Server, including bytes sent, bytes received, connection time, and execution time.

Solution

Use the RetrieveStatistics() method of the Connection class.

The solution creates a Connection and enables statistics by setting the EnableStatistics property of the Connection object to true. A DataAdapter is created and used to fill a DataTable with all records in the Person.Contact table in AdventureWorks. Statistics are retrieved into a dictionary using the RetrieveStatistics() method of the Connection object and then output to the console.

The C# code in Program.cs in the project RetrieveStatistics is shown in Example 10-33.

Example 10-33. File: Program.cs for RetrieveStatistics solution

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace RetrieveStatistics
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated Security=SSPI;Initial Catalog=AdventureWorks";
            string sqlSelect = "SELECT * FROM Person.Contact";

            // open a connection and enable statistics using (SqlConnection connection = new SqlConnection(sqlConnectString)) { connection.StatisticsEnabled = true; // do some work with the connection to generate statistics SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connection); DataTable dt = new DataTable(); da.Fill(dt); // get the statistics IDictionary d = connection.RetrieveStatistics(); ...