使用 ExecuteNonQueryAsync 并报告进度

我以为我正在尝试做一些非常简单的事情。我只想在屏幕上报告一个运行数字，以便用户知道我正在执行的 SQL 存储过程正在工作，并且他们不会不耐烦并开始单击按钮。

问题是我无法弄清楚如何实际调用 ExecutNonQueryAsync 命令的进度报告器。它陷入我的报告循环中并且从不执​​行该命令，但是如果我将它放在异步命令之后，它将被执行并且结果永远不会不等于零。

任何想法、评论、想法将不胜感激。太感谢了！

        int i = 0;
        lblProcessing.Text = "Transactions " + i.ToString();
        int result = 0;
        while (result==0)
        {
            i++;
            if (i % 500 == 0)
            {
                lblProcessing.Text = "Transactions " + i.ToString();
                lblProcessing.Refresh();
            }

        }
        //  Yes - I know - the code never gets here - that is the problem! 
        result = await cmd.ExecuteNonQueryAsync();

最简单的方法是使用第二个连接来监视进度并报告进度。这是一个可以帮助您入门的小示例：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;

namespace Microsoft.Samples.SqlServer
{
    public class SessionStats
    {
        public long Reads { get; set; }
        public long Writes { get; set; }
        public long CpuTime { get; set; }
        public long RowCount { get; set; }
        public long WaitTime { get; set; }
        public string LastWaitType { get; set; }
        public string Status { get; set; }

        public override string ToString()
        {
            return $"Reads {Reads}, Writes {Writes}, CPU {CpuTime}, RowCount {RowCount}, WaitTime {WaitTime}, LastWaitType {LastWaitType}, Status {Status}";
        }
    }
    public class SqlCommandWithProgress
    {


        public static async Task ExecuteNonQuery(string ConnectionString, string Query, Action<SessionStats> OnProgress)
        {
            using (var rdr = await ExecuteReader(ConnectionString, Query, OnProgress))
            {
                rdr.Dispose();
            }
        }

        public static async Task<DataTable> ExecuteDataTable(string ConnectionString, string Query, Action<SessionStats> OnProgress)
        {
            using (var rdr = await ExecuteReader(ConnectionString, Query, OnProgress))
            {
                var dt = new DataTable();

                dt.Load(rdr);
                return dt;
            }
        }


        public static async Task<SqlDataReader> ExecuteReader(string ConnectionString, string Query, Action<SessionStats> OnProgress)
        {
            var mainCon = new SqlConnection(ConnectionString);
            using (var monitorCon = new SqlConnection(ConnectionString))
            {
                mainCon.Open();
                monitorCon.Open();



                var cmd = new SqlCommand("select @@spid session_id", mainCon);
                var spid = Convert.ToInt32(cmd.ExecuteScalar());

                cmd = new SqlCommand(Query, mainCon);

                var monitorQuery = @"
select s.reads, s.writes, r.cpu_time, s.row_count, r.wait_time, r.last_wait_type, r.status
from sys.dm_exec_requests r
join sys.dm_exec_sessions s 
  on r.session_id = s.session_id
where r.session_id = @session_id";

                var monitorCmd = new SqlCommand(monitorQuery, monitorCon);
                monitorCmd.Parameters.Add(new SqlParameter("@session_id", spid));

                var queryTask = cmd.ExecuteReaderAsync( CommandBehavior.CloseConnection );

                var cols = new { reads = 0, writes = 1, cpu_time =2,row_count = 3, wait_time = 4, last_wait_type = 5, status = 6 };
                while (!queryTask.IsCompleted)
                {
                    var firstTask = await Task.WhenAny(queryTask, Task.Delay(1000));
                    if (firstTask == queryTask)
                    {
                        break;
                    }
                    using (var rdr = await monitorCmd.ExecuteReaderAsync())
                    {
                        await rdr.ReadAsync();
                        var result = new SessionStats()
                        {
                            Reads = Convert.ToInt64(rdr[cols.reads]),
                            Writes = Convert.ToInt64(rdr[cols.writes]),
                            RowCount = Convert.ToInt64(rdr[cols.row_count]),
                            CpuTime = Convert.ToInt64(rdr[cols.cpu_time]),
                            WaitTime = Convert.ToInt64(rdr[cols.wait_time]),
                            LastWaitType = Convert.ToString(rdr[cols.last_wait_type]),
                            Status = Convert.ToString(rdr[cols.status]),
                        };
                        OnProgress(result);

                    }

                }
                return queryTask.Result;


            }
        }
    }
}

你会这样称呼它：

    class Program
    {

        static void Main(string[] args)
        {
            Run().Wait();

        }
        static async Task Run()
        {
            var constr = "server=localhost;database=tempdb;integrated security=true";
            var sql = @"
set nocount on;
select newid() d
into #foo
from sys.objects, sys.objects o2, sys.columns 
order by newid();
select count(*) from #foo;
";

            using (var rdr = await SqlCommandWithProgress.ExecuteReader(constr, sql, s => Console.WriteLine(s)))
            {
                if (!rdr.IsClosed)
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine("Row read");
                    }
                }
            }
            Console.WriteLine("Hit any key to exit.");
            Console.ReadKey();


        }
    }

哪个输出：

Reads 0, Writes 0, CPU 1061, RowCount 0, WaitTime 0, LastWaitType SOS_SCHEDULER_YIELD, Status running
Reads 0, Writes 0, CPU 2096, RowCount 0, WaitTime 0, LastWaitType SOS_SCHEDULER_YIELD, Status running
Reads 0, Writes 0, CPU 4553, RowCount 11043136, WaitTime 198, LastWaitType CXPACKET, Status suspended
Row read
Hit any key to exit.