C# .net Concurrency Problems - SQL Connection then ADO.net changes

Question

I have a c# .net program where I need to first insert data into a table using a sql connection and then adjust the same set of data using ADO.net. I am not sure how to make sure the insert via the sql connection is complete before doing the ado.net changes. I am getting a concurrency violation when I try the code below. I would guess that this is a race condition problem.

I am getting a concurrency violation error at the point of the UpdateAll statement and I can't seem to work around it

Thanks for the help.

Below is an example of the code with the SQL and ado.net changes dramatically simplified.

  try
  {

   String deleteQuery = "DELETE FROM dbo.TABLENAME";

   String reportQuery = @"

  INSERT INTO TABLENAME 
  (
  COLUMN1, 
  COLUMN2, 
  COLUMN3
  )

  SELECT 
  COLUMN1,
  COLUMN2,
  COLUMN3
  FROM OTHERTABLES

  ";



            SqlConnection ReportConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.Text;
            cmd.Connection = ReportConnect;
            cmd.CommandTimeout = Convert.ToInt32(Properties.Settings.Default.ReportTimeout.ToString());


            ReportConnect.Open();

            cmd.CommandText = deleteQuery;

            cmd.ExecuteNonQuery();

            cmd.CommandText = reportQuery;

            cmd.ExecuteNonQuery();

            ReportConnect.Close();

            ReportConnect.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);

        }



        try
        {
            foreach (DataRow dr in DataSet.TABLENAME)
            {
   dr[0] = whatever;
   dr[0] = 100;
   dr[0] = 42.42;
            }
        }
        catch (Exception ax)
        {
            MessageBox.Show(ax.Message);
        }
        finally
        {
            this.tableAdapterManager.UpdateAll(this.DataSet);
        }

Answer 1

In theory ExecuteNonQuery would not complete until the SQL had run, rendering your question moot. If you were deliberately executing asynchronously it would be a different matter, but you're not. You should still be aware of issues caused by multiple concurrent users, of course.

Answer 2

The problem here is that the "tableAdapterManager" appears to be created and opened before the data changes are made (with the sqlcommand). If you create the SqlDataAdapter with the wizard, by default the concurrency mode is optimistic (so the update and delete statement detect if the database has changed...) and fails with the exception you expose.

You can solve this issue in the wizard windows "Generate the SQL statements", click on the "Advanced Options" and uncheck the "Use optimistic concurrency" option.

Also you can change this from the form.designes.cs file, look for the UpdateCommand of the SqlDataAdapter and make sure that in the creation of the SqlParameter the DataRowVersion is set to "Default" or use another constructor.