Suppose I run a query against SQL Server to bring back data for a report. Let's say I bring back 1,000 financial deductions (from a database that might have a million records or more.)

I may have a need to get a 2nd result set as well - maybe to summarize the result set on deduction balance $$$ by account. With both, I can display a report with both the details and subtotals.

In VFP, I'd run a subsequent SQL SELECT with a GROUP BY. Unfortunately, you can't do set-based queries on a dataset.

It seems I have the following options:

1) For every report where I need to do this, I'd have to write custom code in C# to create a dataset, where I can through the first result set and create summaries manually into a new dataset.

2) Take the initial detailed result set that I pulled down, and push it back into SQL so that I can run the 2nd query (the GROUP BY) based on the temp table that I pushed back into SQL.

If I knew how to run a query with the data adapter such that SQL would actually 'retain' the results of the inital query (the 1,000 records) as a temp table, then I wouldn't need to push the 1,000 records back into SQL.

Personally, I'd like to use the methodology that was described in last month's UT magazine (a VFP COM utility that takes a dataset [XML] and converts it to a cursor, runs a query, then converts the result back to XML).

Unfortunately, I work with several who are complete 'managed code snobs' and thumb their noses at COM ["it's not managed code, if we have scaling issues then we have to use MTS, blah blah blah").


Maybe there's a simpler solution to this (being able to run a subsequent query against a dataset)?

Thanks,
Kevin