how to convert a datatable using a reference datatable asp.net

Question

I am finding difficulties to convert a datatable to a new datatable using a reference datatable. My question is confusing and I am not good at explaining things so I drew a picture (see below).

I have two datatables on memory and I need to create the third datatable using the second mapping table for reference. The column names are just for example and it can't be hardcoded.

Hope someone can help me. Thanks a lot.

Answer 1

This may not be the most optimized code, but it seems to work ... Basically make a new DataTable using the column names from the "New Columns" column of the mapping table, then for each row in the first table, step through the mapping table, storing the values of the "Old Columns" columns in the "New Columns" columns

Protected Sub MapData()

    Dim table1 = New DataTable()
    Dim table2 = New DataTable()
    Dim table3 = New DataTable()

    With table1
        .Columns.Add("Fore Name")
        .Columns.Add("Sir Name")
        .Columns.Add("Date of Birth")
        .Columns.Add("Country")

        Dim newRow = .NewRow()
        newRow("Fore Name") = "AA"
        newRow("Sir Name") = "AA"
        newRow("Date of Birth") = "01.01.1999"
        newRow("Country") = "UK"
        .Rows.Add(newRow)
        ' etc
    End With

    With table2
        .Columns.Add("Old Columns")
        .Columns.Add("New Columns")

        Dim newRow = .NewRow()
        newRow("Old Columns") = "Fore Name"
        newRow("New Columns") = "First Name"
        .Rows.Add(newRow)

        newRow = .NewRow()
        newRow("Old Columns") = "Sir Name"
        newRow("New Columns") = "Last Name"
        .Rows.Add(newRow)

        newRow = .NewRow()
        newRow("Old Columns") = "Date of Birth"
        newRow("New Columns") = "DOB"
        .Rows.Add(newRow)
    End With

    For Each rowData As DataRow In table2.Rows
        table3.Columns.Add(rowData("New Columns"))
    Next

    For Each table1Data As DataRow In table1.Rows
        Dim newRow = table3.NewRow()

        For Each rowMap As DataRow In table2.Rows
            newRow(rowMap("New Columns")) = table1Data(rowMap("Old Columns"))
        Next

        table3.Rows.Add(newRow)
    Next

End Sub

Answer 2

Try this but this is in c# you can convert it to vb

targetTable.Columns["forename"].Caption = "First Name";// Rename the column
targetTable.Columns["SirName"].Caption = "Last Name";// Rename the column
targetTable.Columns["DateofBirth"].Caption = "DOB";// Rename the column
targetTable.Columns["country"].Table.Columns.Remove("country");//this will remove the column

//reorder column in case you need it
// targetTable.Columns["First Name"].SetOrdinal(0);
// targetTable.Columns["Last Name"].SetOrdinal(1);
// targetTable.Columns["DOB"].SetOrdinal(2);

// newtable =targettable.copy();// this will copy everthing to newtable  

let me know if it didnt help