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