Updating MySQL returns rows affected, but doesn

Question

I'm currently using Mono on Ubuntu with MonoDevelop, running with a DataTable matching a table in the database, and should be attempting to update it.

The code following uses a Dataset loaded from an XML file, which was created from a Dataset.WriteXML on another machine.

try
{
    if(ds.Tables.Contains(s))
    {                        
        ds.Tables[s].AcceptChanges();
        foreach(DataRow dr in ds.Tables[s].Rows)
            dr.SetModified(); // Setting to modified so that it updates, rather than inserts, into the database
        hc.Data.Database.Update(hc.Data.DataDictionary.GetTableInfo(s), ds.Tables[s]);
    }
            }
            catch (Exception ex)
            {
                Log.WriteError(ex);
            }

This is the code for inserting/updating into the database.

public override int SQLUpdate(DataTable dt, string tableName)
    {
        MySqlDataAdapter da = new MySqlDataAdapter();
        try
        {
            int rowsChanged = 0;
            int tStart = Environment.TickCount;

            da.SelectCommand = new MySqlCommand("SELECT * FROM " + tableName);
            da.SelectCommand.Connection = connection;

            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            da.UpdateCommand = cb.GetUpdateCommand();
            da.DeleteCommand = cb.GetDeleteCommand();
            da.InsertCommand = cb.GetInsertCommand();
            da.ContinueUpdateOnError = true;
            da.AcceptChangesDuringUpdate = true;

            rowsChanged = da.Update(dt);

            Log.WriteVerbose("Tbl={0},Rows={1},tics={2},", dt.TableName, rowsChanged, Misc.Elapsed(tStart));
            return rowsChanged;
            catch (Exception ex)
            {
                Log.WriteError("{0}", ex.Message);
                return -1
            }

I'm trying the above code, and rowsChanged becomes 4183, the number of rows I'm editing. However, when I use HeidiSQL to check the database itself, it doesn't change anything at all.

Is there a step I'm missing?

Edit: Alternatively, being able to overwrite all rows in the database would work as well. This is a setup for updating remote computers using USB sticks, forcing it to match a source data table.

Edit 2: Added more code sample to show the source of the DT. The DataTable is prefilled in the calling function, and all rows have DataRow.SetModified(); applied.

Edit 3: Additional information. The Table is being filled with data from an XML file. Attempting fix suggested in comments.

Edit 4: Adding calling code, just in case.

Thank you for your help.

Answer 1

The simplest way which you may want to look into might be to TRUNCATE the destination table, then simply save the XML import to it (with AI off so it uses the imported ID if necessary). The only problem may be with the rights to do that. Otherwise...

What you are trying to do can almost be handled using the Merge method. However, it can't/won't know about deleted rows. Since the method is acting on DataTables, if a row was deleted in the master database, it will simply not exist in the XML extract (versus a RowState of Deleted). These can be weeded out with a loop.

Likewise, any new rows may get a different PK for an AI int. To prevent that, just use a simple non-AI PK in the destination db so it can accept any number.

The XML loading:

private DataTable LoadXMLToDT(string filename)
{
    DataTable dt = new DataTable();
    dt.ReadXml(filename);
    return dt;
}

The merge code:

DataTable dtMaster = LoadXMLToDT(@"C:\Temp\dtsample.xml");
// just a debug monitor
var changes = dtMaster.GetChanges();

string SQL = "SELECT * FROM Destination";
using (MySqlConnection dbCon = new MySqlConnection(MySQLOtherDB))
{
    dtSample = new DataTable();
    daSample = new MySqlDataAdapter(SQL, dbCon);

    MySqlCommandBuilder cb = new MySqlCommandBuilder(daSample);
    daSample.UpdateCommand = cb.GetUpdateCommand();
    daSample.DeleteCommand = cb.GetDeleteCommand();
    daSample.InsertCommand = cb.GetInsertCommand();
    daSample.FillSchema(dtSample, SchemaType.Source);
    dbCon.Open();

    // the destination table
    daSample.Fill(dtSample);

    // handle deleted rows
    var drExisting = dtMaster.AsEnumerable()
                .Select(x => x.Field<int>("Id"));
    var drMasterDeleted = dtSample.AsEnumerable()
                .Where( q => !drExisting.Contains(q.Field<int>("Id")));

    // delete based on missing ID
    foreach (DataRow dr in drMasterDeleted)
        dr.Delete();

    // merge the XML into the tbl read
    dtSample.Merge(dtMaster,false, MissingSchemaAction.Add);

    int rowsChanged = daSample.Update(dtSample);
}

For whatever reason, rowsChanged always reports as many changes as there are total rows. But changes from the Master/XML DataTable do flow thru to the other/destination table.

The delete code gets a list of existing IDs, then determines which rows needs to be deleted from the destination DataTable by whether the new XML table has a row with that ID or not. All the missing rows are deleted, then the tables are merged.

The key is dtSample.Merge(dtMaster,false, MissingSchemaAction.Add); which merges the data from dtMaster with dtSample. The false param is what allows the incoming XML changes to overwrite values in the other table (and eventually be saved to the db).

---

I have no idea whether some of the issues like non matching AI PKs is a big deal or not, but this seems to handle all that I could find. In reality, what you are trying to do is Database Synchronization. Although with one table, and just a few rows, the above should work.