Re: Need to cancel BackgroundWorker - C# Discussion Boards

1-Sep-16 13:01

Hello all. I have a background worker that executes a query against an SQL Server database. The user clicks a button and the query runs, then the results of the query go into a datagrid. The users are requesting a Cancel button for the query because the queries can take a very long time to run. So when the user clicks the Run Query button, a small form with a Cancel Button opens. If the user clicks the button, DialogueResult.Cancel is returned and I execute CancelAsync().

Meanwhile, in my DoWork thread, I am preparing to run the query. When I run the query, I execute

adapter3.Fill(GlobalData.g_queryData);

So, there is no loop. Most of the forums I have seen tell you to check Backgroundworker.CancellationPending. The problem is that I am not running in a loop.

So, can someone suggest maybe another approach to canceling a query that is currently running?

Thanks,
David

using (SqlConnection connection = new SqlConnection(GlobalStr.sqlConnStringMetadata))
                {
                    try
                    {
                        connection.Open();

                        /*
                         * DRW 3/10/2016
                         * Added the following Transaction object in order to set the isolation level of all queries
                         * run from MAnDA to to Read Uncommited.
                         */
                        SqlTransaction Transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);

                        using (SqlCommand testQuery = new SqlCommand(GlobalStr.previewQueryString, connection))
                        {
                            /*
                             * DRW 3/11/2016
                             * User sets the query timeout value via a textbox on the main form. The value is held in a variable.
                             * testQuery.CommandTimeout = 300;
                             */

                            testQuery.CommandTimeout = m_QueryTimeout;
                            testQuery.Transaction = Transaction;                           

                            using (SqlDataAdapter adapter3 = new SqlDataAdapter(testQuery))
                            {
                                adapter3.Fill(GlobalData.g_queryData);

                                // Open the DataSetViewer form and send the table to it
                                DataSetViewer dsv = new DataSetViewer();
                                dsv.SetSource(GlobalData.g_queryData);
                                // set the dataview classification 05272015
                                string strClassification = string.Format("{0} {1}", GlobalStr.g_classification, GlobalStr.g_releaseability);
                                dsv.setClassification(strClassification);
                                Cursor.Current = Cursors.Default;
                                dsv.ShowDialog();

                                //dataGridView1.DataSource = GlobalData.g_queryData;
                            }
                        }
                        connection.Close();
                    }
                    catch (Exception ex)
                    {
                        /// Print error message
                        MessageBox.Show("The query test failed: " + ex.Message);
                        return;
                    }
                }

Pete O'Hanlon1-Sep-16 21:01

Pete O'Hanlon

1-Sep-16 21:01

I have accomplished a similar task in the past using a variation of this code:

public sealed class ExtendedBackgroundWorker : BackgroundWorker
{
  private Thread thread;
  public void Stop()
  {
    thread?.Abort(); // Abort the thread if it has been set.
	thread = null;
  }
  protected override void OnDoWork(DoWorkEventArgs eventArgs)
  {
    thread = Thread.CurrentThread;
	try
	{
	  base.OnDoWork(eventArgs);
	}
	catch (ThreadAbortException threadAbort)
	{
	  Debug.WriteLine("ExtendedBackgroundWorker has been aborted");
	  Thread.ResetAbort();
	  eventArgs.Cancel = true;
	}
  }
}

With this, you're going to Abort the Thread. Now, an Abort is normally a bad method to call (there are various resources you can look up about why it's not recommended), but we're going to handle it in a controlled manner. When the thread is aborted, a ThreadAbortException is raised so, in our OnDoWork handler, we want to Cancel the DoWorkEventArgs and reset the Abort so that it doesn't destabilise our system. Now, you have a background worker that can be killed from the calling code. By the way, I call the method Stop rather than Abort because while this code is carefully written to prevent Abort issues, I don't want people to get into the habit of calling Abort wherever they see thread codes. By hiding the implementation, I'm hiding the idea that Abort should be called.

This space for rent

David_416-Sep-16 8:12

6-Sep-16 8:12

Thank you Peter. I actually worked on this over my long weekend. Sorry for not getting back to you sooner.

I got it working by partly using your suggestion to abort the thread. This works because the only types of queries that will be run here are SELECT queries. So no records are actually being modified. So, no need to worry about rolling back transactions.

I am no longer using a background worker. I am just creating my own thread. I want to show my code below.

Class Level Code:

CloseCancelForm(); //This form contains the Cancel Button

Cancel Form

public partial class FormCancelQuery : Form
    {
        DialogResult result = DialogResult.OK;
        public FormCancelQuery()
        {
            InitializeComponent();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            result = DialogResult.Cancel;
            this.Close();
        }
    }

