C# SqlDataReader Execution Statistics and Information

c#, sql-server-2005, sqlcommand, sqldatareader, statistics

Solution

Try using the built in statistics for the execution time and rows selected/affected:

using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
{
  cn.Open();
  cn.StatisticsEnabled = true;
  using (SqlCommand cmd = new SqlCommand("SP", cn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    try
    {
      using (SqlDataReader dr = cmd.ExecuteReader())
      {
        while (dr.Read())
        {

        }
      }
    }
    catch (SqlException ex)
    {
      // Inspect the "ex" exception thrown here
    }
  }

  IDictionary stats = cn.RetrieveStatistics();
  long selectRows = (long)stats["SelectRows"];
  long executionTime = (long)stats["ExecutionTime"];
}

See more on MSDN.

The only way I can see you finding out how something failed is inspecting the `SqlException` thrown and looking at the details.

Problem

I am creating an automated DB Query Execution Queue, which essentially means I am creating a Queue of SQL Queries, that are executed one by one. Queries are executed using code similar to the following: ``` using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString)) { cn.Open(); using (SqlCommand cmd = new SqlCommand("SP", cn)) { cmd.CommandType = CommandType.StoredProcedure; using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { } } } } ``` What I would like to do is collect as much information as I can about the execution. How long it took. How many rows were affected. Most importantly, if it FAILED, why it failed. Really any sort of information I can get about the execution I want to be able to save.

Original source