DataTable.Merge and DataTable.ImportRow does not change RowState

Question

I am having issues with ADO.NET 2.0 merging/importing data. I need to update/insert data from one generic table to another table with both tables maintaining an identical schema. The following code works great locally, but does not make changes to the database:

        OleDbDataAdapter localDA = loadLocalData();            
        OleDbDataAdapter hostedDA = loadHostedData();            

        DataSet dsLocal = new DataSet();            
        localDA.Fill(dsLocal);

        DataSet dsChanges = new DataSet();
        hostedDA.Fill(dsChanges);

        dsLocal.Tables[0].Merge(dsChanges.Tables[0],false);

        localDA.Update(dsLocal.Tables[0]);

The same is true with this code snippet:

        OleDbDataAdapter localDA = loadLocalData();
        OleDbDataAdapter hostedDA = loadHostedData();

        DataSet dsLocal = new DataSet();
        localDA.Fill(dsLocal);

        DataSet dsChanges = new DataSet();
        hostedDA.Fill(dsChanges);

        foreach (DataRow changedRow in dsChanges.Tables[0].Rows)
        {
            if (recordExists(dsLocal.Tables[0], changedRow["ID"]))
            {

            }
            else
            {
                dsLocal.Tables[0].ImportRow(changedRow);
            }
        }

        localDA.Update(dsLocal.Tables[0]);

When I looked at the RowState property for changed/appended rows they remain "unchanged". I am wanting to avoid data mapping the columns if possible, which is what I may have to do using the NewRow() method and modifying an existing row.

I can come up with a solution that modifies the RowState when using ImportRow. The following code snippet will work: dsLocal.Tables[0].ImportRow(changedRow); dsLocal.Tables[0].Rows[dsLocal.Tables[0].Rows.Count - 1].SetAdded(); However, this only works on appending rows and not updating rows. Any ideas on merge would be very helpful! — Blake Blackwell, Sep 22 '09 at 16:17

score 6 · Answer 1 · answered Jan 21 '13 at 20:23

To get the behavior you want, I did the following instead of dsLocal.Tables[0].Merge(dsChanges.Tables[0],false);:

using (DataTableReader changeReader = new DataTableReader(dsChanges.Tables[0]))
    dsLocal.Tables[0].Load(newTableReader, LoadOption.Upsert);

This reads the changes table and "upserts" into the 'local' table, giving you the appropriate change notifications.

score 6 · Accepted Answer · answered Oct 27 '09 at 15:32

6

You need to fire off the appropriate function for each DataRow.

DataRow.SetAdded()
DataRow.SetModified()

This will update the DataRow.RowState value. This is what a DataAdapter goes by to determine which rows need what actions performed on them.

It would be nice if their was something like a binded dataset in .net so that it would manage these mundane details for us.

answered Oct 27 '09 at 15:32

Chad

892
2
11
25

note, when I use `row.SetAdded()`, I received an exception stating: *SetAdded and SetModified can only be used when the row status is Unchanged.* – IAbstract Dec 14 '10 at 17:27
You only want to use one of them at a time. – JeffO Feb 28 '11 at 19:56

DataTable.Merge and DataTable.ImportRow does not change RowState

2 Answers2

Linked