how to use background worker class for stored procedure execution in c#

Question

I have the below code in C# where i'm trying to use the background worker class to display the progress of execution of a stored procedure.

Or(any input how to display the message of progress while stored procedure execution via c# is much appreciated)

Problem is when i click the button there is no progress of execution in the code dont know where the issue occurs. Let me know if anyone has some ideas.

Thanks

//Butoon click code
 private void button_executeBL_Click(object sender, EventArgs e)
    {
        if (!backgroundWorker1.IsBusy)
        {
            backgroundWorker1.RunWorkerAsync();
        }

        else
        {
            label1.Text = "Busy Processing, Please wait";
        }

    }

    //background worker class process...

   private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
    {
        Thread.Sleep(200);
        int count =1;
        string connectionString = "Data Source=.;Initial     Catalog=VegetablesCoSD;Integrated Security=True";
        string commandText = "CoSD.BusinessLogic";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand(commandText, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 600;
            try
            {
                conn.Open();

                //count will be the number of rows updated. will be zero if no rows updated.
                 backgroundWorker1.ReportProgress( count = cmd.ExecuteNonQuery());

                if (backgroundWorker1.CancellationPending)
                {
                    e.Cancel = true;
                    backgroundWorker1.ReportProgress(0);
                    return;
                }

                e.Result = count;
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Update Failed coz.. " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }


    }

    private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
    {
        progressBar1.Value = e.ProgressPercentage;
        label1.Text = e.ProgressPercentage.ToString();

    }

    private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        if (e.Cancelled)
        {
            label1.Text = "Process Cancelled";
        }
        else if (e.Error != null)
        {
            label1.Text = e.Error.Message;
        }

        else
        {
            MessageBox.Show("Business Rules Executed Successfully!!!");
            label1.Text = "Total Records Inserted" + e.Result.ToString();
        }

    }

    private void button_Cancel(object sender, EventArgs e)
    {
        if (backgroundWorker1.IsBusy)
        {
            backgroundWorker1.CancelAsync();
        }

        else
        {
            label1.Text = " No Operation in progress to cancel";
        }


    }

ExecuteNonQuery() blocks until the stored procedure is done, you wont get back any progress from your SP. Maybe you can use [this](http://stackoverflow.com/questions/10132847/how-to-see-progress-of-running-sql-stored-procedures) to get your SP to report progress so you can track it in c# — BoeseB, Feb 19 '16 at 07:43
after some more research RAISEERROR with a level over 10 will create an SqlException, so maybe it isn't a good idea. — BoeseB, Feb 19 '16 at 07:53
yes it seems to be... Is there any other way to get through this process?.. now the querey is executing and the end result message is getting displayed.. but the progress bar is of no action.... — Gowtham Ramamoorthy, Feb 19 '16 at 08:01
It seems to be possible. [Here a full solution with RAISEERROR](http://stackoverflow.com/a/20100277/4369295) — BoeseB, Feb 19 '16 at 08:25

score 0 · Answer 1 · answered Feb 19 '16 at 07:51

0

Since you want to run an async task you should change your button click method to async void so you can await your backgroundWorker1.RunWorkerAsync();

Async methods not being awaited will always run synchronously. Always await async methods to run them asynchronously.

private async void button_executeBL_Click(object sender, EventArgs e)
{
    if (!backgroundWorker1.IsBusy)
    {
        await backgroundWorker1.RunWorkerAsync();
    }

    else
    {
        label1.Text = "Busy Processing, Please wait";
    }

}

answered Feb 19 '16 at 07:51

John Ephraim Tugado

757
7
15

Hi john tried this one.. yes it got worked but as mentioned in the above comments there is no progress shown while the SP is on execution... There has to be some method other than'execute non- querey' to get the progress of SP in c# – Gowtham Ramamoorthy Feb 19 '16 at 07:59

how to use background worker class for stored procedure execution in c#

1 Answers1