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.

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 — Theofanis Pantelides, Jan 21 '10 at 09:29

Codesleuth · Accepted Answer · 2010-01-21T09:46:07.797

7

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.

edited Jan 21 '10 at 09:46

answered Jan 21 '10 at 09:33

Codesleuth

10,321
8
51
71

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? – Theofanis Pantelides Jan 21 '10 at 09:40
1

@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. – Codesleuth Jan 21 '10 at 09:47
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? – Theofanis Pantelides Jan 21 '10 at 09:50
1

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. – Codesleuth Jan 21 '10 at 10:00

score 1 · Answer 2 · answered Jan 21 '10 at 09:31

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.

C# SqlDataReader Execution Statistics and Information

2 Answers2

Linked