How To perform a SQL Query to DataTable Operation That Can Be Cancelled
- by David W
I tried to make the title as specific as possible. Basically what I have running inside a backgroundworker thread now is some code that looks like:
SqlConnection conn = new SqlConnection(connstring);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(Results);
conn.Close();
sda.Dispose();
Where query is a string representing a large, time consuming query, and conn is the connection object.
My problem now is I need a stop button. I've come to realize killing the backgroundworker would be worthless because I still want to keep what results are left over after the query is canceled. Plus it wouldn't be able to check the canceled state until after the query.
What I've come up with so far:
I've been trying to conceptualize how to handle this efficiently without taking too big of a performance hit.
My idea was to use a SqlDataReader to read the data from the query piece at a time so that I had a "loop" to check a flag I could set from the GUI via a button. The problem is as far as I know I can't use the Load() method of a datatable and still be able to cancel the sqlcommand. If I'm wrong please let me know because that would make cancelling slightly easier.
In light of what I discovered I came to the realization I may only be able to cancel the sqlcommand mid-query if I did something like the below (pseudo-code):
while(reader.Read())
{
//check flag status
//if it is set to 'kill' fire off the kill thread
//otherwise populate the datatable with what was read
}
However, it would seem to me this would be highly ineffective and possibly costly. Is this the only way to kill a sqlcommand in progress that absolutely needs to be in a datatable? Any help would be appreciated!
