mysqldatatablemergesqlcommandbuilder

How to override values in merging DataTables using .Merge() method?


Is there a way to override values in merging DataTables using .Merge() method?

For example if I have two DataTables with the same schematics.

Both have two Columns: Names | Enabled;

DataTable1 first row entries are: Peter Punani | true;

DataTable2 first row entries are: Peter Punani | false;

If I merge them using .Merge() method - DataTable1.merge(DataTable2); -, DataTable1 will then have 2 rows and look like this:

Names | Enabled;  
Peter Punani | true;  
Peter Punani | false;  

Now obviously if I try to .Update() that using SqlCommandBuilder to my SQL DB it will give me an error statement, because of two identical values in my Primary Key(Names).

But I want DB1 to accept the differences from DB2, so it would look like: (and do that with all other entries that differ from DB1.)

Names | Enabled;  
Peter Punani | false; 

So what is the best way to merge these tables, so I can update them properly?


Solution

  • I just implemented my method this way now:

    public static bool synchSqlData(DataTable UpdateTable, string selectedTableName, SqlConnection sqlCon, DataTable MergeTable, bool merge)
        {
            bool success = false;
            //Mitgabe eines SELECT cmds für SqlCommandBuilder zum generieren von INSERT,DELETE und UPDATE
            using (SqlCommand sqlCom = new SqlCommand("SELECT * FROM dbo." + selectedTableName, sqlCon))
            {
                SqlDataAdapter da = new SqlDataAdapter(sqlCom);
                SqlCommandBuilder cmb = new SqlCommandBuilder(da);
    
                da.InsertCommand = cmb.GetInsertCommand();
                da.DeleteCommand = cmb.GetDeleteCommand();
                da.UpdateCommand = cmb.GetUpdateCommand();
    
                //Zuweisen von PKs
                UpdateTable.PrimaryKey = new DataColumn[] { UpdateTable.Columns[0] };
                MergeTable.PrimaryKey = new DataColumn[] { MergeTable.Columns[0] };
    
                //Zusammenführen der beiden DataTables in UpdateTable
                if (merge)
                {
                    //Setzen der Rows auf changed damit sie bei da.Update() auch erkannt werden ;)
                    foreach (DataRow dr in MergeTable.Rows)
                    {
                        if (dr.RowState == DataRowState.Unchanged)
                        {
                            dr.SetAdded();
                        }
                        //Entfernen von doppelten Zeilen zur Änderung einzelner values
                        DataRow doubleRow = UpdateTable.Rows.Find(dr[0].ToString());
                        if (doubleRow != null)
                        {
                            doubleRow.Delete();
                        }
                    }
                    UpdateTable.Merge(MergeTable);
                    MessageBox.Show("Merge abgeschlossen.");
                }
    
                try
                {
                    da.Update(UpdateTable);
                    success = true;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error has occured!" + ex.Message);
                    success = false;
                }
            }
            return success;
        }
    

    Basically I just delete all duplicate rows before i merge and update, works fine for me but probably not the ideal solution.