Matthew,
Unless there's some special function on the MM.NET framework, ADO.NET does not support set-based operations (i.e. full SQL statements) against datasets/datatables.
So you can't really apply full SQL syntax against ADO.NET result sets. This was a big surprise to me when I came across this awhile back.
Here are your options (and these not not mutually exclusive)...
1) Construct your initial SQL statements to bring back just the filtered data you want.
2) ADO.NET does have some capabilities for filtering (like filtering on all datarows in a datatable where State = 'PA', etc.) and summing. It's not full-blown SQL, but I've found it does help some.
3) There is a technique that Kamel Patel demonstrated in the UT magazine, well over a year ago. It involves the following...
- passing a dataset as XML to a FoxPro function
- converting it from XML to a cursor in the fox function
- performing your 'second query' in the fox function
- writing the result of the 2nd query back to XML
- reading it back in as a dataset
Of course, you are introducing code 'outside the CLR' to do this last one, though depending on where the code resides, it may not be a major issue.
HTH,
Kevin