Cancel Command ADO.NET

[Rodrigo Meneses]

Hello.
The cancel command interrupts the current query being executed... but when I
run another query using the same connection (or another connection using
connection pooling) sometimes I get another "Operation Cancelled by user"
exception. It appears that the Cancel command runs asynchronously and the
connection associated to the command remains in a "Dirty" for a while making
future calls to ExecuteReader on the command throws an Operation Cancelled
by user.
Does somebody know how to control this? How to know when a command is
completly cancelled?
Some code:
string select = "SELECT * FROM A";

SqlConnection c = new SqlConnection(sqlConnection1.ConnectionString);

c.Open();

SqlCommand command = new SqlCommand(select, c);

SqlDataReader r = command.ExecuteReader();

command.Cancel();

command3.Dispose();

r.Close();

c.Close();


SqlConnection c2 = new SqlConnection(sqlConnection1.ConnectionString);


c2.Open();

SqlCommand command2 = new SqlCommand("select top * from B", c2);

SqlDataReader read = command2.ExecuteReader();

while (read.Read()) ///sometimes this line throws an "Operation Cancelled
by user exception" because of the "command.Cancel()"

{

read.ToString();

}

c2.Close();

c2.Dispose();

Thanks

 

[Al Davie]

Sorry, no definate answer for you, but I have a couple of
thoughts.

Although you have both Closed and Disposed of the oringal
Connection object, you are prob. getting the DataBase
connection back, because of connection pooling. You can
confirm for yourself that the DB connection is not closed
by running sp_who after you have Closed or Disposed of the
SqlConnection. It requires approx 6 minutes of no
connection requests before it is removed from the pool.

You can stop pooling by specifying 'pooling=false' in the
connection string.

Personally, I think you have found a bug. The connection
should not be made avaliable from the pool if it is not
yet read.

Al