How do I use the VFPOLEDB Provider to make use of

Question

I am writing a .NET application in C# that reads a FoxPro .DBF using the Visual FoxPro OLE DB provider (VFPOLEDB.1).

The problem is the .DBF is 900MB, so a simple read takes way too long, compared to the instantaneous reading from FoxPro (we use the DOS FoxPro). The difference between the provider and and a native FoxPro search is that I assume FoxPro makes use of the indexing files (we use .CDX and .NDX) and the provider does a kind of linear search with the given SQL statement.

public DataTable getData(string refNum = "TESTREFNUM")
{

    DataTable result = new DataTable();

    OleDbConnection connection = new OleDbConnection(
            "Provider=VFPOLEDB.1;Data Source=C:\\PATH\\TO\\DBFFILES\\;Exclusive=No");

        connection.Open();

        if (connection.State == ConnectionState.Open)
        {
            string mySQL = "SELECT DP_PRO, DP_FILE, DP_NUM"
                + "FROM DISPATCH"                           // 900MB dispatch.dbf
                + "WHERE DP_PRO LIKE '%" + refNum + "%'";

            OleDbCommand MyQuery = new OleDbCommand(mySQL, connection);
            OleDbDataAdapter DA = new OleDbDataAdapter(MyQuery);

            DA.Fill(result);

            connection.Close();
        }
    return result;
}

So from my research, I've seen more support for .CDX files than .NDX files, which is fine. But how do I set the OleDBConnection to make use of the indexing, besides the default slow search through the file?

Are there any changes I have to make to the SQL statement? Or the provider? Or settings to change on the DBF file? From research, I've also found out that .CDX references are in the headers of the .DBF file, so it shouldn't be a problem for the provider to know which .CDX files are associated.

Any help would be greatly appreciated. I'm a recent grad with no FoxPro experience. :p

Answer 1

VFPOLEDB provider doesn't support indexes at all.

Answer 2

In terms of the compound index (the CDX) you want to make sure your queries are Rushmore-optimised. That's all you need to do - if a CDX is present for the table in question (as indicated in the header of the DBF) then it will be automatically used. Looking at the example above, the assumption therefore is that there is no index tag on the field DP_PRO.