c# - How to merge rows in a DataTable when data in multiple columns match?

Question

I am trying to figure out a good way to merge rows in a DataTable when column1, column2, column3, column4 match. Does anyone have pointers or ideas of how this can be accomplished in VB.NET and/or C#?

DataTable with duplicate rows to merge 
-------------------------------------------------------------
| Column1 | Column2 | Column3 | Column4 | Column5 | Column6 |
-------------------------------------------------------------
| 123456  | 6       | 54      | 5       | 0.00    | 36.78   |
| 123456  | 6       | 54      | 5       | 21.00   | 0.00    |
| 123456  | 6       | 54      | 8       | 0.00    | 102.09  |
| 123456  | 6       | 54      | 8       | 6.50    | 0.00    |


Final DataTable with merged rows 
-------------------------------------------------------------
| Column1 | Column2 | Column3 | Column4 | Column5 | Column6 |
-------------------------------------------------------------
| 123456  | 6       | 54      | 5       | 21.00   | 36.78   |
| 123456  | 6       | 54      | 8       | 6.50    | 102.09  |

Answer 1

Here is a non-LINQ alternative. What it does is iterate each row in the first table. It then checks a secondary table to see if there are any rows in it that match the criteria. If there are, it adds the values in the other columns. If there aren't, it adds the entire row to the new table.

// Clone() only clones the table structure. It does not also clone the data.
DataTable dtFinal = dtOriginal.Clone();
for (int i = 0; i < dtOriginal.Rows.Count; i++)
{
    bool isDupe = false;
    for (int j = 0; j < dtFinal.Rows.Count; j++)
    {
        if (dtOriginal.Rows[i][0].ToString() == dtFinal.Rows[j][0].ToString()
            && dtOriginal.Rows[i][1].ToString() == dtFinal.Rows[j][1].ToString()
            && dtOriginal.Rows[i][2].ToString() == dtFinal.Rows[j][2].ToString())
        {
            dtFinal.Rows[j][3] = int.Parse(dtFinal.Rows[j][3].ToString()) + int.Parse(dtOriginal.Rows[i][3].ToString()); 
            isDupe = true;
            break;
        }
    }

    if (!isDupe)
    {
        dtFinal.ImportRow(dtOriginal.Rows[i]);
    }
}

You could expand upon this to include more/less columns in your matching criteria and your addition logic. You could probably also think of something to get rid of the column number hardcoding such as iterating them up to a specific index or something. It all depends on your requirements. This should give you a decent starting point though.