VB.Net Merging datatables not obeying primary key constraints-CodePudding

I'm having difficulty using the DataTable.Merge method and preventing rows from duplicating. I have multiple datatables that I need to merge together into one, including duplicate columns that need to get merged.

My datatables look like this:

PID|140      PID|140          PID|144          PID|142
 1 | X         1|               1|               1|X
 2 |           2|X              2|X              2|X
 3 |           3|               3|X              3|X

When I merge them it creates duplicate row values.

What I get:                      What I actually want:
PID|140|144|142                  PID|140|144|142
 1 | X |   |                      1 | X |   | X  
 2 |   |   |                      2 | X | X | X
 3 |   |   |                      3 |   | X | X
 1 |   |   |
 2 | X |   |
 3 |   |   | 
 1 |   |   |
 2 |   | X |
 3 |   | X | 
 1 |   |   | X
 2 |   |   | X
 3 |   |   | X

I'm setting the primary key and also using addWithKey on the schema options, but it seems to ignore the primary key constraints when merging.

  'writeTable being the destination of the merges'
  Dim primaryKey(1) As DataColumn
  primaryKey(1) = writeTable.Columns("PID")
  writeTable.PrimaryKey = primaryKey
  '...merging from a selected dataview to table'
      selected = view.ToTable("Selected", False,"PID","144")
      primaryKey(1) = selected.Columns("PID")
      selected.PrimaryKey = primaryKey
      writeTable.Merge(selected, False, MissingSchemaAction.AddWithKey)

CodePudding user response：

The Merge is meant to update a DataTable with changes from a database. When you Merge writeTable with selected with the second parameter as False, you get the new values from selected, 1,Nothing 2,X 3,Nothing. Each value is different from the intitial value so the intial values are overwritten. When the second parameter is True, you get 1,X 2,Nothing 3,Nothing. The initial values are preserved. If you want to have both 1 and 2 showing X in the 140 column you will have to filter the selected table to exclude nulls in the 140 column. Then only the non-null values in selected will overwrite the initial values. I did it like this

    Dim NonNullselected = selected.Select($"[140] Is Not Null").CopyToDataTable

The brackets around 140 are necessary. Field that begins with a number has to be dealt with.

I believe your main problem is assigning the primary key.

Dim primaryKey(1) As DataColumn

This creates an array of DataColumn with 2 elements, indexes 0 and 1.

primaryKey(1) = writeTable.Columns("PID")

This adds a column to the second element of the array. The first element is Nothing.

writeTable.PrimaryKey = primaryKey

I can see how this is not successfully creating the PK.

I did it like this

    dt.PrimaryKey = {dt.Columns(0)}

For your code that would be

    writeTable.PrimaryKey = {writeTable.Columns("PID")}

The braces indicate array which is what is expected so the Property can handle compound keys.

Private writeTable As DataTable
Private selected As DataTable
Private Table3 As DataTable
Private Table4 As DataTable

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    CreateAndFillTables()
    Dim NonNullselected = selected.Select($"[140] Is Not Null").CopyToDataTable
    writeTable.Merge(NonNullselected, False, MissingSchemaAction.AddWithKey)
    writeTable.Merge(Table3, False, MissingSchemaAction.AddWithKey)
    writeTable.Merge(Table4, False, MissingSchemaAction.AddWithKey)
    DataGridView1.DataSource = writeTable
End Sub

Private Sub CreateAndFillTables()
    writeTable = CreateTable("140")
    writeTable.Rows.Add({1, 0})
    writeTable.Rows.Add({2})
    writeTable.Rows.Add({3})
    selected = CreateTable("140")
    selected.Rows.Add({1})
    selected.Rows.Add({2, 0})
    selected.Rows.Add({3})
    Table3 = CreateTable("144")
    Table3.Rows.Add(1)
    Table3.Rows.Add(2, 0)
    Table3.Rows.Add(3, 0)
    Table4 = CreateTable("142")
    Table4.Rows.Add(1, 0)
    Table4.Rows.Add(2, 0)
    Table4.Rows.Add(3, 0)
End Sub

Private Function CreateTable(SecondColumnName As String) As DataTable
    Dim dt As New DataTable
    dt.Columns.Add("PID", GetType(Integer))
    dt.Columns.Add(SecondColumnName, GetType(Integer))
    dt.PrimaryKey = {dt.Columns(0)}
    Return dt
End Function

Result