CODEWITHSUNDEEP
c#sqlmultithreadingdatatable
CoderBrien
CoderBrien

Reputation: 693

C#: Populate DataTable from SqlDataReader with the ability to interrupt?

I would like to fill a DataTable object via a SqlDataReader and have the ability to interrupt the operation (eg via the u/i in the case a user wants to cancel a long running query).

Unfortunately, I don't see any overload of DataTable.Load() that takes a CancellationToken.

Is there a clean way to achieve this?

(My only thought is use a thread and see if Thread.Interrupt() does the trick and if not then Thread.Abort() but that seems pretty unfriendly).

Upvotes: 1

Views: 829

Answers (2)

Kinetic
Kinetic

Reputation: 2650

You could use a Task and a cancellation token. Of course, you will have to fill the DataTable manually instead of using the Load method. 

private void FillTable(CancellationToken token)
{
    var reader = new SqlDataReader();
    var dt = CreateDataTable();
    while(reader.Read() && !token.IsCancellationRequested)
    {
        var row = dt.NewRow();
        // fill row from reader....
        dt.Rows.Add(row);
    }
}

You would use this method like this : 

CancellationTokenSource tokenSource = new CancellationTokenSource();
Task.Factory.StartNew(() => FillTable(tokenSource.Token), tokenSource.Token);

Then you can cancel the operation :

tokenSource.Cancel();

Upvotes: 2

William Xifaras
William Xifaras

Reputation: 5312

Coupled with a SqlDataAdapter, you can handle the DataTable RowChanged event and either throw an exception or close the data adapter's connection which causes an InvalidOperationException to be thrown.

Take a look at the following example (source):

private BackgroundWorker worker;
private DataTable table;

private void button2_Click(object sender, EventArgs e)
{
  if (worker != null)
  {
    worker.CancelAsync();
  }
}

private void button1_Click(object sender, EventArgs e)
{  
  this.worker = new BackgroundWorker();
  worker.WorkerReportsProgress = true;
  worker.WorkerSupportsCancellation = true;

  worker.DoWork += new DoWorkEventHandler(worker_DoWork);
  worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);

  worker.RunWorkerAsync();
}

void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
  MessageBox.Show(this.table.Rows.Count.ToString());
}

[System.Diagnostics.DebuggerStepThrough]
void worker_DoWork(object sender, DoWorkEventArgs e)
{
  this.table = new DataTable();

  using (SqlConnection connection= new SqlConnection())
  using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM table", connection))
  {
    table.RowChanged += new DataRowChangeEventHandler(table_RowChanged); 
    da.Fill(table);        
  }
}

[System.Diagnostics.DebuggerStepThrough]
void table_RowChanged(object sender, DataRowChangeEventArgs e)
{
  if (worker.CancellationPending)
  {
    throw new ApplicationException("Canceled"); // throw a spanner in the works
  }
  Thread.Sleep(5); // Just slow things down for testing
}

Upvotes: 0

Related Questions