CODEWITHSUNDEEP
c#.netado.net
Aline Bernstein
Aline Bernstein

Reputation: 5

Multiple calls to CreateCommand on same SqlConnection

I have a Windows Forms app in C#/.NET4 with a SQL 2008 back end. I open a SqlConnection in my button click event handler, then call multiple backgroundworkers each of which calls the connection's CreateCommand method inside of a using block:

    private void btnSubmit_Click(object sender, EventArgs e)
    {
        cn.Open();
        bw01.RunWorkerAsync(x);
        bw02.RunWorkerAsync(x);
        while (bw01.IsBusy || bw02.IsBusy)
            Application.DoEvents();
        cn.Close();
    }

    private void bw01_DoWork(object sender, DoWorkEventArgs e)
    {
        try
        {
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = <some SQL>
                e.Result = Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return;
        }
    }

    private void bw02_DoWork(object sender, DoWorkEventArgs e)
    {
        try
        {
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = <some SQL>
                e.Result = Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return;
        }
    }

The code works some of the time, but other times I get errors, most commonly "A severe error occurred on the current command. The results if any should be discarded."

I have MultipleActiveResultSets=true in my connection string. What am I doing wrong?

Upvotes: 0

Views: 1578

Answers (2)

Phil
Phil

Reputation: 1973

Are these commands going to be SELECTs (read only), INSERTs/UPDATEs/DELETEs or what? You might also want to be using a dataadaptor in your case, and filling a dataset with the dataadaptor values. Set the e.result to that value.

And this won't fix your issue really, but you probably want to set the CommandType of the sqlcommand as well.

Upvotes: 0

fenix2222
fenix2222

Reputation: 4730

You connection variable is not thread safe as both threads are trying to use it. ADO.NET will manage connection pool for you so you should move connection creation inside each thread and make it atomic

Upvotes: 2

Related Questions