CODEWITHSUNDEEP
vb.netdatatablemerge
vbnewbie
vbnewbie

Reputation: 226

How to Merge two DataTable with condition

I have 2 two DataTable dt_original and dt_updated. I want to merge them together with a condition, how can I merge the row only if dt_updated have the same item_id with dt_orignal?

dt_original:-

item_id|item_name|qty|unitprice|col1|col2|col3|
===============================================
1      |apple    |5  |1.00     |xxx |xxx |xxx |
2      |orange   |10 |2.00     |xxx |xxx |xxx |

dt_updated:-

item_id|item_name|qty|
======================
1      |apple    |2  |

result that I want:-

item_id|item_name|qty|unitprice|col1|col2|col3|
===============================================
1      |apple    |2  |1.00     |xxx |xxx |xxx |

MyCode:

Dim dt_original As DataTable = Model_Query(str_tableControl1)
Dim dt_result As DataTable

dt_original.PrimaryKey = New DataColumn() {dt_original.Columns("item_id")}
dt_updated.PrimaryKey = New DataColumn() {dt_updated.Columns("item_id")}
dt_original.Merge(dt_updated)
dt_result = dt_original

MyResult:-

item_id|item_name|qty|unitprice|col1|col2|col3|
===============================================
1      |apple    |2  |1.00     |xxx |xxx |xxx |
2      |orange   |10 |2.00     |xxx |xxx |xxx |

So how to solve the problem?

Upvotes: 0

Views: 312

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460058

So you want to update the quantity field? I would use LINQ and a loop:

Dim updatedRows = From rowOriginal In dt_original.AsEnumerable()
                  Join rowUpdated In dt_updated.AsEnumerable()
                  On rowOriginal.Field(Of Int32)("item_id") Equals rowUpdated.Field(Of Int32)("item_id")

Dim dt_result As DataTable =  dt_original.Clone() ' empty table, same columns 
For Each x In updatedRows
    dt_result.ImportRow(x.rowOriginal)
    dt_result.Rows(dt_result.Rows.Count-1).SetField("qty", x.rowUpdated.Field(of int32)("qty"))
Next

If you want to update the original table it's even easier:

For Each x In updatedRows
    x.rowOriginal.SetField("qty", x.rowUpdated.Field(of int32)("qty"))
Next

Upvotes: 3

Related Questions