Tuesday, November 3, 2009

Perform UNION on Datatables in LINQ

hi.
some times we need to merge two tables in C#.we can use merge method of Data table.but if you have complex data and you want to perform some editional operations like sorting etc. before merging them you can use LINQ to dataset.

you can see working example below.
Table-1:

public DataTable GetTable1()
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("BirthDate");
dt.Rows.Add("1", "Herry", "Thu, 18 Jan 2007 11:00:00 GMT");
dt.Rows.Add("1", "Mac", "Thu, 19 Jan 2007 11:00:00 GMT");
dt.Rows.Add("1", "Wisly", "Thu, 20 Jan 2007 11:00:00 GMT");
return dt;
}


Table-2:

public DataTable GetTable2()
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("BirthDate");
dt.Rows.Add("1", "July", "1/19/2009 4:28:24");
dt.Rows.Add("1", "Anna", "1/20/2009 4:28:24");
dt.Rows.Add("1", "Jeniffer", "1/21/2009 4:28:24");
return dt;
}

Now you can join both tables using below code.

DataTable tmpDatatable = GetTable1().Clone();
var all = (from boys in GetTable1().AsEnumerable()
orderby boys["BirthDate"] descending
select boys).Union(from girls in GetTable2().AsEnumerable()
orderby girls["BirthDate"] descending
select girls);
all.CopyToDataTable(tmpDatatable, LoadOption.OverwriteChanges);
grvAll.DataSource = tmpDatatable;
grvAll.DataBind();

Thanks.