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 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.

    您可以中止该线程，但该查询将仍然运行，因为这是由SQL服务器托管的。

    链接地址: http://www.djcxy.com/p/71582.html

    上一篇: 疑难解答SQL存储过程

    下一篇: 中断执行存储过程