Wednesday, April 22, 2015

Merge Two DataTables Into one DataTable in C#

Merge Two DataTables Into one DataTable in C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("EmpID", typeof(int));
            dt1.Columns.Add("ColX", typeof(int));
            dt1.Columns.Add("ColY", typeof(int));

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("EmpID", typeof(int));
            dt2.Columns.Add("ColZ", typeof(int));

            DataRow row;
            for (int i = 1; i <= 2; i++)
            {
                row = dt1.NewRow();
                row["EmpID"] = i;
                row["ColX"] = 10 + i;
                row["ColY"] = 20 + i;
                dt1.Rows.Add(row);
            }
            for (int i = 1; i <= 5; i++)
            {
                row = dt1.NewRow();
                row = dt2.NewRow();
                row["EmpID"] = i;
                row["ColZ"] = 30 + i;
                dt2.Rows.Add(row);
            }
            var lst = from table1 in dt1.AsEnumerable()
                      join table2 in dt2.AsEnumerable() on (int)table1["EmpID"] equals (int)table2["EmpID"]
                      select new
                     {
                         EmpID = (int)table1["EmpID"],
                         ColX = (int)table1["ColX"],
                         ColY = (int)table1["ColY"],
                         ColZ = (int)table2["ColZ"]
                     };
            DataTable targetTable = new DataTable();
            var dt1Columns = dt1.Columns.OfType<DataColumn>()
                .Where(dc => dc.ColumnName != "ColX")
                .Select(dc => new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
            var dt2Columns = dt2.Columns.OfType<DataColumn>()
                .Where(dc => dc.ColumnName != "EmpID")
                .Select(dc => new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
            targetTable.Columns.AddRange(dt1Columns.ToArray());
            targetTable.Columns.AddRange(dt2Columns.ToArray());         
            foreach (var item in lst)
            {
                DataRow dr = targetTable.NewRow();
                dr["EmpID"] = item.EmpID;
                dr["ColY"] = item.ColY;
                dr["ColZ"] = item.ColZ;
                targetTable.Rows.Add(dr);
            }
        }
    }
}

No comments:

Post a Comment