Switching DataViews for a single DataTable

Hello, everyone.

I'm not sure if I'm on the right road so I'll just describe what I need in the end first.

I have a data table a simplified version of which is represented below:

Code:

---

Number | Group | Date  | Client      | Amount
--------+-------+-------+--------------+--------
      1 | 220  | 28/12 | Company A    |  0.345
--------+-------+-------+--------------+--------
      2 | 220  | 29/12 | Company B    |  0.451
--------+-------+-------+--------------+--------
      3 | 240  | 26/12 | Company A    |  0.510
--------+-------+-------+--------------+--------
      4 | 240  | 27/12 | Company A    |  0.510
--------+-------+-------+--------------+--------
      5 | 220  | 30/12 | Company C    |  0.151

---

etc.

I have a default view which represents the whole table.

But I need also a different view which represents the summary of the same data.

Code:

---

Code | Amount
------+--------
  220 |  0.947
------+--------
  240 |  1.02

---

In other words, I need grouped totals.

The question is - is it possible to accomplish by preparing a dataview for the datatable and assign it to the .DefaultView property (I need a user to be able to switch between detailed and summary views).
Normally I always prepare views in a database but now I need to do it with DataTable and I simply have no experience of working with DataView class.
Can somebody show me how it's done?

I haven't used DataTables and DataViews much but as the DefaultView property is ReadOnly I don't think you could prepare another DataView and assign it to your already existing datatable. I think that the only way left is to use another DataTable. Then (I'm guessing you are using a DataGrid to display the DataTable's rows) you can simply change the DataGrid source to show the DataTable you want.

There might be better solution's though, but as I said I haven't worked with DataTables much.

Edit: Never mind, if you are using a DataGrid to display the data, you can set its DataSource using a DataView, so I guess you can simply create your "summary" DataView and set your DataGrid's DataSource to the new DataView.

I don't know of any way to automatically group and sum the data in a data table as your requirement. However, you can do it manually by create another datatable and poplutate it.
1. Get a list of distinct groups from your main datatable
2. Create a new datatable with the columns you need (code, amount)
3. Loop through the list of distinct groups in #1
a) For each group, do a mainDataTable.Select("Group = " & currentGroup)
b) Loop through the returned datarow array and add up the values in the Amount column
c) Add a new row to the datatable created in # 2
4. When the user switch to different views (the whole table or the summary only), you switch the datasource of your control accordingly.

Quote:

---

Originally Posted by stanav

I don't know of any way to automatically group and sum the data in a data table as your requirement.

---

I think you could use LINQ to do that. I'll try to get a good example tho.

Edit: here's something I think could work, didn't test it though as I'm at work. Is in C# :

C# Code:

DataTable summaryTable = from row in existingTable.AsEnumerable()
group row by row.Field<int>("Group") into grp
orderby grp.Key
select new
{
Code = grp.Key,
Amount = grp.Sum(r => r.Field<decimal>("Amount"))
}.CopyToDataTable();

Yeah, LINQ, why didn't I think of that in the first place. Then, perhaps, if the performance is poor (the datatable has about 100k records) I'll just set up an additional datatable.