How to run a stored procedure asynchronously and continue with other statements in C#

Question

The following is the code I have written hoping that I can start the stored procedure asynchronously and get the code under the call running so as to make UI changes necessary. But this works only when I run from visual studio and that too when I put breakpoints. The moment I remove breakpoints it doesn't run the code after the following line: -

IAsyncResult result = command.BeginExecuteNonQuery();

However, I am only assuming it doesn't run but I am not sure as I don't know how to properly know if it is running, only depending on the output I am able to say this. The button hiding or visibility logic doesn't work.

I thought, because the above statement was asynchronous, I can achieve the execution as I needed.

I referred the following to achieve this code:- BeginExecuteNonQuery without EndExecuteNonQuery

 protected void Button1_Click(object sender, EventArgs e)
 {

    using (SqlConnection conn = new SqlConnection(sqlConnectionString))
    {
        try
        {
            SqlCommand command = new SqlCommand();
            command.Connection = conn;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["SqlCommandTimeOut"]);
            command.CommandText = "RunPackage";
            command.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int));
            command.Parameters["@UserID"].Value = SessionVariables.UserID;
            command.Parameters.Add(new SqlParameter("@JobName", SqlDbType.NVarChar));
            command.Parameters["@JobName"].Value = System.Configuration.ConfigurationManager.AppSettings["JobName"].ToString();
            conn.Open();

            IAsyncResult result = command.BeginExecuteNonQuery();
            bool boolresult = GetCurrentRunningJob();

            if (boolresult == true)
            {
                Button1.Enabled = false;
                Button1.Visible = false;
                Button2.Visible = true;
                Button2.Enabled = true;
                lblPackageStatus.Text = "The job is already running. Please wait to re-run.";
                lblPackageStatus.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                Button2.Enabled = false;
                Button2.Visible = false;
                Button1.Visible = true;
                Button1.Enabled = true;
                lblPackageStatus.Text = "";
            }
            command.EndExecuteNonQuery(result);
        }
        catch (Exception err)
        {
            Elmah.ErrorLog.GetDefault(HttpContext.Current).Log(new Elmah.Error(ex));
        }
    }

}

Answer 1

It seems you are checking the to see if the query is done by calling a custom method GetCurrentRunningJob.

If that is the case, you do not need to do that. Instead you can look at the IsCompleted property on the result - true if the operation is complete; otherwise, false.

IAsyncResult result = command.BeginExecuteNonQuery();

while (!result.IsCompleted)
{
   // your code
}

command.EndExecuteNonQuery(result);