Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
335 views
in Technique[技术] by (71.8m points)

c# - Can sql server queries be really cancelled/killed?

I would like to give a user the ability to cancel a running query. The query is really slow. (Query optimization is besides the point.) This is mainly out of my curiosity.

MSDN says:

If there is nothing to cancel, nothing occurs. However, if there is a command in process, and the attempt to cancel fails, no exception is generated.

  • Cmd - SqlCommand
  • DA - DataAdapter
  • Conn - SqlConnection
  • CurrentSearch - Thread
  • LongQuery - Singleton

Here's what I have:

var t = new Thread(AbortThread);
t.Start();

void AbortThread()
{
    LongQuery.Current.Cmd.Cancel();
    LongQuery.Current.Cmd.Dispose();
    LongQuery.Current.DA.Dispose();
    LongQuery.Current.Conn.Close();
    LongQuery.Current.Conn.Dispose();
    LongQuery.Current.Cmd = null;
    LongQuery.Current.DA = null;
    LongQuery.Current.Conn = null;
    CurrentSearch.Abort();
    CurrentSearch.Join();
    CurrentSearch = null;
}

I noticed that CurrentSearch.Abort() was blocking, that's why I wrapped it in a thread, which probably means that the thread is still working.

Finally, is there anything else than this that I can do to cancel a query? Is it actually possible to cancel such a long query from .NET?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

IF you really absolutely want to kill it for good use this approach:

When you want to kill it:

  • Open a new DB connection
  • issue a KILL command for that session ID
    BEWARE as the MSDN documentation states you need the permission ALTER ANY CONNECTION to do this

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...