Linq - interrupt execution of stored procedure

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 开发者_JAVA技巧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 ?

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

You could try abandoning the process server side but it requires HIGH privilesdge (server admin), so itis not feasbile under most coircusmtances.

Thread abortion could work (not recommended), but I am not sure the db would stop processing.

At the end I am not sure ther is any way to do that efficiently.

You can abort the thread but the query will still run as this is hosted by SQL server.