Synchronize DataTable with MySQL database two ways

Question

I have a DataSet/DataTable which I would like write changes to a MySQL database and if there are any changes in the MySQL database write those changes back to my DataSet/DataTable.

In short:

myDataSet -> MySQL database (user edits values)

myDataSet <- MySQL database (another user edited the MySQL database)

Filling the DataSet from the MySQL database is done this way:

private MySqlDataAdapter mysqlDataAdapter;
private DataSet dsTable;
private string _tablename;

public void ReadTable(string tablename)
{
    _tablename = tablename;
    String query = "select * from " + tablename;

    if (dsTable == null)
        dsTable = new DataSet();

    mysqlDataAdapter = new MySqlDataAdapter(query, (MySqlConnection)connection);
    MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(mysqlDataAdapter);

    mysqlDataAdapter.Fill(dsTable, tablename);
}

Updating values in the table there is Row_Changed EventHandler set for the table:

private void Row_Changed(object sender, DataRowChangeEventArgs e)
{... ChangeIntegerValueInReflectedTable(...); }

where I call:

public void ChangeIntegerValueInReflectedTable(int rowIndex, string columnName, int newValue)
{
    dsBom.Tables[0].Rows[rowIndex][columnName] = newValue;
}

To update the MySQL Database I use:

public void UpdateTable()
{
        mysqlDataAdapter.Update(dsTable, _tablename);
}

This works. Now I would like to update changes in the remote MySQL database in my local table. There is no command for this as far as I know and mysqlDataAdapter.Update doesn't make updates back to my table only local table -> MySQL database.

So I wrote an functions which fetches the Table every 30 seconds and compares it. Then I write the changes into my dsTable. That works but next time I call UpdateTable() I get the error:

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. System.Exception {System.Data.DBConcurrencyException}

I don't know why. I change the cells in dsTable too when I update single values from the user interface.

When I call mysqlDataAdapter.Fill(dsTable, tablename) my datagridview is refreshed in a endless loop.

I'm stuck right now. Is there a good approach?

flexo · Answer 1 · 2014-10-07T05:37:06.230

Okay, now I understand it.

First: when the RowState of one row is set to modified (which is the case when setting a new value) and I call the mysqlDataAdapter.Update(dsTable, _tablename); I get the error because the set value is the same as it is in the database.

Second: because I had the Eventhandler Row_Changed active, suddenly after changing the value in my table to the value I have in the MySQL database the mysqlDataAdapter.Update(dsTable, _tablename) was called before I could set the RowState to Unchanged.

Solution: I simply set a bool value when I searching and changing for differences made on the remote MySQL server. While this value is set to true the Row_Changed doesn't fire the UpdateTable(). When changing a value in my local table (because it has changed on the remote server) I call AcceptChanges() for the row IF the row isn't changed already. When the user does make changes to the local table in this short time I update the changes aren't lost, they are only local and aren't updated on the remote MySQL server immediately.

Synchronize DataTable with MySQL database two ways

1 Answers1