datatable.merge and rowstate

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
Hi

I have 2 databases with the same structure.

I have filled the 1st dt up with contents of the local db and the 2nd dt with content of the remote db.

I have used dt1.merge(dt2) hoping that i would get a dt1 with all the changes and new rows from dt2 and i could do a da.update(dt1) to commit the changes to db.

At the moment dt1 is empty and dt2 has 4 records. When the 4 records get copied to dt1 the rowstate of them all is unchanged. I was hoping this would be 'added' and would then would then insert to the db.

How can I achieve this behaviour please?

Is a case of having to manually set the rowstate in dt1 for all the dt2 extras rows?

VB.NET:
dt1TableAdapter.Fill(Dataset.dt1)

cmd = New MySqlCommand("SELECT* from coupons", DataConnection.getRemoteConnection)
dr = cmd.ExecuteReader()

Dim dt2 As New DataTable
dt2.Load(dr)

Dataset.dt1.Merge(dt2)

dt1TableAdapter.Update(dt1)

Thanks
 

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
erm interesting point.

I would say as they are read from a db and not changed they would be 'unchanged' aswell.

if i change them all to 'changed' would they 'merge' nicely with the existing records?

for example some records might already be there, so i wont to overwrite, and some might be new so i need to insert.

Thanks
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,053
Programming Experience
10+
My tests indicate that if the row being merged in is Unchanged, then the destination row will also be unchanged. If the incoming row is added then the destination row will be added if it didnt exist, or modified if it did. If the incoming row is modified, then the destination row will be modified

VB.NET:
DataTable dt = new DataTable();
      dt.Columns.Add("test");
      dt.Columns.Add("other");
      dt.Constraints.Add("pk", dt.Columns["test"], true);

      DataTable dt2 = new DataTable();
      dt2.Columns.Add("test");
      dt2.Columns.Add("other");
      
      DataRow ro = dt2.NewRow();
      ro["test"] = "New row";
      ro["other"] = "New row";
      dt2.Rows.Add(ro);

//row is currently Added. comment these in or out depending if you want the row unchanged, added or modded
      ro.AcceptChanges();
      ro.SetModified();

      ro = dt2.NewRow();
      ro["test"] = "Updated row";
      ro["other"] = "DT2 value";
      dt2.Rows.Add(ro);
      
//row is currently Added. comment these in or out depending if you want the row unchanged, added or modded
      ro.AcceptChanges();
      ro.SetModified();


//add one row to the dest table
      ro = dt.NewRow();
      ro["test"] = "Updated row";
      ro["other"] = "DT1 value";
      dt.Rows.Add(ro);

      dt.AcceptChanges();


      dt.Merge(dt2);

I didnt test rowstate deleted
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,053
Programming Experience
10+
if i change them all to 'changed' would they 'merge' nicely with the existing records?
I think that will leave them all "modified"

Make them all "added" instead and see if it behaves

I do this stuff in the DB (oracle has a MERGE statement, but just define a primary key, and try the insert, if it fails, do an update instead) anyway..
 
Top Bottom