How to stop the execution of a SqlCommand?

Question

SQL

I am writing a winforms application on .net 4.0. There is a task to show a waiting dialog with a button to cancel the execution of the request while executing queries to the database.
Form code:

public partial class LoadingFrm : Form
{
     //task for execute query
     private Task execute;
     //action for cancelling task
     private Action cancel;

     public LoadingFrm(Task e, Action c)
     {
         execute = e;
         cancel = c;
         InitializeComponent();
         this.cancelBtn.Click += this.cancelBtn_Click;
         this.FormBorderStyle = FormBorderStyle.None;
         this.Load += (s, ea) =>
         {
             //start task
             this.execute.Start();
             //close form after execution of task
             this.execute.ContinueWith((t) =>
             {
                 if (this.InvokeRequired)
                 {
                     Invoke((MethodInvoker)this.Close);
                 }
                 else this.Close();
             });
         };
      }

      //event handler of cancel button
      private void cancelBtn_Click(object sender, EventArgs e)
      {
          cancel();
      }
}

Code of the class with which SqlCommands are executed:

public class ExecuteHelper
{
      public static SqlDataReader Execute(SqlCommand command)
      {
          var cts = new CancellationTokenSource();
          var cToken = cts.Token;
          cToken.Register(() => { command.Cancel(); });
          Task<SqlDataReader> executeQuery = new Task<SqlDataReader>(command.ExecuteReader, cToken);
          //create a form with execute task and action for cancel task if user click on btn
          LoadingFrm _lFrm = new LoadingFrm(executeQuery, () => { cts.Cancel(); });
          _lFrm.ShowDialog();
          SqlDataReader r = null;
          try
          {
              r = executeQuery.Result;
          }
          catch (AggregateException ae)
          {
          }
          catch (Exception ex)
          {
          }

          return r;
      }
}

By clicking on the cancel button, I call the method, cts.Cancel();which in turn calls the method command.Cancel();. The problem is that the request is still not interrupted. After a while, the data still comes from the sql server.
Question: how to stop the execution SqlCommand.ExecuteQuery()
PS: Maybe you solved similar problems in a different way, share your experience. The goal is to show a dialog box (allowing you to cancel the execution of the query) while the query is being executed. Might be better done with help BeginExecuteReader();and EndExecuteReader();
Stackoverflow

Answer 1

Cancel() does not always cancel the request. it's only trying to undo. tell me how it will cancel something if the request has already been transferred to the subd and it is already fulfilling it?
yes, why cancel it? if it is a request for a selection, it remains only to ignore what has come. if a request to change data, then transactions will help you. open a transaction and, upon completion of work, commit this transaction. if the user clicked cancel, then you roll back the transaction and everything that you have changed in the aisles of this transaction will not be saved in the database.

Answer 2

Cancel tries to cancel the request and tries to pass this on to SQLServer. If the request is long and consists of separate steps, and these steps have not yet begun to be executed, then the command will be interrupted. If there is an issuance, then it can also be interrupted.
You don't have to keep reading if you're interrupted - interrupt, don't call Read() again, close the reader and notify the user. When using BeginExecuteReader, you can organize the process of waiting and interrupting more easily, but the process of receiving data is a little more complicated.
In one project, I acted a little differently. I quickly got the identifiers of objects relevant to the query, and then calmly read lines with data in a separate thread (many slow columns). This process was controlled by me and could be interrupted at any moment. As a bonus, you get the correct progress bar.

Answer 3

I think you should read in the direction of multithreading (threading) and asynchronous (async / await) programming.