Dataset Aggregrate

[Mongr1l]

Is there a way to do an sql aggregrate on a dataset?

Something like "Select Category, Product, Sum(cost) from Products group by category, Product"?

I know that DataViews have rowfilter and sort, but I'd like something more direct?


Ideas?

mongr1l

 

[cjelec]

Short answer, No.

Now for the long answer:

You can in a way group tables like sql, but you have to setup the dataset to handle it for you. There are two ways i know of, and only one i use, XML Schema's.

But, are you getting the data from sql or simular database? if so, then its not really going to work.

Add a xml schema file to your project, when its open, you can drag/drop the tables you want and type in the columns.

Now, when you have finished, drag a child table to an parent one: Products(Child) -Into> Categories(parent). Save the file (you may need to copy it into the Bin folder)

In your code, where you have your dataset decliration, use:
ds.ReadXMLSchema(XMLSchema_FileName)
to load the schema

Then when you add a product(example), you ref the category:

Dim NewProductRow as DataRow = DataSet.Tables("Products").NewRow

NewProductRow.SetParentRow(CategoryRow)
...

Then when you want all products under 'Toys' you:

Dim ToyProducts() As DataRow = DataSet.Table("Categories").Select("Name = 'Toys')(0).GetChildRows("Categories_Products")

Hope this makes some sense?

 

[mansii]

Actually, there is an aggregate function in datatable object:

Dim o As Object = ds.Tables(0).Compute("sum(cost)", "category='XXX'")

Unfortunately, it seems that we can only get one value in a time.

Good luck.