title: Cancelling query using SqlCommand.Cancel
body: <p>I am trying to create a way to cancel a query to a SQL Server database (using ADO.net) by using <code>SqlCommand.Cancel</code>. As explained in other articles on <code>SqlCommand.Cancel</code>, I run a thread to govern the cancellation of the <code>SqlCommand</code>. Whenever I want to trigger the cancellation, I will set the <code>cancelQuery</code> variable to true, but whenever I trigger the query cancelling, nothing is happening (no exception thrown either) and the query continue.</p>
  
  <p>Is there anything that I did wrong here? </p>
  
  <pre><code>    public void FillDataSet(string statement, string name, DataSet d)
      {
          trackFillThread = new Thread(() =&gt; CancelQuery());
          trackFillThread.Start();
          trackFillThread.IsBackground = true;
  
          LoadData(statement, name, d);            
      }
  
      void LoadData(string statement, string name, DataSet d)
      {
          connectionString = string.Format("data source={0};initial catalog={1};uid={2};pwd={3};Connection Timeout=80", dataSource, catalog, uid, pwd);
  
          DataTable dt = new DataTable();
          dt.TableName = name;
          Debug.Log(name);
  
          using (SqlConnection oConn = new SqlConnection(connectionString))
          {
              oConn.Open();
              cmd = new SqlCommand(statement, oConn);
              cmd.CommandTimeout = 120;
  
              try
              {
                  Debug.Log("Start Query");
                  System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
                  dt.Load(dr);
                  d.Tables.Add(dt);
                  //cancelQuery = true;
              }
              catch //(System.Exception e)
              {
                  Debug.Log("Load Data Exception");
                  return;
              }
          } 
      }
  
      void CancelQuery()
      {
          while (true)
          {
              if (cancelQuery)
              {
                  cancelQuery = false;
  
                  if (cmd != null)
                  {
                      Debug.Log("Cancel");
                      cmd.Cancel();
                  }
  
                  break;
              }
          }
  
          System.Data.SqlClient.SqlConnection.ClearAllPools();
          Debug.Log("CancelQuery Thread Ends");
      }
  </code></pre>