Display progress of execution through progress bar

Question

I have a silly problem but i am stuck. I am executing a stored procedure form my code procedure takes time so for this I am displaying a progress bar, which shows the progress of execution, but stored procedure executes and there is nothing to which I increment the value of progress bar.

This is my code

void btnYes_Click(object sender, EventArgs e)
{
   if (DialogResult.Yes == MessageBox.Show("Are you sure", "", MessageBoxButtons.YesNo))
    {
        try
        {
         dbDataEntities db = new dbDataEntities();
        string myquery = "DECLARE @return_value int EXEC    @return_value = [dbo].[ssspUpdateMarksOfStudent] SELECT 'Return Value' = @return_value";
         //progressbar1.Maximum = 5000; 
         //progressbar1.value = ?; // how could i increment it
         //
         db.Database.ExecuteSqlCommand("myquery");



        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

Also i try to do it with stopwatch by setting the current value which did not increment while execution of procedure

Stopwatch st = new Stopwatch();
st.Start();
progressbar1.Maximum = 5000; 
progressbar1.Value = Convert.ToInt16(st.Elapsed.Seconds);
//My stored procedure call 
st.Stop(); 

so is it can only be done by using background worker or is there any other way to do this?

I am new to programming so did not use background worker much so i am trying to find an alternative.

Please suggest. Thanks in advance.

Answer 1

One funny idea SqlConnection provides InfoMessage event which fires when server executes a PRINT command. You can print in some parts of your stored procedure and listen to this event.

-- do something
PRINT '10 PERCENT COMPLETED';
-- do another thing
PRINT '20 PERCENT COMPLETED';
...
PRINT '100 PERCENT COMPLETED';

other than that, use @hamlet-hakobyan 's solution. just show a infinite progress bar.

---

Update: Updated to include a full solution

First of all, I hate to give full answers. It prevents mind's ability to find a solution. Instead I like to nudge people into correct path, so they can walk the walk. But here it is anyway. Tested using VS2012, NET4, MSIL under W7x64SP1 and SQL2012.

My very time consuming SP. Used RaisError instead of Print to send messages immediately.

Create Procedure usp_LongProcess As Begin

    Declare @i Int;
    Declare @msg VarChar(50);
    Set @i = 0;
    while (@i < 100) Begin
        WaitFor Delay '00:00:02';

        Set @i = @i + 10;
        Set @msg = Convert(VarChar(10), @i) + ' PERCENT COMPLETE';
        RaisError(@msg, 1, 1) With NoWait
    End
End

And my form with

• a button (CallSpButton)
• a progress bar (progress)
• a label (statusLabel) and
• a background worker (SpCaller) with WorkerReportsProgress set true.

And at last the code that makes the call

private void CallSpButton_Click(object sender, EventArgs e)
{
    CallSpButton.Enabled = false;
    SpCaller.RunWorkerAsync();
}

private void SpCaller_DoWork(object sender, DoWorkEventArgs e)
{
    var self = (BackgroundWorker) sender;

    var cb = new SqlConnectionStringBuilder
    {
        DataSource = ".", 
        InitialCatalog = "Sandbox", 
        IntegratedSecurity = true
    };

    using (var cn = new SqlConnection(cb.ToString()))
    {
        cn.FireInfoMessageEventOnUserErrors = true;
        cn.Open();
        cn.InfoMessage += (o, args) => self.ReportProgress(0, args.Message);

        using (var cmd = cn.CreateCommand())
        {
            cmd.CommandText = "usp_LongProcess";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.ExecuteNonQuery();
        }
    }
}

private void SpCaller_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    CallSpButton.Enabled = true;
}

private void SpCaller_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
    var message = Convert.ToString(e.UserState);
    Debug.WriteLine(message);
    statusLabel.Text = message;

    if (message.EndsWith(" PERCENT COMPLETE"))
    {
        int percent;
        if (int.TryParse(message.Split(' ')[0], out percent))
            progress.Value = percent;
    }
}

Answer 2

It looks like you're running the stored procedure on the gui thread.

That means that your application is going to be unresponsive whilst the call is running as the message pump is blocked. That's probably a bad idea. I would recommend moving all long running calls to another thread.

Background Worker is a class designed to do this to allow you to report back how a long running task is getting on so you can update the UI, but as your task is only one item, there isn't much to report back, so it might not be the right choice for you.

Perhaps you want something of the form (guessing the syntax as I don't have a compiler to hand) and borrowing from Windows Forms ProgressBar: Easiest way to start/stop marquee?

progressbar1.Style = ProgressBarStyle.Marquee; //thanks Hamlet
progressbar1.MarqueeAnimationSpeed = 30;

//run the long running thing on a background thread.
var bgTask = Task.Factory.StartNew(() => db.Database.ExecuteSqlCommand("myquery"));

//when it's done, back on this thread, let me know and we'll turn the progress bar off
bgTask.ContinueWith(resultTask => {
    progressBar1.Style = ProgressBarStyle.Continuous;
    progressBar1.MarqueeAnimationSpeed = 0;
 }, TaskScheduler.FromCurrentSynchronizationContext());

Answer 3

Stored Procedures don't provide progress information. You can use ProgressBar in Marquee style setting ProgressBar.Style Property to ProgressBarStyle.Marquee

Answer 4

Please follow this method.

1. First calculate the time to execute the stored procedure. db.Database.ExecuteSqlCommand("myquery"); enter code here

2. Allocate a separate thread/(part of process) for the execution of this query. by using the background worker control in c# (in do_work Event)

3. similarly assign an separate process thread using a background worker for displaying the progress-bar status.(in Progress-Changed Event) just increment value by 10%. give some small sleep (based on the time to consume your query divide it into ten parts). On completion make Progressbar.value=100 (in Run-Worker-CompletedEvent)

Please call both thread at mean time in backgroundWorker1.RunWorkerAsync();

I think this solves your Problem.

---

Answer 5

I studied Background worker and i do it this way I put execution of my procedure on another thread and progress bar in GUI thread

 BackgroundWorker bg = new BackgroundWorker();
 Boolean stopwork = true;
 Private void btnYes_Click(object sender, EventArgs e)
        {
            if (DialogResult.Yes == MessageBox.Show(clsGlobalObjectRefrances.OMessageString.SureWant2UpdateMarks, "", MessageBoxButtons.YesNo))
            {
                try
                {

                    bg.DoWork += bg_DoWork;
                    bg.RunWorkerCompleted += bg_RunWorkerCompleted;
                    bg.RunWorkerAsync();
                    pgbUpdateMarks.Maximum = 60;
                    Stopwatch st = new Stopwatch();
                    st.Start();
                    while(stopwork)
                    {
                        pgbUpdateMarks.Value = st.Elapsed.Seconds;
                    }
                    pgbUpdateMarks.Value = 0;
                    MessageBox.Show("Executed sucessfully");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

Where DoWork is

 void bg_DoWork(object sender, DoWorkEventArgs e)
        {

        dbDataEntities db = new dbDataEntities();
        string myquery = "DECLARE @return_value int EXEC    @return_value = [dbo].[ssspUpdateMarksOfStudent] SELECT 'Return Value' = @return_value";
        db.Database.ExecuteSqlCommand("myquery");
        stopwork = false;
        }