Possibly this can be a question which has been requested many occasions but after lots of searching and reading through I am still less than sure what's the ultimate way.

Issue is simple: Some database transactions during my application are lengthy (seconds, minutes). This is often consequence of a couple of things: Single totally requested to process lots of data or multiple queries are performed on a number of retrieved data. Oftentimes both situations are in play. This dangles customers interface and even worse he's not able to cancel this.

The answer also appears simple: Move individuals transactions into another thread after which destroy this thread as needed.

However a lot of people here on the web argues against killing threads. They suggest using DbCommand.Cancel() the industry thread safe operation. But, other people state that there's no guarantee that that command will cancel the query. A great deal larger issue is presented: just how can UI thread know which DbCommand is performing once the user clicks? Heck, thread could processing data in memory at that time.

Are you able to shed any light about this subject?

there's no guarantee that that command will cancel the query

It is dependent around the provider. Some companies don't support rescheduling instructions, but SQL Server does, therefore it should not be an problem. Obviously additionally, it rely on the type of command being performed...

A great deal larger issue is presented: just how can UI thread know which DbCommand is performing once the user clicks?

DbCommand.Cancel is definitely an instance method, not really a static method... so you have to have a mention of command being performed, and call cancel about this instance.