Run stored procedure in the background while ASP.NET C# page goes to a different page

Question

I'd like to submit my form and then let it go to another page (telling the user that they'll receive an email when it's finished) while running the stored procedure (SQL) in the background.

I don't want the user to wait 30 mins for the search to complete and then go to the other page. How can I accomplish this?

// Clicking on the Search button    
protected void btnSearch_Click(object sender, EventArgs e)
{
    Guid SearchGUID;
    SearchGUID = Guid.NewGuid();

    Hashtable htSearch = new Hashtable();
    htSearch["value"] = 2;

    // Run the stored procedure (takes 30 mins)
    objSmart.RunSearch(htSearch);

    // Redirect to the other page
    Response.Redirect("Search.aspx?search=" + SearchGUID.ToString());
}

Answer 1

If your background task were going to take only 30~90 seconds, it could be handled with Async, the thread pool/Tasks library, or any number of other solutions. But once it starts taking several minutes, or in your case, 30+ minutes, you need a more robust out-of-process solution. ASP.NET's architecture is just not designed for long-running background threads. IIS will recycle your application pool/domain at random intervals to optimize resource usage, and while it will try its best to let your threads finish, that's not guaranteed. See Scot Hanselman's blog post on this subject for some background and suggestions.

Here are a few solutions that you could consider, depending on your application constraints:

• If you're using MS SQL Server, you might be able to encapsulate the stored procedure and the completion email in a SQL Agent Job that you kick off from your web app. (You can even run .NET code in SQL Server, although it's not pretty.)
• You could write a true background process to run as an EXE and be hosted in Windows Task Scheduler. Your web app can manually kick off this task, e.g. C# start a scheduled task.
• You could use a third-party background tasking/queuing framework like https://www.hangfire.io/.