How to cancel a long query

[HJ Rodirguez]

Hello All,

I have an applications which can run some long queries. The queries
take a long time to execute because they are large rowsets with some
aggregations.

I'd like to provide user with the ability to cancel, a query which is
running.

My thought is to have a control with a progress-bar and a cancel
button which when pressed will stop the SQLCommand which is executing.

BTW - I'm storing my rowsets in a SQLDataReader.

Anyone have any good ideas how to best accomplish this.

Thanks in advance, for your help.

Regards
HJ

 

[Pablo Castro [MS]]

SqlCommand has a Cancel() method that will *attempt* to cancel the
operation. Note that this is very timing dependent, and also can behave
differently depending on what the server is doing when the cancelation is
sent. It should probably work fine in your case.

Note that if you want to cancel the command while it's running, the thread
that called the command will be blocked, so you'll need to call cancel from
a different thread (cancel in the only operation in SqlClient that's
thread-safe). Let me know if you'd like further details on this.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[HJ Rodriguez]

Thanks Pablo,

That was very helpfull.

SqlCommand has a Cancel() method that will *attempt* to cancel the
operation. Note that this is very timing dependent, and also can behave
differently depending on what the server is doing when the cancelation is
sent. It should probably work fine in your case.

Note that if you want to cancel the command while it's running, the thread
that called the command will be blocked, so you'll need to call cancel from
a different thread (cancel in the only operation in SqlClient that's
thread-safe). Let me know if you'd like further details on this.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Bill]

Hi Pablo -- I'd be interested in knowing how to do this. I'm running
into the same problem that HJ ran into and I'm stumped as to how to
gracefully cancel a long-running query (in VB.NET).

Many thanks in advance,

Bill