Cancel a running query

Anto BAro 86 Reputation points
2023-07-12T14:25:57.73+00:00

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");
        }
    }
}    	
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,270 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,947 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bruce (SqlWork.com) 52,576 Reputation points
    2023-07-12T21:14:31.9933333+00:00

    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));
                    }
                }
            }
        ```
    
    2 people found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. P a u l 10,306 Reputation points
    2023-07-12T20:33:39.6833333+00:00

    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;
    
    0 comments No comments