Wednesday, April 19, 2006

Merge DataTable in C#

I used to frequently need a function to merge two DataTables based on a particular column. So made up a generic function.

public static DataTable MergeTables(DataTable dtFirst, DataTable dtSecond,string CommonColumn)
{
DataTable dtResults = dtFirst.Clone();
int count=0;
for(int i=0;i{
if(!dtFirst.Columns.Contains(dtSecond.Columns[i].ColumnName))
{
dtResults.Columns.Add(dtSecond.Columns[i].ColumnName,dtSecond.Columns[i].DataType);
count++;
}
}
DataColumn []columns = new DataColumn[count];
for(int i=0,j=0;i{
if(!dtFirst.Columns.Contains(dtSecond.Columns[i].ColumnName))
{
columns[j++] = new DataColumn(dtSecond.Columns[i].ColumnName,dtSecond.Columns[i].DataType);
}
}
dtResults.BeginLoadData();
foreach(DataRow dr in dtFirst.Rows)
{
dtResults.Rows.Add(dr.ItemArray);
}
foreach(DataRow dr in dtSecond.Rows)
{
foreach(DataRow dr1 in dtResults.Rows)
{
if(dr1[CommonColumn].Equals(dr[CommonColumn]))
{
foreach(DataColumn c in columns)
{
dr1[c.ColumnName] = dr[c.ColumnName];
}
}
}
}
dtResults.EndLoadData();
return dtResults;
}