views:

212

answers:

2

Hi,

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.

+1  A: 

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.

Don
+4  A: 

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.

Codesleuth
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
as Don suggested below
Theofanis Pantelides
@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
Thank you very much!
Theofanis Pantelides
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
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