Paging through a datatable in codebehind

I need to handle very large datatables (2 million rows+) that comes from databases (SQL, Oracle, Access, MySQL, Sharepoint etc) outside of my control: Currently I loop through every row and column building a string object, but I run out of memory at about 100k rows.

The only solution I may take is to break the datatable into smaller pieces and persisting each block before starting on the next block of rows.

Since I cannot add ROW_NUMBER() or anything similar, I have to handle the populated datatable.

How can I easily (keep performance in mind) break the populated datatable into smaller datatables like paging开发者_高级运维?

PS there is no visual component to this functionality.

Are you using string concatenation? like this string += string.

Change that to StringBuilder and you should not have problems, at least not for 20k rows.

If you are talking about filling a DataTable object (which loads the results of your calls into memory before processing), you will likely be better off using a datareader for each of the mentioned providers so then you can process each row as it is read from the database instead of storing the DataTable in memory...

A great answer to another question lists the pro/cons of datareaders/datatables

If you're already using datareaders- ignore this. But your memory problem might be from also storing the retrieved results...