Merging rows from two DataTables into a new table with more columns if there is no common information.

Introduction

This started as an answer to a QA question.

If you have two DataTables:

Name      Address 
Joe       At home
Mike      Over there

Mobile    Favorite Cheese
999       Stilton
911       In a can

And you want to combine them into a single DataTable:

Name      Address      Mobile    Favorite Cheese
Joe       At home      999       Stilton
Mike      Over there   911       In a can

If they have no common information, then you have done something very wrong in your design!

If they have common information, then a simple Linq Join would do it.

But without? That's nasty...but it can be done, on a row-by-row basis

Using the code

The code isn't complex - it's just brute force and ignorance really:

private DataTable MergeColumns(DataTable dt1, DataTable dt2)
    {
    DataTable result = new DataTable();
    foreach (DataColumn dc in dt1.Columns)
        {
        result.Columns.Add(new DataColumn(dc.ColumnName, dc.DataType));
        }
    foreach (DataColumn dc in dt2.Columns)
        {
        result.Columns.Add(new DataColumn(dc.ColumnName, dc.DataType));
        }
    for (int i = 0; i < Math.Max(dt1.Rows.Count, dt2.Rows.Count); i++)
        {
        DataRow dr = result.NewRow();
        if (i < dt1.Rows.Count)
            {
            for (int c = 0; c < dt1.Columns.Count; c++)
                {
                dr[c] = dt1.Rows[i][c];
                }
            }
        if (i < dt2.Rows.Count)
            {
            for (int c = 0; c < dt2.Columns.Count; c++)
                {
                dr[dt1.Columns.Count + c] = dt2.Rows[i][c];
                }
            }
        result.Rows.Add(dr);
        }
    return result;
    }

The only real complexity is that you need to create duplicate columns in the new table because teh framework won;t let you "copy" a column if it's already in a DataTable.

Points of Interest

The code isn't written to be that efficient - you could probably improve it quite a bit just with simple loop optimisations - but if you are combining tables which don't share common info, then you deserve some pain! :laugh:

History

2015-06-06   First version.