How to cancel a long-running Database operation?

You could have the background worker fire off the actual database call on a different thread, and then periodically check to see if either the database call has finished, or cancel has been pressed, at which point you could kill off the database thread. This wouldn't actually help the database load any (as your query has been sent and is still processing) but it does release your local resources related to it.

I also noticed command.Cancel() doesn't really abort the command. What worked for me is closing the connection (rollback transaction if you use one) when the user aborts. This will raise an exception in your background thread while the command is executing, so you have to catch it and check the CancellationPending property there and not rethrow the exception in that case...

// When aborting
worker.CancelAsync();
command.Connection.Close();

// In your DoWork event handler
...
catch (Exception)
{
    if (worker.CancellationPending)
    {
        e.Cancel = true;
        return;
    }
    else
    {
        throw;
    }
}

// And in your RunWorkerCompleted event handler
if (e.Error == null && !e.Cancelled)
{
    ...
}

I am pretty sure it is possible- we use TOAD for Oracle, and it lets you cancel long-running queries, as described here. I'm not sure how they do it though.

If you're using ADO.NET and SQL data provider, take a look at SqlCommand.Cancel method. That does what you're looking for. However, it tries to cancel and the cancellation may take time. Basically, it's up to SQL Server to decide when to grant your cancellation request. When the query is cancelled, you should get a SqlException that indicates that the operation was cancelled by user. Apparently, you don't want to treat this exception as exception and handle it specially such as if SqlException is due to user cancelling the operation, just swallow it.