Animate Progress Bar while SQL Executes

Question

In my C# WindowsForm Application, I'm trying to animate a progress bar from start to finish, using a fixed duration (In my case 6500 milliseconds). This is roughly how long the SQL Execute takes.

I've tried running the animate method prior to executing the SQL, but while the SQL is executing, it appears as thought the whole thread is frozen up. I don't want to run the SQL Asynchronously in case of an exception.

I'm new to multi-threading and backgroundworker process, although I believe this may be the key?

If anyone can steer me in the right direction it would be much appreciated!

private void btnInternalMovement_Click(object sender, EventArgs e)
{

    AnimateProgBar(6500);


    // ***** BLAH MORE CODE HERE ******

    using (SqlCommand cmd = new SqlCommand("CatamacInternalStockMovement_C", SqlConnection))
    {

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ItemName", items);
        cmd.Parameters.AddWithValue("@Quantity", quantities);
        cmd.Parameters.AddWithValue("@WarehouseFrom", cbxFromWarehouse.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@WarehouseTo", cbxToWarehouse.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@LocationFrom", cbxFromZone.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@LocationTo", cbxToZone.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@UOM", "Each");
        cmd.Parameters.AddWithValue("@TransferDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@User", User);
        cmd.ExecuteNonQuery();

    }

    // PROGRESS BAR ONLY STARTS ANIMATING HERE
    MessageBox.Show("Success!");

}

And here is the code for timer / Animate method.

private void timer1_Tick(object sender, EventArgs e)
{
    if (progressBar1.Value < 100)
    {
        progressBar1.Value += 1;
        progressBar1.Refresh();
    }
    else
    {
        timer1.Enabled = false;
    }
}

public void AnimateProgBar(int milliSeconds)
{
    if (timer1.Enabled) return;

    progressBar1.Value = 0;
    timer1.Interval = milliSeconds / 100;
    timer1.Enabled = true;
}

Updated Tags, this particular application is running .NET 4.5 — Brendan Gooden, Jan 30 '17 at 01:40

score 2 · Accepted Answer · edited May 23 '17 at 12:31

Because cmd.ExecuteNonQuery(); executes inside the callback to btnInternalMovement_Click, it executes on the UI thread. The UI will be blocked during the time it takes for any callback to complete including btnInternalMovement_Click. If cmd.ExecuteNonQuery() takes a long time to complete then you will see that reflected in the UI with frozen updates; unresponsive keyboard and mouse input.

async/await with Network-bound Calls

Your call to the database is I/O-bound not CPU-bound so you should not be using Task.Run() as per Jim's incorrect answer. If you want to use async/await make sure you use the IOCP form and not spin up and/or waste an unnessary worker thread through-out the life of the call.

Avoid using Task.Run() when there is a perfectly good ExecuteNonQueryAsync() instead. ExecuteNonQueryAsync() is built with IOCP in mind and is more efficient than performing something like this:

await Task.Run(() =>
   {
      // I/O-bound: bad!  DON'T DO THIS for I/O-bound operations such 
      //as disk; network; DB etc
   });

Change this:

private void btnInternalMovement_Click(object sender, EventArgs e)
{

    AnimateProgBar(6500);


    // ***** BLAH MORE CODE HERE ******

    using (SqlCommand cmd = new SqlCommand("CatamacInternalStockMovement_C", SqlConnection))
    {

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ItemName", items);
        cmd.Parameters.AddWithValue("@Quantity", quantities);
        cmd.Parameters.AddWithValue("@WarehouseFrom", cbxFromWarehouse.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@WarehouseTo", cbxToWarehouse.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@LocationFrom", cbxFromZone.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@LocationTo", cbxToZone.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@UOM", "Each");
        cmd.Parameters.AddWithValue("@TransferDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@User", User);
        cmd.ExecuteNonQuery();

    }

    // PROGRESS BAR ONLY STARTS ANIMATING HERE
    MessageBox.Show("Success!");

}

...to:

private async void btnInternalMovement_Click(object sender, EventArgs e)
{

    AnimateProgBar(6500);


    // ***** BLAH MORE CODE HERE ******

    using (SqlCommand cmd = new SqlCommand("CatamacInternalStockMovement_C", SqlConnection))
    {

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ItemName", items);
        cmd.Parameters.AddWithValue("@Quantity", quantities);
        cmd.Parameters.AddWithValue("@WarehouseFrom", cbxFromWarehouse.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@WarehouseTo", cbxToWarehouse.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@LocationFrom", cbxFromZone.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@LocationTo", cbxToZone.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@UOM", "Each");
        cmd.Parameters.AddWithValue("@TransferDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@User", User);
        await cmd.ExecuteNonQueryAsync(); // <----- NEW

    }

    // PROGRESS BAR ONLY STARTS ANIMATING HERE
    MessageBox.Show("Success!");

}

Thanks MickyD, that's a lot of info to chew over :) I'll edit my code! — Brendan Gooden, Jan 31 '17 at 05:52
@BrendanGooden Not to worry good buddy. Wishing you well :) (check out [Stephen Cleary's blog](http://blog.stephencleary.com/) as he goes into it in much more detail) — , Jan 31 '17 at 05:59

score 1 · Answer 2 · answered Jan 30 '17 at 03:41

There are some possible errors in your code;

You don't appear to Start() your timer, that should probably go in AnimateProgBar.
AnimateProgBar expects milliseconds in it's argument, but you do this

timer1.Interval = milliSeconds / 100;

which is wrong because .Interval also expects millis.

To answer your question, you can just change btnInternalMovement_Click to

    private async void btnInternalMovement_Click(object sender, EventArgs e)
{

    AnimateProgBar(6500);


    // ***** BLAH MORE CODE HERE ******
await Task.Run(() => { 
    using (SqlCommand cmd = new SqlCommand("CatamacInternalStockMovement_C", SqlConnection))
    {

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ItemName", items);
        cmd.Parameters.AddWithValue("@Quantity", quantities);
        cmd.Parameters.AddWithValue("@WarehouseFrom", cbxFromWarehouse.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@WarehouseTo", cbxToWarehouse.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@LocationFrom", cbxFromZone.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@LocationTo", cbxToZone.SelectedValue.ToString());
        cmd.Parameters.AddWithValue("@UOM", "Each");
        cmd.Parameters.AddWithValue("@TransferDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@User", User);
        cmd.ExecuteNonQuery();

    }

    // PROGRESS BAR ONLY STARTS ANIMATING HERE
    MessageBox.Show("Success!");
});
}

Notice that that method is now marked async, and the long running stuff is inside Task.Run (which puts it in the background). You really don't want long running tasks happening in the UI thread.

Obviously there's a lot more to asynchronous programming than I'm putting in this answer, so please see https://msdn.microsoft.com/en-us/library/mt674882.aspx

`await Task.Run()` is incorrect use of TPL when call is _network bound_. Your code is assuming it is CPU-bound which is not the case. See [my answer](http://stackoverflow.com/a/41929362/585968) — , Jan 30 '17 at 05:15
@MickyD you're right that my answer is less optimal, I hastily gave an answer without considering the specifics of his task. — Jim W, Jan 30 '17 at 17:06

Animate Progress Bar while SQL Executes

2 Answers2

async/await with Network-bound Calls

See also