Using ExecuteNonQueryAsync and Reporting

Question

I thought I was trying to do something very simple. I just want to report the running number on the screen so that the user understands that the SQL stored procedure that I am running is working and that they don’t get impatient and start pressing buttons.

The problem is that I cannot figure out how to actually call the progress reporter for the ExecutNonQueryAsync team. It gets stuck in my report loop and never executes a command, but if I put it after the async command it will be executed and the result will never be zero.

Any thoughts, comments, ideas will be appreciated. Thank you very 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

You just want to tell the user that something is happening, and you actually do not need to show the current progress?

If so, you can simply display the ProgressBar with its Style set to Marquee .

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

eg.

 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 2

You cannot force ExecuteNonQueryAsync to do what you want here. To accomplish what you are looking for, the result of the method must be either in rows or in chunks that increase during the SQL call, but this is not how the SQL Server query package works, or really, as you want it to work prospects. You pass the SQL statement to the server and after it has finished processing the statement, it returns the total number of rows affected by the statement.

Answer 3

The easiest way to do this is to use a second connection to monitor progress and report it. Here is a small example 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; } } } } 

What would you 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(); } } 

What 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 4

This is an interesting question. In the past, I had to implement such things. In our case, the priority was the following:

• Respond on the client side in case the user does not want to stick and wait.
• Update your user action and progress.

What I would do is use threading to start the process in the background, for example:

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

Then, using the method of estimating working time, I would increase the progress indicator on the client side on the watch. To do this, request data for a variable that gives you a linear relationship with the working time required for the FunctionThatCallsSQLandTakesTime function.

For example; the number of active users this month controls the time of FunctionThatCallsSQLandTakesTime. For each 10,000 user, this takes 5 minutes. This way you can update your progress bar accordingly.

Answer 5

I am wondering if this could be a reasonable approach:

  IAsyncResult result = cmd2.BeginExecuteNonQuery(); int count = 0; while (!result.IsCompleted) { count++; if (count % 500 == 0) { lblProcessing.Text = "Transactions " + i.ToString(); lblProcessing.Refresh(); } // Wait for 1/10 second, so the counter // does not consume all available resources // on the main thread. System.Threading.Thread.Sleep(100); }