How can I cancel a database query in ASP.NET when the user's browser disconnects?

Question

My ASP.NET (3.5) app allows users to run some complex queries that can take up to 4 minutes to return results.

When I'm doing a long loops of code, I'll check Response.IsClientConnected() occasionally so I can end the page if the user closes their browser or hits the stop button.

But when querying SQL Server, my .NET code is blocked at the call to GetDataReader().

Is there a straightforward way to do GetDataReader() asynchronously so I can wait around for it but still check, say, every 5-10 seconds to see if the user is still connected, and bail on the database query if they've left?

Or is there some other alternative I'm not thinking of?

Answer 1

I would suggest creating an object that has Start() and Wait() methods encapsulating your query. Internally use a ManualResetEvent to wait on the query completing. You'll use the Set() function when the query is complete, and the WaitOne() or WaitOne(TimeSpan ts) method to wait on it in your Wait() method.

Answer 2

you can use the sqlcommand's BeginExecuteReader to get an asynch sqlreader example

not 100% sure if thats what you need?

link showing cancel