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.