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.

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

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

Solution 3

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

Solution 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.

---

View more solutions

Comments

[SuperPrograman]

Is there a way to use the ReportProgress and ProgressChanged methods of BackgroundWorker to increment a regular ProgressBar?

[jaquesri]

Yes, if your long running task has places where it would make sense to report progress, you can call ReportProgress on the worker thread and ProgressChanged events will fire on the UI thread. I think you may need to explicitly enable that. Read the docs on Background Worker can show you how to do it: msdn.microsoft.com/en-us/library/…

[Panagiotis Kanavos]

Up to .NET 3.5 this may have been an acceptable but not great solution. .NET 4 allows you to do the same in a much simpler fashion. You could have done the same thing in just 6 lines. BTW the way you call the procedure is not just wrong but dangerous. Just create a DbCommand with a type of StoredProcedure and pass the parameters.

[Panagiotis Kanavos]

There's no reason to use BackgroundWorker when you can use Tasks. It doesn't offer anything over the combination of Task.Run/IProgress<> while it requires much more code, is heavier adn can't provide any progress beyond a simple number

[Amit Bisht]

How Could i Show status of stored procedure while execution of code.. because UI got stuck when procedure executes

[Erdogan Kurtur]

UI got stuck because you used BackgroundWorker wrong. After you have called RunWorkerAsync, you still run a code until worker completes. You should create another timer to update progress bar, and use RunWorkerCompleted to track completion.

[Amit Bisht]

As i mentioned in my question i am not using background worker so without using it Ui got stuck

[jaquesri]

Thanks @PanagiotisKanavos I hadn't noticed the new IProgress<> interface, that looks much cleaner than background worker.