Serializing Aysnchronous SQLCommand

Question

I have the code below which works fine when the Session state is InProc. However when the Session state is Sql Server, HandleCallback never gets called. How do I change the code so HandleCallBack gets called?

    private void TAdata(object sender, EventArgs e)
    {
        if (((Form)sender).DialogResult == DialogResult.No)
        {
            return;
        }

        if (Changed)
        {
            MessageBox.Show(this.ParentForm, "Save Payroll Changes First", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
        else
        {
            SqlConnection dbconnAS = new SqlConnection(strDBconnAS);
            {
                try
                {
                    AsyncCallback callback = new AsyncCallback(HandleCallback);
                    using (SqlCommand SQLcmd = new SqlCommand("dbo.KronosTaData", dbconnAS))
                    {
                        SQLcmd.CommandType = CommandType.StoredProcedure;
                        dbconnAS.Open();
                        Changed = true;
                        SQLcmd.BeginExecuteNonQuery(callback, SQLcmd);
                        strResult = "";
                        ExportProgress.Visible = true;
                        ExportProgress.Value = 0;
                        ExportProgress.Maximum = 120;
                        ExportTimer.Start();
                    }
                }
                catch (Exception ex)
                {
                    Changed = false;
                    strResult = ex.Message;
                    if (dbconnAS != null)
                    {
                        dbconnAS.Close();
                    }
                }
            }
        }
    }

    private void HandleCallback(IAsyncResult result)
    {
        try
        {
            using (SqlCommand SQLcmd = (SqlCommand)result.AsyncState)
            {
                int rowCount = SQLcmd.EndExecuteNonQuery(result);
                strResult = "OK";
                SQLcmd.Connection.Close();
            }
        }
        catch (Exception ex)
        {
            strResult = ex.Message;
        }

    }

    private void ExportTimer_Tick(object sender, EventArgs e)
    {
        //Timer Exists on UI thread
        if (strResult == "")
        {
            if (cmdKronos.Enabled) cmdKronos.Enabled = false;
            if (ExportProgress.Value > ExportProgress.Maximum - 10) ExportProgress.Maximum += 10;
            ExportProgress.Value += 1;
        }
        else if (strResult == "OK")
        {
            Changed = false;
            cmdKronos.Enabled = true;
            ExportProgress.Visible = false;
            ExportTimer.Stop();
            MessageBox.Show(ParentForm, "Kronos data succesfully imported", "Data Import", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        else
        {
            Changed = false;
            cmdKronos.Enabled = true;
            ExportProgress.Visible = false;
            ExportTimer.Stop();
            MessageBox.Show(ParentForm, Text, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
    }

Answer 1

You are disposing the command as soon as you've finished starting it:

using (SqlCommand SQLcmd = new SqlCommand("dbo.KronosTaData", dbconnAS))
{
    //...
    SQLcmd.BeginExecuteNonQuery(callback, SQLcmd);
    //...
}

that will abort everything - so indeed: it will never complete. Basically; using doesn't play nicely with Begin*/End*, so don't do that. You might find it much easier to do this using async/await, by the way (via ExecuteNonQueryAsync).

You also probably want to close and dispose the connection somewhere; again, async/await would make this much easier to get right.

Answer 2

The solution is to declare the variable strResult as static.

See Visual Webgui Variable Scope