Main UI Form

private void qbTestQueryBtn_Click(object sender, EventArgs e)
        {            
            frmCancel = new FormCancelQuery();
            RunQueryThread = new Thread(new ThreadStart(RunQuery));
            RunQueryThread.Start();
            DialogResult dr = frmCancel.ShowDialog(this);
            if (dr == DialogResult.Cancel)
            {
                frmCancel.DialogResult = DialogResult.OK;
                RunQueryThread.Abort();
            }                       
        }

private void RunQuery()
        {
            if (ValidQuery())
            {
                string queryTestString = GlobalStr.previewQueryString;
                // Check the query for the where fill flag, and if it contains it, branch off to the filling routine
                string fillFlag = "'mandafillflag'";
                if (queryTestString.ToLower().Contains(fillFlag))
                {
                    MessageBox.Show("This query requires completing the fields. Click OK to begin procedure.");
                    QueryFiller qf = new QueryFiller();
                    queryTestString = qf.FillQuery(queryTestString.ToLower(), fillFlag);
                }

                GlobalStr.previewQueryString = queryTestString;
                //selectTB.Text = GlobalStr.previewQueryString;
                selectTB.Invoke(new MethodInvoker(delegate { selectTB.Text = GlobalStr.previewQueryString; }));
                //whereTB.Clear();
                whereTB.Invoke(new MethodInvoker(delegate { whereTB.Clear(); }));
                //fromTB.Clear();
                fromTB.Invoke(new MethodInvoker(delegate { fromTB.Clear(); }));
                //orderTB.Clear();
                orderTB.Invoke(new MethodInvoker(delegate { orderTB.Clear(); }));

                try
                {
                    //examineQueryInputString(GlobalStr.previewQueryString, "", cmplxWhereClauseCB.Checked);
                    examineQueryInputString(GlobalStr.previewQueryString, "", GlobalBool.g_CmplxWhereClauseChecked);
                }
                catch (System.Exception ex)
                {
                    CloseCancelForm();
                    MessageBox.Show(ex.Message);
                    return;
                }

                // Clear the data table
                
                try
                {
                    GlobalData.g_queryData.Rows.Clear();
                    GlobalData.g_queryData.Columns.Clear();
                }
                catch (Exception ex)
                {
                    GlobalData.g_queryData = new DataTable();                    
                }                
                
                // set the wait cursor if query takes long. ens 01/28/2015
                Cursor.Current = Cursors.WaitCursor;
                //using (SqlConnection connection = new SqlConnection(GlobalStr.sqlConnString))
                using (SqlConnection connection = new SqlConnection(GlobalStr.sqlConnStringMetadata))
                {
                    try
                    {
                        connection.Open();

                        /*
                         * DRW 3/10/2016
                         * Added the following Transaction object in order to set the isolation level of all queries
                         * run from MAnDA to to Read Uncommited.
                         */
                        SqlTransaction Transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);

                        using (SqlCommand testQuery = new SqlCommand(GlobalStr.previewQueryString, connection))
                        {
                            /*
                             * DRW 3/11/2016
                             * User sets the query timeout value via a textbox on the main form. The value is held in a variable.
                             * testQuery.CommandTimeout = 300;
                             */

                            testQuery.CommandTimeout = m_QueryTimeout;
                            testQuery.Transaction = Transaction;                           

                            using (SqlDataAdapter adapter3 = new SqlDataAdapter(testQuery))
                            {
                                adapter3.Fill(GlobalData.g_queryData);

                                // Open the DataSetViewer form and send the table to it
                                DataSetViewer dsv = new DataSetViewer();
                                dsv.SetSource(GlobalData.g_queryData);
                                // set the dataview classification 05272015
                                string strClassification = string.Format("{0} {1}", GlobalStr.g_classification, GlobalStr.g_releaseability);
                                dsv.setClassification(strClassification);
                                Cursor.Current = Cursors.Default;
                                CloseCancelForm();
                                dsv.ShowDialog();

                                //dataGridView1.DataSource = GlobalData.g_queryData;
                            }
                        }
                        connection.Close();                        
                    }
                    catch (Exception ex)
                    {
                        //frmCancel.Invoke(new MethodInvoker(delegate { frmCancel.Close(); }));                        
                        if (ex.Message != "Thread was being aborted.")
                        {
                            CloseCancelForm();
                            /// Print error message
                            MessageBox.Show("The query test failed: " + ex.Message);                            
                        }
                        return;
                    }
                }
            }
            else
            {
                CloseCancelForm();
                MessageBox.Show("SQL test failed.");
            } 
        }

        private void CloseCancelForm()
        {            
            try
            {
                if (frmCancel.InvokeRequired)
                {
                    frmCancel.Invoke(new MethodInvoker(delegate { frmCancel.DialogResult = DialogResult.OK; }));
                    frmCancel.Invoke(new MethodInvoker(delegate { frmCancel.Close(); }));
                }
                else
                {
                    frmCancel.DialogResult = DialogResult.OK;
                    frmCancel.Close();
                }                
            }
            catch(Exception ex)
            {
                string message = ex.Message;
                return;
            }
        }

