Join multiple DataRows into a single DataRow

Question

I am writing this in C# using .NET 3.5. I have a System.Data.DataSet object with a single DataTable that uses the following schema:

Id      :  uint
AddressA:  string
AddressB:  string
Bytes   :  uint

When I run my application, let's say the DataTable gets filled with the following:

1   192.168.0.1   192.168.0.10   300
2   192.168.0.1   192.168.0.20   400
3   192.168.0.1   192.168.0.30   300
4   10.152.0.13   167.10.2.187    80

I'd like to be able to query this DataTable where AddressA is unique and the Bytes column is summed together (I'm not sure I'm saying that correctly). In essence, I'd like to get the following result:

1   192.168.0.1   1000
2   10.152.0.13     80

I ultimately want this result in a DataTable that can be bound to a DataGrid, and I need to update/regenerate this result every 5 seconds or so.

How do I do this? DataTable.Select() method? If so, what does the query look like? Is there an alternate/better way to achieve my goal?

EDIT: I do not have a database. I'm simply using an in-memory DataSet to store the data, so a pure SQL solution won't work here. I'm trying to figure out how to do it within the DataSet itself.

Answer 1

most efficient solution would be to do the sum in SQL directly

select AddressA, SUM(bytes) from ... group by AddressA

Answer 2

I agree with Steven that the best way to do this is to do it in the database. But if that isn't an option you can try the following:

• Make a new datatable and add the columns you need manually using DataTable.Columns.Add(name, datatype)
• Step through the first datatables Rows collection and for each row create a new row in your new datatable using DataTable.NewRow()
• Copy the values of the columns found in the first table into the new row
• Find the matching row in the other data table using Select() and copy out the final value into the new data row
• Add the row to your new data table using DataTable.Rows.Add(newRow)

This will give you a new data table containing the combined data from the two tables. It won't be very fast, but unless you have huge amounts of data it will probably be fast enough. But try to avoid doing a LIKE-query in the Select, for that one is slow.

One possible optimization would be possible if both tables contains rows with identical primary keys. You could then sort both tables and step through them fetching both data rows using their array index. This would rid you of the Select call.

Answer 3

I agree with Steven as well that doing this on the server side is the best option. If you are using .NET 3.5 though, you don't have to go through what Rune suggests. Rather, use the extension methods for datasets to help query and sum the values.

Then, you can map it easily to an anonymous type which you can set as the data source for your grid (assuming you don't allow edits to this, which I don't see how you can, since you are aggregating the data).

Answer 4

For readability (and because I love it) I would try to use LINQ:

var aggregatedAddresses = from DataRow row in dt.Rows
group row by row["AddressA"] into g
select new {
    Address = g.Key,
    Byte = g.Sum(row => (uint)row["Bytes"])
};

int i = 1;
foreach(var row in aggregatedAddresses)
{
    result.Rows.Add(i++, row.Address, row.Byte);
}

If a performace issue is discovered with the LINQ solution I would go with a manual solution summing up the rows in a loop over the original table and inserting them into the result table.

You can also bind the aggregatedAddresses directly to the grid instead of putting it into a DataTable.