How To Iterate Through A Large Sql Result Set With Multiple Related Tables

I am retrieving a very large number of records with multiple result sets from SQL Server into my .NET 3.5 project. I can't just pull them all into a DataSet and work on them, sinc

Solution 1:

One way to tackle this problem is to split the data and process it in chunks, while keeping information about the last processed record.

This way, if the database connection is lost you wouldn't need to start over but you could continue at the last "checkpoint" (you could save checkpoint after every record if that makes sense).

If processing of each loaded record takes long, it would make sense to use a DataTable to download a small chunk of data into memory, process it and continue (this improves usage of db resources). But if the processing is fast and most of the time is spent downloading the data from database, then it would make more sense to use DataReader (to save memory).