Search code examples
multithreadingado.netbackgroundworkersqlclient

Performing a series of SQL queries, and need to be able to cancel them from the main UI thread. What is the best approach?

Currently, I have several queries that need to run in order (some create temp tables which I draw from in later queries).

The queries run in a BackgroundWorker thread, and currently if there is a CancellationPending, I break out of the method in between query calls.

However this approach is problematic as there are times when a single query can run for a long time or never returns, so I need to be able to run each query while polling for BackgroundWorker.CancellationPending.

What is the best asynchronous pattern to use in this case? I need to fill a DataTable for each query, but still have the ability to cancel if it takes too long.

Solution

  • In all cases, abort cooperatively. Don't even think about using Thread.Abort.

    The great Stephen Toub has written a best-practice.

    TL;DR: When a timeout timer fires you complete the Task immediately and let the SQL query continue to run. You just ignore it.