C# SqlDataReader Execution Statistics and Information

Question

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.

Vole

Please note, that it is not necessarily a SP, it could be a query like 'TRUNCATE TABLE daily_Stats', or an update command, or an insert command – Vole

Answer 1

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.

Micronucleus

How about for any errors, should I wrap everything in a try Catch and capture the SqlException, or is there a more explicit way of monitoring whether things are executed correctly? – Vole

@Theofanis Pantelides: Updated to show where you should be catching the exceptions from executions. You should also place the block for cn.Open() in a try-catch too, to avoid nasty errors when the connection fails. – Micronucleus

One more quick question: After executing (IDictionary stats = cn.RetrieveStatistics();) can I close the connection and proceed with using the 'stats'? or does the connection have to remain open? – Vole

Yes, you can close the connection after you retrieve the statistics. As far as I am aware, you must have the connection still open when you call .RetrieveStatistics() but the resulting IDictionary does not tie to the connection in any way, and is safe to play around with. I wrote a class to load all the details from the IDictionary as a "time frame", and then add it to a "timeline", used for more detailed analysis. It helps to not have to use the IDictionary key/value lookup all the time. – Micronucleus

Answer 2

While I am a bit unsure what your question really is, with that I mean if you want a list of statistics that could be useful to save or how to get the statistics you mention above.

SqlDataReader has properties .RecordsAffected and .FieldCount that tells you a bit about how much data was returned.

You can also catch the SqlException to find out some information about what (if anything) went wrong.

Marchetti