Cancelling a database query dillema

Perhaps this is a question that's been asked many times but after a lot of searching and reading I'm still not quite sure what is the best approach.

Problem is simple: Some database transactions in my application are long (seconds, minutes). This can be result of two things: Single query is asked to process a lot of data or multiple queries are executed on a series of retrieved data. In many cases both things are in play. This hangs users interface and worse still he's unable to cancel this.

The solution also seems simple: Move those transactions into another thread and then destroy this thread if needed.

However a lot of folks here on t开发者_StackOverflow社区he internet argues against killing threads. They suggest using DbCommand.Cancel() which is a thread safe operation. But, other folks say that there is no guarantee that that command will cancel the query. Even bigger problem is presented: how can UI thread know which DbCommand is executing when the user clicks? Heck, thread could processing data in memory at that moment.

Can you shed any light on this subject?

---

there is no guarantee that that command will cancel the query

It depends on the provider. Some providers don't support cancelling commands, but SQL Server does, so it shouldn't be an issue. Of course it also depend on the kind of command being executed...

Even bigger problem is presented: how can UI thread know which DbCommand is executing when the user clicks?

DbCommand.Cancel is an instance method, not a static method... so you need to keep a reference to the command being executed, and call cancel on this instance.