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!

Answer 1

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/