Multiple calls to CreateCommand on same SqlConnection

Question

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?

fenix2222 · Accepted Answer · 2012-06-18 04:11:11Z

2

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

answered Jun 18, 2012 at 4:11

fenix2222

4,7504 gold badges39 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aline Bernstein Over a year ago

Ah, did not realize that. Changed my pattern and it works like a charm. You saved my night.

Phil · Accepted Answer · 2012-06-18 04:40:29Z

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.

they are SELECTs and I use dataadapter fill when returning a table, executescalar when returning a count.

Collectives™ on Stack Overflow

Multiple calls to CreateCommand on same SqlConnection

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related