Query Group Dataset

[Chris Thunell]

I have a dataset / datatable in memory and i would like to run a query
against that to create another in memory datatable or dataview. In this
case I have a bunch of data... and i want to run a 'grouping' select query
where i just get all items grouped together for example:

Existing in memory datatable (2 fields, qty and desc):
Record 1: Qty: 23 Desc: Bananas
Record 2: Qty 10 Desc: Strawberries
Record 3: Qty 23 Desc: Bananas
Record 4: Qty 18 Desc: Grapes

I want to run a group query on field 'Desc' to give me:
Record 1: Bananas
Record 2: Strawberries
Record 3: Grapes

Any ideas? I know how to do this if the data is in an sql database.. but
how do i run a query against an in memory dataset?

Thanks in advance!
Chris
ct******@pierceassociates.com

[Felix Wang]

Hi Chris,

Thanks for posting. Is it possible that we "select" the new table directly
from the SQL Server data?

SELECT DISTINCT Desc FROM Fruit
(Here I assume the table name is "Fruit")

If not, to generate the new table from an existing DataTable as you
described, we are not able to use SQL statements directly. However, we can
try the following code to enumerate and manipulate the tables:

Dim dt As New DataTable
Dim dr As DataRow
'Define the table structure
dt.Columns.Add("Desc", GetType(System.String))
'Enumerate the original table DataSet1.Tables("Fruit")
For Each dr In DataSet1.Tables("Fruit").Rows
Dim ndr As DataRow
'Set the flag
Dim DuplicatedFound As Boolean
DuplicatedFound = False
'Enumerate the new table to check for duplicated values
For Each ndr In dt.Rows
'If duplicated value found.
If dr("Desc") = ndr("Desc") Then
'Set the flag. We may also add some additional code
here (e.g. to sum the Qty)
DuplicatedFound = True
Exit For
End If
Next
'Add the row to the new table if no duplicated found
If (Not DuplicatedFound) Then
Dim NewRow(0) As Object
NewRow(0) = dr("Desc")
dt.Rows.Add(NewRow)
End If
Next

At the end of the execution, the variable "dt" will point to the new
DataTable you need.

I hope this helps.

Regards,

Felix Wang
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.