How does the cancel command work?

[Rodrigo Meneses]

Does somebody know how does the cancel command work? Because i'm getting the
following error:
When I execute a Cancel method on a command sometimes this cancel request
cancels another command on the same connection (or in another connection
when I use connection pooling) that appears after the first command. For
example

1. SqlCommand c=new SqlCommand("Select * from a", sqlCon1);
2. SqlDataReader r = c.ExecuteReader();
3. c.Cancel();
4. r.Close();
5. c=new SqlCommand("Select * from a", sqlCon1);
6. r = c.ExecuteReader();
7. while (r.Read())
{
}
8. r.Close();
Sometimes the line 7 throws an Operation Cancelled by User exception because
of the Cancel method in line 3. It appears to me that the cancel command is
asyncrhonous and is tied to the connection rather than the command.
Regards,
-Rodrigo

 

[Angel Saenz-Badillos[MS]]

Rodrigo,
The Cancel command is asynchronous and it is also one of the few thread safe
method calls in ado.net. This is necesary since you must call command Cancel
from a different thread for it to have the expected result. This would be a
typical scenario:

Thread1 Execute command that takes a lot of server processing.
Thread2 command Cancel

When the server receives the Cancel command and will stop processing the
result set, if it has already completed the processing it will ignore the
cancel command. Because Sql server is so fast at processing commands Cancel
is only usefull for exceptionally long processing queries.
hope this helps