Executing a stored procedure within a function and not waiting for the return

Question

I know somewhere I may have overlooked something, but I am trying to fix an application that I didn't develop and don't usually maintain, so forgive me in advance if I am making a straight forward blunder.

There is currently an issue with this web-app that is causing a timeout when executing a certain function, I've traced it down to one thing, it is executing a stored procedure and expecting the stored procedure to return the # of rows affected, which is all fine and dandy. However, this stored procedure runs a minimum of 50s and is causing a timeout. Also, I don't want the UI to hang for 50s while this happens, it is just updating some status fields in a database based on the information that was changed, what it returns is of no use to the end user, so it just needs to be triggered and forgotten about so that the user can continue to use the application.

I've tried both creating a task and creating a thread and for some reason the function insists on waiting for this to complete.

This is the function that calls the stored procedure:

 public static void UpdateComputerStatusByApplicationID(int ApplicationID)
{
    using (SqlConnection Conn = new SqlConnection(GlobalMethods.CONNECTION_STRING))
    {
        SqlCommand Cmd = new SqlCommand("UpdateComputerStatusByApplicationId", Conn);
        Cmd.CommandType = CommandType.StoredProcedure;
        Cmd.Parameters.Add("@applicationid", SqlDbType.Int).Value = ApplicationID;

        Conn.Open();
        Cmd.ExecuteNonQuery();
        Conn.Close();
    }
}

And the exception is coming from Cmd.ExecuteNonQuery(); which is a timeout exception.

This is where I am calling it as a task from the function:

public void UpdateApplication(a_Applications newApplication, XPToWin7 xpToWin7)
    {
        try
        {
            var orginalApplication = GetApplication(newApplication.AutoNumber);
            ObjectContext.ApplyCurrentValues(orginalApplication.EntityKey.EntitySetName, newApplication);

            if (xpToWin7 != null)
            {
                UnlinkXPAppWithWin7App(orginalApplication.AutoNumber);
                LinkXPAppWithWin7App(orginalApplication.AutoNumber, xpToWin7);
            }

            Task UpdateComputerStatus = Task.Factory.StartNew(() =>
            {
                DAL.UpdateComputerStatusByApplicationID(orginalApplication.AutoNumber);
            }, TaskCreationOptions.LongRunning);

            SaveChanges();
        }
        catch (Exception ex)
        {
            throw new Exception("Exception when updating the application" + ex.Message, ex);
        }
    }

I'm not sure where I've gone wrong or why it isn't working as intended but when this function gets invoked then it just hangs in the UI till it throws an inner exception which is from the cmd.ExecuteNonQuery(); .

Any help or suggestions would be greatly appreciated!

Solution

Calling stored procedures asynchronously from the client can be a royal pain - you have to consider multiple threads, BeginXXX and EndXXX methods, use callbacks and the application must remain active until it's completed - otherwise a rollback might occur. Yes, it's possible - have a peek at this answer: Asynchronous call of a SQL Server stored procedure in C#

There might be an easier way - why not make the stored procedure asynchronous? With Service Broker you can create a simple queue that activates a stored procedure. The calling stored procedure does not have to wait for a response. It simply creates a new conversation passing in the details of the request and immediately returns. The activated stored procedure is what does all the heavy lifting which could take seconds, minutes or hours - but that doesn't really matter to the UI because it's not sitting around waiting for a response. Even if your UI shuts down, the asynchronous stored procedure would run until completion.

http://rusanu.com/2009/08/05/asynchronous-procedure-execution/

Comments

[Steve]

Start changing the cmd.CommandTimeout property to a longer value, The default is 30 seconds. This should resolve the Timeout exception though not the UI hang

[Chris Gessler]

50 seconds is a long time for a stored proc - can you please post the stored proc, maybe it's using a cursor that can be removed, or possibly there's a missing index

[Evan L]

Also, the UI issue can be solved by having your UI and Data operations on different Threads. Consider using a BackgroundWorker and calling your update methods asynchronously.

[Chris Gessler]

Are you using SQL Server?

[ron tornambe]

Have you tried running the procedure outside of the application to see if it also takes 50s to complete?

[BAllan]

I am using SQL Server, I'll post the stored proc in the question.

[BAllan]

Hey Chris, So I've experimented with a couple of options including calling jobs from stored procedures, which gets me further then I've been because the UI no longer hangs, but once you introduce multiple users you run into errors because the job cannot run through in multiple instances. I think the service broker is the solution to this, I'm wondering if you could point me to can example of a solution doing something similar? I'm not familiar with using the Service Broker as I've been mostly working in the SharePoint realm but have recently moved into doing some work for our apps team.

[Chris Gessler]

@BAllan - added a link to my answer.