Linq - interrupt execution of stored procedure

Question

I am working on a C# application that calls stored procedures from a SQL server. Depending on the search parameters the user inputs, the stored procedure can take a while and return many records.

I want to implement a cancel button so the user can cancel the execution of the stored procedure when it takes to long. I run the call to the stored procedure in a new thread so the GUI doesn't get affected when the stored procedure takes a while. I read about aborting a thread but found many rejecting opinions about using abort.

Possible solutions i think of:

• Is there a common manner to stop executing the stored procedure (which is a method in the c# code)
• Is there a better way to stop the thread ? (I found also Thread.Interrupt() but this only works on a blocked thread, not on running code)
• I can abandon the thread and start a new one but then unnecessary database and network resources are used
• Is there a way to stop the stored procedure from the server side ?

Answer 1

You might not want to use Linq here. Try this:

Stop SQL query execution from .net Code

Basically use SqlCommand.Cancel()

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.cancel.aspx