How to fetch large data in batches
Posted on 2005-04-11
I have a VB.Net application (.Net Framework 1.1) which fetches data from a DB2 for iSeries database and then writes the fetched data to an SQL Server 2000. I used HitSoftware's Ritmo for iSeries to connect to DB2. The approach I did is to use ADO.Net DataSet.GetXml() which output I pass to a stored procedure for processing (INSERT).
This works ok except when the DataSet is so large I actually get the out of memory exception.
Here are my other challenges:
- I need to know how I can fetch large data in batches using ADO.Net. SQL Server's DTS for example can fetch large data 1000 records at a time. I want to know if this is possible with ADO.Net.
- I need to achieve "all or nothing" which means if there are 50,000 records, I can fetch 1000 at a time untill all 50,000 records are fetched and processed. If I encounter a problem along the way, I can rollback on my SQL Server 2000.
- I need to achieve all these without requiring changes to DB2.
If this is not possible using ADO.Net, does anyone know of something (API or component) I can use in my VB.Net app?