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;
    }
}