LINQ-SQL & ADO.NET - How to make a bulk transaction entirely asynchronous?

Question

Hi Guys,

So i'm dealing with an ASP.NET 4.0 Web Forms Application in which the DAL is built with a combination of LINQ-SQL and classic ADO.NET (for auditing transactions, and bulk updates).

I have an admin page on the site which performs an update on a bunch of records (could be thousands), and not to mention there is T-SQL triggers in place on those records. Needless to say, its a killer of an update.

So of course, the page is timing out.

The update transaction is performing with the following code:

db.ExecuteCommand("UPDATE [tblFoo] SET [IsFoo] = 0 WHERE [Bar] = {0}", bar.Id);

So it's a classic ADO.NET bulk update.

What i've tried to do is make the call to this method asynchronous, by firing off a thread on the button click on the form:

protected void MyButton_Click(object sender, EventArgs eventArgs)
{
    var thread = new Thread(OnMyAsyncMethod) { Name = "Hi, im a thread, how are you?"};
    var dataArray = new object[2];
    dataArray[0] = someData;
    dataArray[1] = someData2;
    thread.Start(dataArray);
}

The method OnMyAsyncMethod simply executes the above ADO.NET call.

This solved the UI problem, being the page now posts back and refreshes immediately. But then around 30 seconds lateri see that wonderful little flashing light on my Visual Studio toolbar - "an unhandled exception has occured, would you like to attach to process, etc".

So of course, now the actual call in the DAL is timing out.

Am i doing this wrong - is there a way i can perform the update transaction (db.ExecuteCommand) totally asynchrously?

Hopefully you see what im trying to do - i just need to fire off a killer of a T-SQL transaction. The only thing i need back from the call is the number of rows updated.

Any ideas people?

Answer 1

Solved issue by increasing timeout for that specific command.

db.CommandTimeout = 300; // set timeout to 5 minutes

But i'm open to some better suggestions.

Answer 2

You can't do this kind of thing in ASP.NET! The page object, and everything on it is gone after the request completes!

See Wicked Code: Asynchronous Pages in ASP.NET 2.0, and in general, http://social.msdn.microsoft.com/Search/en-US/?Query=asynchronous+asp.net.