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).
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();
reader.ReadAsync() in my case. how do I implement CreateDataTable() when I want the metadata to be based on the query results? Thanks!
DataTable dt = reader.GetSchemaTable(); where reader is the SqlDataReader.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
}
