Using ExecuteNonQueryAsync and Reporting Progress

Question

I thought I was trying to do something very simple. I just want to report a running number on the screen so the user gets the idea that the SQL Stored Procedure that I'm executing is working and that they don't get impatient and start clicking buttons.

The problem is that I can't figure out how to actually call the progress reporter for the ExecutNonQueryAsync command. It gets stuck in my reporting loop and never executes the command but, if I put it after the async command, it will get executed and result will never not equal zero.

Any thoughts, comments, ideas would be appreciated. Thank you so much!

        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();

Answer 1

The simplest way to do this is to use a second connection to monitor the progress, and report on it. Here's a little sample to get you started:

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;


            }
        }
    }
}

Which you would call something like this:

    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();


        }
    }

Which outputs:

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.

Answer 2

You're not going to be able to get ExecuteNonQueryAsync to do what you want here. To do what you're looking for, the result of the method would have to be either row by row or in chunks incremented during the SQL call, but that's not how submitting a query batch to SQL Server works or really how you would want it to work from an overhead perspective. You hand a SQL statement to the server and after it is finished processing the statement, it returns the total number of rows affected by the statement.

Answer 3

Do you just want to let the user know that something is happening, and you don't actually need to display current progress?

If so, you could just display a ProgressBar with its Style set to Marquee.

If you want this to be a "self-contained" method, you could display the progress bar on a modal form, and include the form code in the method itself.

E.g.

public void ExecuteNonQueryWithProgress(SqlCommand cmd) {
    Form f = new Form() {
        Text = "Please wait...",
        Size = new Size(400, 100),
        StartPosition = FormStartPosition.CenterScreen,
        FormBorderStyle = FormBorderStyle.FixedDialog,
        MaximizeBox = false,
        ControlBox = false
    };
    f.Controls.Add(new ProgressBar() { 
        Style = ProgressBarStyle.Marquee,
        Dock = DockStyle.Fill
    });
    f.Shown += async (sender, e) => {
        await cmd.ExecuteNonQueryAsync();
        f.Close();
    };
    f.ShowDialog();
}

Answer 4

That is an interesting question. I have had to implement similar things in the past. In our case the priority was to:

• Keep client side responsive in case the user doesn't want to stick around and wait.
• Update the user of action and progress.

What I would do is use threading to run the process in the background like:

HostingEnvironment.QueueBackgroundWorkItem(ct => FunctionThatCallsSQLandTakesTime(p, q, s));

Then using a way to estimate work time I would increment a progress bar from client side on a clock. For this, query your data for a variable that gives you a linear relationship to the work time needed by FunctionThatCallsSQLandTakesTime.

For example; the number of active users this month drives the time FunctionThatCallsSQLandTakesTime takes. For each 10000 user it takes 5 minutes. So you can update your progress bar accordingly.