1

I have a complex piece of SQL and it involves lot of calculations etc. I want to know whether it is possible to cancel the query that is issued to the SQL server?

Ex. There is a button named Search and user clicks on Search. I want to show a button named "Cancel" and that should cancel the query issued to the SQL server.

Is this possible?

Dyrandz Famador
  • 4,499
  • 5
  • 25
  • 40
Jack
  • 7,433
  • 22
  • 63
  • 107
  • 1
    Since you can do it in Management Studio, I strongly would believe that you also can do it on your own. See also [Asynchronous Operations](http://msdn.microsoft.com/en-us/library/zw97wx20.aspx) in MSDN. – Uwe Keim Dec 27 '12 at 10:08
  • Use a transaction and rollback? See here: http://stackoverflow.com/questions/3935900/how-to-commit-and-rollback-transaction-in-sql-server – Mamta D Dec 27 '12 at 10:08
  • may be duplicate of http://stackoverflow.com/questions/7837739/can-sql-server-queries-be-really-cancelled-killed – tschmit007 Dec 27 '12 at 10:09
  • 1
    @Jack Which part seems unclear to me? – Uwe Keim Dec 27 '12 at 10:10
  • @UweKeim: "Since you can do it in Management Studio, I strongly would believe that you also can do it on your own.". Please don't post blind comments without understanding the question. Further you gave a link to .Net 4.5 even though I have tagged my question as C# 4. – Jack Dec 27 '12 at 10:11
  • SqlCommand has a Cancel method: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.cancel.aspx – rene Dec 27 '12 at 10:11
  • 2
    The question 'Is this possible?' is perfectly answered by @UweKeim – rene Dec 27 '12 at 10:13
  • 2
    @rene: Answer should be in context. I could very well say shut down the machine..the query will be cancelled. That doesn't however answer this question and perhaps also should try to read the whole question rather than just reading the end line. – Jack Dec 27 '12 at 10:14
  • 2
    +1, and the answer is "Yes" 8-/ – iDevlop Dec 27 '12 at 10:20

1 Answers1

8

There are a lot of asynchron functions in ADO.NET, e.g. SqlCommand.BeginExecuteNonQuery.

You can call these functions in your application, store the result object and cancel it when the user clicks the "Cancel" button.

So in pseudo-code you can do these steps:

  1. User clicks the Search button.
  2. In the button's handler, open your DB connection and the like.
  3. Show your Cancel button.
  4. Call the ADO.NET async function of your choice.
  5. Store the IAsyncResult returned object of the function call (for detecting when the operation has finished to hide the cancel button again).
  6. In the cancel button's handler, call the Cancel method of SqlCommand.
Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
  • 3
    Thanks!! That really gave me a clue :) and is better than giving access of server details to my users :) – Jack Dec 27 '12 at 10:19