Populate a DataTable (with Aggregation) from an existing DataTable

[Guest]

I am working in ADO.NET (C#) and am trying to derive a new DataTable based on an existing DataTable, but for the purpose of Aggregation

Let me explain

I have the following DataTable loaded in a DataSet that was populated from a Database
DataTable1
OrderNbr (int
CustNbr (int
CustName (varchar
SalesEmpNbr (int
OrderPartNbr (varchar
OrderPartPrice (decimal

And the following data loaded into the DataTable1
1, 20, Bob’s Shop, Tom, A-1, 2
1, 20, Bob’s Shop, Tom, B-1, 1
1, 20, Bob’s Shop, Tim, C-1,
2, 30, Nat’s Shop, Tom, A-1, 2
2, 30, Nat’s Shop, Tom, X-1, 5
2, 30, Nat’s Shop, Tim, Z-1, 3

So, what I am looking to do is load the following (derived) DataTable ‘DataTable2’, based on values from ‘DataTable1’
Here is the definition
DataTable dataTable2 = dsMyDataSet.Tables.Add("DataTable2")
dataTable2.Columns.Add("OrderNbr", typeof(int))
dataTable2.Columns.Add("TotOrderPrice", typeof(decimal))

Basically, this is the SQL that I would like to pass against DataTable1, to populate DataTable2
Insert Into DataTable
Values (Select OrderNbr, SUM(OrderPartPrice
From DataTable
Group By OrderNbr

So the result in DataTable2 would be as follows
1, 3
2, 10

The reason I am interested in doing this, is that I have already made a ‘round-trip’ complex SQL Statement to the DBMS once to get the specific ‘Detail Data’ (in this case for CustNbr 20 & 30), which is only selecting minimal Data out of a DBMS Table that has 800,000+ records, and includes multiple joins to this table. At this point, I already have the Detail Data that I am interested in, but now I want to aggregate the Data without ‘hitting’ the DBMS again

Any help with this would be greatly appreciated

Regards

Diabl

 

[Cor]

Hi Diablo,

When I look at what you want, I absolutly do not understand it.

However did you already look at the datatable compute method

It looks very much on what you want to do.



Cor