Long Running Stored Proc - Report Progress Using BackgroundWorker & Timer
- by daveywc
While a long running stored proc (RMR_Seek) is executing (called via a Linq-To-SQL data context) I am trying to call another stored proc (RMR_GetLatestModelMessage) to check a table for the latest status message. The long running stored proc updates the table in question with status messages as it executes. I want to display the status message on a message panel to advise the user of the status of the execution of Proc_A. For various reasons it is not possible to determine how long RMR_Seek will take to execute so a progress bar with percentage increments is not feasible.
I thought I'd found the way to do it by calling the long running stored proc from in a BackgroundWorker process DoWork event handler. This worked fine and allowed me to update my message panel with some dummy status messages that were NOT obtained via Proc_B while Proc_A was running. However now that I have tried to implement this fully by calling Proc_B to obtain the status messages I am running into problems that seem to be related to the mix of the backgroundworker and my System.Windows.Forms.Timer. An extract of the code I am using is below. I have tried many different ways around this but each one seems to present its own set of problems.
The code below is problematic in the bw_DoWork event. The RMR_Seek stored proc gets called but does not execute properly - it also seems to be inconsistent as to whether _IsCompleted gets set to true. I'm sure there is a better way to achieve what I am trying to do.
private bool _IsCompleted;
private void RunRevenueSeek()
{
if (_SelectedModel == null)
{
MessageBox.Show("Please select a model from the list and try again.", "Model Generation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
var bw = new BackgroundWorker();
bw.DoWork += new DoWorkEventHandler(bw_DoWork);
ProgressPanelControl.Visible = true;
_IsCompleted = false;
MessageTimer.Start(); // Has an interval of 3000
bw.RunWorkerAsync();
ProgressLabelControl.Text = "Refreshing Data";
this.Update();
...more code goes here
}
}
private void bw_DoWork(object sender, DoWorkEventArgs e)
{
using (var dc = new RevMdlrDataClassesDataContext())
{
dc.CommandTimeout = 300;
dc.RMR_Seek(_SelectedModel.ModelSet_ID);
_IsCompleted = true;
}
}
private void MessageTimer_Tick(object sender, EventArgs e)
{
string message = "";
if (_IsCompleted)
{
MessageTimer.Stop();
}
else
{
using (var dc = new RevMdlrDataClassesDataContext())
{
dc.CommandTimeout = 300;
dc.RMR_GetLatestModelMessage(_SelectedModel.ModelSet_ID, ref message);
ProgressLabelControl.Text = message;
this.Update();
}
}
}
