Linq - interruption of the stored procedure

Question

I am working on a C # application that calls stored procedures from an SQL server. Depending on the search parameters entered by the user, the stored procedure may take some time and return many records.

I want to implement a cancel button so that the user can cancel the execution of a stored procedure when it takes a long time. I am launching a stored procedure call in a new thread, so the GUI is not affected when the stored procedure takes some time. I read about thread interruption, but found a lot of rejecting opinions about using interruption.

Possible solutions that I think of:

• Is there a general way to stop the execution of a stored procedure (which is a method in C # code).
• Is there a better way to stop the flow? (I also found Thread.Interrupt (), but this only works on a blocked thread, not on running code)
• I can refuse a stream and start a new one, but then unnecessary databases and network resources are used.
• Is there any way to stop the stored procedure from the server side?

Answer 1

You might not want to use Linq here. Try the following:

Stop executing SQL query from .net code

Mostly used by SqlCommand.Cancel ()

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

Answer 2

You can try to refuse the process server side, but this requires HIGH privilesdge (server administrator), so it cannot be used in most cases.

Niche abortion might work (not recommended), but I'm not sure db will stop processing.

In the end, I'm not sure if this is the way to do this efficiently.

Answer 3

You can interrupt the stream, but the query will be executed as it is placed on the SQL server.