Cancel a running query

Question

Dear all,

in my project I have 2 Buttons.

If I click START_QUERY_Click a query is executed, but if it exceeds 20 seconds (SecDelete = 20000) it is canceled.

Now I would like to create a second button, STOP_QUERY_Click where if the user clicks on this the query is immediately canceled.

Unfortunately it is impossible to click on STOP_QUERY_Click as the execution of the query blocks the form.

So I think I need to create a second task, but I can't find a solution. Is there anyone who can give me a hint please? Thank you

This is my code

int SecDelete = 20000;
private void STOP_QUERY_Click(object sender, EventArgs e)
{
    SecDelete = 100;
}
private void START_QUERY_Click(object sender, EventArgs e)
{
    using(SqlConnection cn = new SqlConnection(connectionString))
    {
        cn.Open();
        // Execute the command
        using(SqlCommand cmd = new SqlCommand(commandText, cn))
        {
            CancellationTokenSource cts = new CancellationTokenSource();
            // Cancel the command if it doesn't complete in 20 seconds
            cts.CancelAfter(SecDelete);
            Task < Int32 > task;
            task = cmd.ExecuteNonQueryAsync(cts.Token);
            try
            {
                int activityCount = task.Result;
                MessageBox.Show(activityCount.ToString());
            }
            catch(AggregateException ae)
            {
                // handle task execution errors
            }
            MessageBox.Show("End");
        }
    }
}    	

Answer 1

to cancel query you need run the query as a task. then cancel via the token

        int SecDelete = 20000;
		CancellationTokenSource cts = new CancellationTokenSource();

		private void STOP_QUERY_Click(object sender, EventArgs e)
        {
            CancelQuery();
        }
        private void START_QUERY_Click(object sender, EventArgs e)
        {
            DoQuery();
        }
        private voicemail CancelQuery()
        {
            cts.Cancel();
            cts = new CancellationTokenSource();
        }
        private async Task DoQuery()
        {
             var watchTask = Task.Run(() => Thread.Sleep(SecDelete));
             using (SqlConnection cn = new SqlConnection(connectionString))
             {
                 cn.Open();

                 // Execute the command
                 using (SqlCommand cmd = new SqlCommand(commandText, cn))
                 {
                    try
                    {
                        var sqlTask = cmd.ExecuteNonQueryAsync(cts.Token);
                        await Task.WhenAny(sqlTask, watchTask);
                        if (!sqlTask.IsCompleted)
                        {
                           CancelQuery(); // kill query 
                           throw new Exception("killed query");
                        }
                        var activityCount = sqlTask.Result;
                        // run on ui thread - check docs for calling dispatcher
                        Invoke(() => MessageBox.Show(activityCount.ToString());
                     }
                     catch (Exception ex)
                     {
                     }

                     Invoke(() =>  MessageBox.Show(message));
                }
            }
        }
    ```

Answer 2

This line will be blocking your UI thread causing controls to be non-responsive until the task returns, because accessing .Result synchronously blocks the calling thread:

int activityCount = task.Result;

Instead try declaring your start handler as async:

private async void START_QUERY_Click(object sender, EventArgs e)

And awaiting the task instead:

int activityCount = await task;