Delphi ADOQuery result concatenation using ClientDataSet

Question

I have written an application that allows a user to define a query, run it using a TADOQuery component, and save the report to their PC as an XML document (SaveAsFile passing in pfXML as the Format parameter) for offline viewing. This works fine.

What I now need to do is concatenate the results. What I mean by this is that I need to get at least two reports (with identical fields as they are using the same query) and join them together to make one report. For example: Say I have a query that retrieves a list of users. I run this query on "System A" and "System B" where System A and System B are two separate systems. Once I have run the query on both systems, I have two separate report files, which I can load back into a TADOQuery component and then view on screen. What I need to do though is join the reports together, so instead of having to switch between the two reports, I can see all the results on screen at the same time.

I have been told that I can do this using a TClientDataSet but I'm relatively new to database programming and have no idea where to start with this. I've checked out the articles at http://delphi.about.com/od/usedbvcl/a/tclientdataset.htm but I'm still in the dark as nothing seems to do exactly what I need it to do.

I therefore have two questions: 1. Is using a TClientDataSet the best way to take two separate result sets and concatenate them? If there is a better way to do this, what is it? 2. If using a TClientDataSet is the best way to do this, how exactly would I go about doing this? How do you transfer data from a TADOQuery into a TClientDataSet and then back again? The basic flow of the processing needs to go something like this:

Report 1 (LoadFromFile) -> TADOQuery \

                                  ClientDataSet -> TADOQuery -> SaveToFile

Report 2 (LoadFromFile) -> TADOQuery /

All help would be appreciated on this. I really don't know where to start!

Answer 1

I've just about finished looking into this. My application allows the user to generate reports by querying their databases. I can get this to work and it is very efficient for small result sets - however, as this is a reporting application, and it's entirely possible that hundreds of thousands of records can be returned, using a ClientDataSet gives massive performance problems. Once you get above around 50,000 records (reasonable, given the customer base), processing starts to increase exponentially, so this is now basically moot.