I have visual fox pro database with thousands of rows. I am using oledb to fetch data from fox pro and export(after doing some calculation and fomatting) it to sql server.I have used dataset to populate 2-3 datatable(related table) at a time.
First problem is that the memory use is very high because the dataset are huge.I want to reduce memory footprint.Any suggestion for this.
So i have decided to fetch few rows at a time.How do i fetch rows using oledb command so that i can fetch for eg 1-20 and then 20-40 etc
Based on all the comments and HatSoft's code here is what I think you are looking for. This is pseudo-code, whcih means it will not compile but should give you a good idea of where to go from here.
int NumberOfRecordsToRetrieve = 10000;
int StartRecordNumber = 1;
bool EndOfFile = false;
string queryString = "SELECT OrderID, CustomerID FROM Orders WHERE RECNO() BETWEEN @StartRecordNumber AND @EndRecordNumber";
While (!EndOfFile)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand command = new OleDbCommand(queryString, connection);
command.Parameters.Add(new OleDbParameter("@StartRecordNumber", StartRecordNumber));
command.Parameters.Add(new OleDbParameter("@EndRecordNumber", StartRecordNumber + NumberOfRecordsToRetrieve));
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
EndOfFile = true;
while (reader.Read())
{
EndOfFile = false
//Retrieve records here and do whatever process you wish to do
}
reader.Close();
StartRecordNumber += NumberOfRecordsToRetrieve;
}
}