C# SqlDataReader 执行统计和信息 [英] C# SqlDataReader Execution Statistics and Information

问题描述

我正在创建一个自动化的数据库查询执行队列，这实质上意味着我正在创建一个 SQL 查询队列，这些队列一个一个地执行.

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.

推荐答案

尝试使用内置的执行时间和选定/受影响行的统计信息:

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

在 MSDN 上查看更多信息.

See more on MSDN.

我能看到您找出失败原因的唯一方法是检查 SqlException 抛出并查看详细信息.

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

这篇关于C# SqlDataReader 执行统计和信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！