c# - Merging 2 datatables in to 1 datatable with same number of rows.

Question

How can i merge two Datatables into the same row. I am using different stored procedures to get data into datasets. In asp.net using c#, i want to merge them so there are same number of rows as table 1 with an added column from table 2.

For example:

DataTable table1 = dsnew.Tables[0];
DataTable table2 = dsSpotsLeft.Tables[0];
table1.Merge(table2);

This is fetching me 4 rows instead of 2 rows. What am i missing here? Thanks in advance!!

Answer 1

You cannot use the method Merge in this case, instead you should create new DataTable dt3, and then add columns and rows based on the table 1 and 2:

var dt3 = new DataTable();

var columns = dt1.Columns.Cast<DataColumn>()
                  .Concat(dt2.Columns.Cast<DataColumn>());

foreach (var column in columns)
{
    dt3.Columns.Add(column.ColumnName, column.DataType);
}

//TODO Check if dt2 has more rows than dt1...
for (int i = 0; i < dt1.Rows.Count; i++)
{
    var row = dt3.NewRow();
    row.ItemArray = dt1.Rows[i].ItemArray
                       .Concat(dt2.Rows[i].ItemArray).ToArray();

    dt3.Rows.Add(row);
}