Dataset problem

[georgepatotk]

Dear all,

 

I am wondering whether my approach is applicable in .net.

 

I have created a dataset to store temporay datas as shown below:

ID, Name, Age, Sex, Address

 

So, As for usual, I want to sort it in the order of Age, Sex, Name, then Address.

 

 

How do I do so?

In SQL query, we usually do it as ORDER BY Age, Sex, Name, Address

 

Thanks in advance

[georgepatotk]

the other similar question is:

 

How do I do GROUP BY for dataset?

 

For example: GROUP BY Age, Sex

[Ido]

use the dataview class:

for EXAMPLE:

 

'create a DataView and have it sorted by the "amount" column
Dim myDataView as DataView = ds.tables("products" ).defaultview
myDataView.Sort = "amount"
       
'Finally, bind the DataView to the DataGrid
dgRandOrder.DataSource = myDataView
dgRandOrder.DataBind()    

[Arch4ngel]

For better understanding... here is more info.

 

The sort property act like this ... is contain a string that specify fields to sort by. The correct format is like this "age,sex, name, address". You could add a ASC or a DESC to specify if you want them ascending or descending.

 

However... group by isn't supported in my knowledge... please light my way on this one if someone know more than me about it.

 

Here is your answer young padawan.

[georgepatotk]

so, i shtat means I should do like this:

 

myDataView.Sort = "Age, Sex, Name ASC" ?

[Ido]

so, i shtat means I should do like this:

 

myDataView.Sort = "Age, Sex, Name ASC" ?

 

 

Yes.

 

In addition. DataView has another usefull property: RowFilter which filters rows in the dataview. For example:

 

dataview1.Sort = "amount DESC"  '=> or ASC
dataview1.RowFilter = "price > 50.00"
datagrid1.datasource = dataview1
datagrid1.databind

 

Goodluck!

[Nerseus]

Depending on what you want to do with the filtered/sorted data, you might use a DataView (if you need binding, mostly) or a DataRow[] select (faster, good for looping and doing stuff with each row). An example of each:

DataView dv = new DataView(ds.Tables[0], "Age >= 18 and Age <=30", "Age DESC, Sex ASC, Name ASC", DataViewRowState.CurrentRows);

// The default row types to be returned by "Select" are DataViewRowState.CurrentRows
DataRow[] rows = ds.Tables[0].Select("Age >= 18 and Age <=30", "Age DESC, Sex ASC, Name ASC");

 

The sort can have ASC or DESC for each field: "Age DESC, Sex ASC, Name ASC". Fields default to ASC so you don't really have to specify that for Sex and Name above.

 

The filter works great, but it's more like the WHERE clause than the Group By.

 

A DataTable has a Compute method that can take aggregates, including filters. For example:

object sum = ds.Tables[0].Compute("AVG(Age)", "Age >= 18 and Age <= 30");

 

The Compute method returns an object because, if I remember right, it might return System.DBNull.Value.

 

While the Compute method is close to a Group By, it's not the same and there is no direct equivalent that I can think of. For example, a Group By allows you to get an ID and a count at the same time, including a HAVING clause:

SELECT CustomerNumber, count(*) FROM Customer GROUP BY CustomerNumber HAVING sum(NumSales) > 10

(completely made up query)

 

To do this in .NET you'd need a Select for each CustomerNumber (if you want unique you'd have to work at it :)) and then use a Compute on each of those individually.

 

You CAN have an expression column in a DataTable and it contain contain aggregate functions, but it would only give you what you wanted if you split the table into two tables. In the dummy query above, you'd have to put the CustomerNumber in one table and the NumSales field in a child table. Then you could add an expression column to the parent table that SUMmed up the NumSales column. If that's not how your tables are setup, that may not be the easiest thing to do.

 

Generally, if you want to do anything with aggregate functions or GROUP BY you normally try doing that in SQL and returning the result. Or, if you're using a Grid, have the grid do the summing (many 3rd party grids provide robust grouping/summing options).

 

-nerseus