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);
        }
    }

score 3 · Answer 1 · answered Jan 13 '15 at 14:25

3

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.

answered Jan 13 '15 at 14:25

Marc Gravell

1,026,079
266
2,566
2,900

In the non serialized environment it works fine. In fact the key to failure was using 'using' to dispose the SQLconnection. As soon as I took this away it worked OK in the InProc state. – Ewan Jan 13 '15 at 15:24
I tried async / await, however these commands do not play well in the Visual Webgui environment – Ewan Jan 13 '15 at 15:32
After a closer inspection I have discovered the issue but not quite sure how to solve it. The issue is when the callback fires it sets strResult to OK. However the value of strResult on the main thread remains empty string. It is as if suddenly there are two versions of strResult. – Ewan Jan 14 '15 at 08:16
@user3540780 threading is hard ;p You haven't shown how the main thread is accessing `strResult`, so I can't comment; but yes: it is possible for things to report incorrectly if you don't synchronize correctly. – Marc Gravell Jan 14 '15 at 14:38
The main thread has a timer running that is stopped when the value of strResult changes. The timer increments a progress bar, which is the only way of achieving this using WebGUI – Ewan Jan 14 '15 at 16:31

Ewan · Answer 2 · 2015-02-15T13:21:03.273

-3

The solution is to declare the variable strResult as static.

See Visual Webgui Variable Scope

edited Feb 15 '15 at 13:21

answered Jan 14 '15 at 13:25

Ewan

1,067
8
15

I don't think that I have much of a choice as different threads create separate variable instances unless the variable is declared static. Can you suggest an alternative? – Ewan Jan 14 '15 at 16:27
is is **completely** incorrect to say that "different threads create separate variable instances". `strResult` is a field: it is scoped to the object. What you are *probably* seeing is CPU-level caching because you have failed to synchronize properly. Again: I would need to see where the other thread accesses `strResult` to comment in detail. `static` means something **completely different**. – Marc Gravell Jan 14 '15 at 17:11
Bad guess! I have never seen this before as it does not occur when using in memory processing. I have only seen it occur when using a state server. In VWG static variables are common to all users. So using a static variable might be the correct choice in this example as I do not want more than a single user to run this at any given point in time. – Ewan Jan 15 '15 at 12:22
if the problem is your scoping of objects, then that is a separate issue that you need to resolve; again, you *still* haven't shown your usage. I'm not familiar with "VWG", and google yields nothing. However, your code looks like winforms, in which case "all users" sounds ... odd. Either way, making a result field `static` still seems the entirely wrong way to fix this; the correct way is twofold: 1: make sure you know which object instance you are talking to; 2: synchronize correctly. `static` touches only the first of these (and in a bad way). Both are required. – Marc Gravell Jan 16 '15 at 07:52
VWG = Visual WebGui (visualwebgui.com) The code looks like winforms because the whole concept behind VWG is windows on the web. Since your comments I have done considerable research on this. The Microsoft way is to pass the variable values back to the main UI thread using Invoke. I tried this without success whist invoke worked correctly the main UI still did not get the values. I suspect serialization is causing the issue here. – Ewan Jan 16 '15 at 08:46
Also see http://wiki.visualwebgui.com/pages/index.php/Storage_Scopes#Context_Scope which discusses variable scope. – Ewan Jan 16 '15 at 15:20

Serializing Aysnchronous SQLCommand

2 Answers2