CODEWITHSUNDEEP
c#.netdatatable
Prince Kumar
Prince Kumar

Reputation: 55

Merge two datatables?

I want to merge two data table in which column "ID" as primary key in both table.

Note: Both table has two column as "ID, Name" & "ID, name" where Name & name is case sensitive.

Table: 1

enter image description here

Table: 2

enter image description here

Expected Merged Table Result:

enter image description here

Code:

    public MainWindow()
    {
        InitializeComponent();

        // Table 1
        DataTable table1 = new DataTable();
        table1.Columns.Add("ID", typeof(int));
        table1.Columns.Add("Name", typeof(String));
        table1.PrimaryKey = new DataColumn[] { table1.Columns["ID"] };
        table1.Rows.Add(1, "A");
        table1.Rows.Add(4, "D");
        table1.Rows.Add(5, "E");

        // Table 2
        DataTable table2 = new DataTable();
        table2.Columns.Add("ID", typeof(int));
        table2.Columns.Add("name", typeof(String));
        table2.PrimaryKey = new DataColumn[] { table2.Columns["ID"] };
        table2.Rows.Add(1, "A");
        table2.Rows.Add(2, "B");
        table2.Rows.Add(3, "C");
        table2.Rows.Add(5, "E");

        table1.Merge(table2);
    }

Please help me to achieve this expected result.

Upvotes: 0

Views: 1250

Answers (2)

mm8
mm8

Reputation: 169420

You could temporarily change the name of the second column and still use Merge:

const string OriginalName = "name";
const string TemporaryName = "temp";
table2.Columns[OriginalName].ColumnName = TemporaryName;
table1.Merge(table2);
table1.Columns[TemporaryName].ColumnName = OriginalName;
table2.Columns[TemporaryName].ColumnName = OriginalName;

Upvotes: 1

Bluemarble
Bluemarble

Reputation: 2069

Use this:

DataTable dtResult = new DataTable();
dtResult.Columns.Add("ID", typeof(int));
dtResult.Columns.Add("Name", typeof(string));
dtResult.Columns.Add("name", typeof(string));



var result = from dataRows1 in table1.AsEnumerable()
     join dataRows2 in table2.AsEnumerable()
     on dataRows1.Field<int>("ID") equals dataRows2.Field<int>("ID") 
     into rows
     from row in rows.DefaultIfEmpty()
     select dtResult.LoadDataRow(new object[]
     {
        dataRows1.Field<int>("ID"),
        dataRows1.Field<string>("Name"),
        dataRows2.Field<string>("name")
      }, false);

Upvotes: 0

Related Questions