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

Featured Post

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management https://docs.microsoft.com/en-us/azure/api-management/api-...

Popular posts