C # SqlDataReader Executing Statistics and Information

Question

I am creating an automated queue for executing database queries, which essentially means that I create a queue of SQL queries that are executed one after the other.

Requests 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 possible about the implementation. How much time has passed. How many lines were affected.

Most importantly, if it DOESN’T, why it failed.

In fact, any information I can get about the execution that I want to keep.

Answer 1

Try using inline statistics for runtime and selected / affected rows:

 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"]; } 

Learn more about MSDN .

The only way I can see that you learn how something failed is to check the SqlException throw and view the details.

Answer 2

Although I'm a little unsure what your question really is, with that I mean if you want a list of statistics that can be useful for saving or how to get the statistics mentioned above.

SqlDataReader has the .RecordsAffected and .FieldCount , which tell a little about how much data was returned.

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