Hopefully this will help someone.

David

Pete O'Hanlon6-Sep-16 9:39

Pete O'Hanlon

6-Sep-16 9:39

Thanks for coming back and posting your update. Glad you got it working.

This space for rent

Gerry Schmitz3-Sep-16 6:53

3-Sep-16 6:53

Yes; the usual reaction is to figure out how to "cancel the query".

Perhaps one should first look at "tuning" the query.

The "query" is being confused with the "what it is that is submitting the query" (existentially speaking). The "query" is the "database server engine"; not some thread that submitted the query.

Is the query returning a scalar or a "set"?

If a set, is it required to be viewed and paged? Reported on?

Is virtualization an option for "paged" results.

Can the execution plan be estimated and presented before kicking off the query?

What is the nature of query? Would a data reader suffice? Can intermediate results be used to confirm kicking of further queries instead of resorting to a single "large" (poorly optimized) query (e.g. when "drilling" down).

etc....

David_416-Sep-16 8:20

6-Sep-16 8:20

Hi Gerry,

These are user created queries, so there is no tuning the query.

Yes, I guess you are correct. The query is being run by the database server engine, so I guess there is no canceling the query. All I can really do is force my program to stop waiting for the query results. I guess since the query is running in a transaction, I could try rolling back the transaction.

The query returns a set.

The query is created by the user through a query designer that we wrote in C#. It only allows the user to execute SELECT queries against tables or Views in the database. It returns a dataset with the results displayed in a datagrid. The results can then be exported to a CSV or XLSX file.

Thanks or your input.

Gerry Schmitz6-Sep-16 9:08

6-Sep-16 9:08

The queries may be "user-created", but that does not mean they should have "free access" to the database.

I would give users "unlimited" query abilities; but with some "reasonableness" applied.

With an ERP system, it makes little sense to run queries on "all".

Based on the context (User; Department; Title; etc.), users may be required to provide a "year", "month", "department code", etc. via a query interface.

For "historical" data analysis, we would build data warehouses ("facts"; dimensions; "star" relationships) that were "optimized" for ad-hoc queries.

One simply does not unleash users on "unstructured" data without some planning (and expect performance).

David_416-Sep-16 9:14

6-Sep-16 9:14

Hi Gerry. They do not have free unlimited access to everything. We use User Roles to control what they have access to. They cannot run Action Queries or DROP or CREATE queries. If the application needs to do that, then we use an Application Role with elevated permissions. We have covered our bases I think.

Thanks,

David

David_416-Sep-16 9:18

6-Sep-16 9:18

Also, just to be safe, we have an algorithm that validates their query and looks for those action query keywords such as DROP, DELETE, INSERT, CREATE, UPDATE, etc. It will not allow anyone to run queries with those keywords. We also have the user pick the tables that they want to run their query against from a combo box. The algorithm that loads the combo box only lists certain tables.

David

Gerry Schmitz6-Sep-16 9:48

6-Sep-16 9:48

When I said "free access", I did not mean: DELETE; DROP; etc.

I meant that a person who works in "Accounts Receivable" (AR) typically only deals with "AR" data that is current for the month, and a few previous; and NOT to xx years of data for every department in the company.

That's why one has "performance problems": users (or anybody) running "dumb" queries.

And if you need to "cancel it", it cannot have been that important.

If it WAS important, one typically creates a "canned query"; with a few "optional parameters".

Anything else is chaos; and you get blamed for the performance issues.

David_416-Sep-16 10:05

6-Sep-16 10:05

Ok Gerry, I apologize. I misunderstood you.

In our environment, our SQL Server is on a non-Internet connected machine. And the data is Analysis data. More and more data is added on a regular basis. We have many millions of rows of data in several databases. Our data analysts run queries. Sometimes they create a query incorrectly (or inefficient) . We have had cases where a user will start a query before they go home for the day, and then when they come in the next morning, it is still running.

Some of our queries consist of many views joined together and some of them use scalar functions and table valued functions which get used repeatedly. This can of course slow the query down significantly. Anyway, previously the only way to cancel a query was to End Task on the program, now the user can just click the cancel button. It works very well.

Thanks,

David

Gerry Schmitz6-Sep-16 10:25