oledb, visual fox pro and c#

Question

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

score 1 · Answer 1 · answered Jul 31 '12 at 09:31

1

string queryString = "SELECT OrderID, CustomerID FROM Orders WHERE ORDERID >= @StartOrderID AND ORDERID <= @EndOrderID";

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        OleDbCommand command = new OleDbCommand(queryString, connection);
        command.Parameters.Add(new OleDbParameter("@StartOrderID", "PASS THE VALUE HERE"));
        command.Parameters.Add(new OleDbParameter("@EndOrderID", "PASS THE VALUE HERE"));

        connection.Open();
        OleDbDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
//Retrieve records here
        }
        reader.Close();
    }

answered Jul 31 '12 at 09:31

HatSoft

11,077
3
28
43

most of my tables don't have primary key ids.Can we not use something like row_number() in the query like in sql server. – arjun Aug 01 '12 at 01:10
@arjun in your case yes you can – HatSoft Aug 01 '12 at 07:44
@arjun I am mid of something please can you wait for 15 minutes – HatSoft Aug 01 '12 at 08:53
@arjun you will need to modify you sql query to use ROW_NUM look at the example here http://msdn.microsoft.com/en-us/library/ms186734.aspx – HatSoft Aug 01 '12 at 09:36
I was talking about visual fox pro not sql server – arjun Aug 01 '12 at 11:04
Visual FoxPro has a RECNO() function you can use in the SQL query to get certain record numbers. – Gene S Aug 01 '12 at 14:20
@GeneS But RECNO() doesn't work when used with joins. Is there any other options? – arjun Aug 04 '12 at 01:56
@GeneS But recno() doesnt't work when used with joins. Is there any other solutions for this? – arjun Aug 06 '12 at 05:57
Could you provide an example in your question so I can see what you are talking about? – Gene S Aug 21 '12 at 21:41
@GeneS OP is arjun not me, please request him, also he has already accepted an answer so why do you want to ask him now after such a long time – HatSoft Aug 22 '12 at 08:24
@HatSoft Sorry about that HatSoft. He accepted the answer but then asked the question about problems with INNER JOIN. He was asking the question under this comment section and I didn't pay attention to where his question was asked. I will ask my question in the comment section under the accepted answer. – Gene S Aug 22 '12 at 13:12

score 1 · Accepted Answer · answered Aug 01 '12 at 14:35

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;
     } 
}

But there is a small problem.When there are inner joins the recno() doesn't seem to be working. — arjun, Aug 03 '12 at 11:41
@arjun Could you provide an example of how you are attempting to use the RECNO() in an inner join? — Gene S, Aug 22 '12 at 13:13

oledb, visual fox pro and c#

2 Answers2