Merging DataTables with Condition

Question

I would like to merge DataTables in a list using sum or average depending on conditions. For example:

private DataTable getData(List<DataTable> datas, string[] KeyColumnNames, string valueCol)
{
    List<DataTable> dataTables = datas;
    //if datas has 3 dataTables in it : dt1, dt2, dt3
    // then I want to create another dataTable dtAll which will have the sum of 
    // valueCol of all three datatables for the row which  will be conditioned using 
    // KeyColumnNames (can be multiple Primary keys)
    return dataTable;
}

Consider all datatables to be exactly same but different values as they are tables from similar schemas but different data centers. Thanks.

Consider using LINQ it has ability to aggregate as well as union. — Yuriy Galanter, Jun 04 '13 at 21:43

MoonKnight · Accepted Answer · 2013-06-04T22:15:32.773

I would do

List<DataTable> dataTableList = dtList;
DataTable unionDataTable = new DataTable();
for(int i = 0; i < dtList.Count; i++)
    unionDataTable = Union(unionDataTable, dtList[i], "UnionDataTable");

where the Union method is defined by something like the following

public static DataTable Union(DataTable First, DataTable Second, string strReturnedTableName)
{
    // Result table.
    DataTable table = new DataTable(strReturnedTableName);

    // Build new columns.
    DataColumn[] newcolumns = new DataColumn[First.Columns.Count];
    for (int i = 0; i < First.Columns.Count; i++)
        newcolumns[i] = new DataColumn(First.Columns[i].ColumnName, First.Columns[i].DataType);

    // Add new columns to result table.
    table.Columns.AddRange(newcolumns);
    table.BeginLoadData();

    // Load data from first table.
    foreach (DataRow row in First.Rows)
        table.LoadDataRow(row.ItemArray, true);

    // Load data from second table.
    foreach (DataRow row in Second.Rows)
        table.LoadDataRow(row.ItemArray, true);

    table.EndLoadData();
    return table;
}

I hope this helps.

Edit. You can pass an Action in to the method and use this in the foreach blocks to do what you want.

this will just merge the data, I need to perform operations on data. Something like column value of unionDataTable will have sum or average of values from all the tables. — Ratan, Jun 04 '13 at 22:03
See edit. I can't write the entire code for you but the above should be a good start... — MoonKnight, Jun 04 '13 at 22:15

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

Your question is way too vague to provide any actual code but you want to do a LINQ Join. It works about the same as a SQL join. This question shows joins in both query and method syntax How to do a join in linq to sql with method syntax? also you can look at the msdn docs here; http://msdn.microsoft.com/en-us/library/bb311040.aspx

edited May 23 '17 at 10:25

Community

1
1

answered Jun 04 '13 at 21:42

evanmcdonnal

46,131
16
104
115

Merging DataTables with Condition

2 Answers2