views:

237

answers:

0

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 RMR_Seek. 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 RMR_Seek from a BackgroundWorker DoWork event handler. This seemed to work OK and allowed me to update my message panel with some dummy status messages. However now that I have tried to implement this fully by calling RMR_GetLatestModelMessage 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();
            }
        }
    }