Geek Methods ®: Solve multiple concurrence with ADO.NET

When we have multiple instances of the same application updating the same database, we have to stablish control for handling the possible concurrence conflicts amidst all requests to the database

In this example we have the next application and data in a grid

The code for submit changes to the database when updated is the next

     
	private void btnUpdate_Click(object sender, EventArgs e)
    {
        commandBuilder = new SqlCommandBuilder(dataAdapterConcerts);
        dataAdapterConcerts.Update(dataSet.Tables["Concerts"]);
        dataSet.AcceptChanges();
    }

In order to stablish concurrence control, we are going to implement the event handler RowUpdated of our SqlDataAdapter object

    dataAdapterConcerts.RowUpdated += 
        new SqlRowUpdatedEventHandler(dataAdapter_RowUpdated);

with the next code

     
	private void dataAdapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
    {
        if (e.Status == UpdateStatus.ErrorsOccurred)
        {
            solveConcurrence(e.Row);
            e.Status = UpdateStatus.SkipCurrentRow;
        }
    }

The method solveConcurrence for the row is as follows

     
private void solveConcurrence(DataRow row)
{
    DataSet actualData = new DataSet();
    dataAdapterConcerts.Fill(actualData, "Concerts");
    dataAdapterHalls.Fill(actualData, "Halls");

    actualData.Tables["Concerts"].PrimaryKey = new DataColumn[] 
        { actualData.Tables["Concerts"].Columns["IdConcert"] };

    DataRow actualRow = actualData.Tables["Concerts"].Rows.Find((int)row["IdConcert"]);

    string message = string.Format("actual value = title: {0} - id hall: {1} - date: {2}
       \r\rproposed value = title: {3} - id hall: {4} - date: {5}",
        actualRow["Title"], actualRow["IdHall"], actualRow["Date"], 
        row["Title"], row["IdHall"], row["Date"]);

    if ((MessageBox.Show("Concurrence conflict\r\rdo you want to override changes?\r\r" 
        + message, "Concerts#",
        MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        == DialogResult.Yes))
    {
        dataSet.Merge(actualData, true);

        dataAdapterConcerts.Update(dataSet.Tables["Concerts"]);

        row.AcceptChanges();
    }
    else
    {
        row["IdConcert"] = actualRow["IdConcert"];
        row["Title"] = actualRow["Title"];
        row["IdHall"] = actualRow["IdHall"];
        row["Date"] = actualRow["Date"];

        row.AcceptChanges();
    }
}

The result of this code when we launch two or more instances of the application and apply different changes on the same row, is the next

Posts

Saturday, May 4, 2013

Solve multiple concurrence with ADO.NET