Compare and synchronize DataTables

Question

I need to Compare Two dataTables.
dataTable A contains current set of Data on clients machine.
dataTable B contains future updates to dataTable A.

dataTable A structure
ID | firstname  | lastName
 1 | "test"     | "last"
 2 | "whatever" | "someone"
 3 | "hi"       | "hello

dataTable B Structure
ID | firstname  | lastName
 1 | "updated"  | "yes"
 2 ->deleted
 3 | "hi"       | hello" ->unchanged
 4 | "new"      |record " ->row added

When I go dataTableA.merge(datatableB)

I basically just get dataTableA with dataTableB added rows so for example

ID | firstname  | lastName
 1 | "test"     | "last"
 2 | "whatever" | "someone"
 3 | "hi"       | "hello
 1 | "updated"  | "yes"
 3 | "hi"       | hello" ->unchanged
 4 | "new"      |record " ->row added

It doesn't match on the IDs and get updated or deleted. I just want to compare two tables, update table A that should look exactly like table B. I'm not quite sure how to accomplish this properly.

Basically there is a SQL table in the clients machine that needs to get completed updated and sync exact to a datatable B that is being passed in. In theory i just want to take table B and basically update table A. So after I need to update the SQL table. I tried something like this.

Dim adapter = New SqlDataAdapter("select * from test_table2", connection)
Using (New SqlCommandBuilder(adapter))
    adapter.Fill(dTable)
    connection.Open()
    adapter.Update(dTable)
End Using

Doesn't seem to work.

Answer 1

If the datatables are supposed to end up identical, instead of using

dataTableA.merge(datatableB)

Try

dataTableA=datatableB.Copy

Got this info from here https://msdn.microsoft.com/en-us/library/system.data.datatable.copy(v=vs.110).aspx

Answer 2

Your both datatables are being created separately, independent of each other. In this context you can use following DataTable and LINQ operations such as

• Intersect to find matching rows in both DataTables,
• Except to find unique rows only in one table
• ImportRow to copy specific rows of one DataTable to another.
• Copy to copy an entire datatable into another

It could be like this

Dim dtA As DataTable 'Your original Table A
Dim dtB As DataTable 'Your modified Table B
Dim dtC As DataTable 'Unchanged Rows
Dim dtD As DataTable 'Final synchronized Table
Dim dtE As DataTable 'Deleted Rows

'Populate your Table A and Table B into dtA and dtB

'get unchanged rows into dtC (all rows - changed - deleted)
dtC = dtA.AsEnumerable.Intersect(dtB.AsEnumerable, DataRowComparer.Default).CopyToDataTable

'Copy all unchanged rows to final table
dtD = dtC.Copy

'Copy the structure to blank datatable
dtE = dtC.Clone

'process modified rows (changed + deleted) i.e. (all rows - unchanged rows)
For Each cdRow As DataRow In dtA.AsEnumerable.Except(dtC.AsEnumerable, DataRowComparer.Default).CopyToDataTable.Rows
    'check if this row exists in modified rows
    If dtB.Select("cid = " & cdRow.Item("cid").ToString).Count > 0 Then
        'This is a modified row copy it to the final table or process it to database
        dtD.ImportRow(dtB.Select("cid = " & cdRow.Item("cid").ToString).CopyToDataTable.Rows(0))
    Else
        'This is a deleted row copy it to the deleted records table or process it directly to the database
        dtE.ImportRow(cdRow)
    End If
Next
'Now dtE contains your deleted rows
'Finally dtD is your synchronized datatable

Assumptions and other details:

• All DataTables should have same table structure
• This code assumes that ID column is unique to identify new and old rows.
• You will still have to process deleted rows (dtE) from original database.
• DataTable dtD, dtE can be avoided, but just for the purpose of simplicity and better understanding of the concept these are included.
• Instead of later processing all records in dtD and dtE in a separate loop you can directly update them to the database as mentioned in code above.
• There may be memory, resources, processing/timing related issues in case you are processing large DataTables