How to query a Foxpro .DBF file with .NDX index file using the OLEDB driver in C#

Question

I have a Foxpro .DBF file. I am using OLEDB driver to read the .DBF file. I can query the DBF and utilize its .CDX index file(cause it is automatically opened). My problem is that I want to query it with the .NDX index file (which is not automatically opened when the .DBF is opened). How can I open the .NDX file in C# using OLEDB driver cause the DBF is really big to search for a record without the index? Thanks all! Here is the code I am using to read the DBF.

OleDbConnection oleDbConnection = null;
        try
        {
            DataTable resultTable = new DataTable();
            using (oleDbConnection = new OleDbConnection("Provider=VFPOLEDB.1;Data Source=P:\\Test\\DSPC-1.DBF;Exclusive=No"))
            {
                oleDbConnection.Open();
                if (oleDbConnection.State == ConnectionState.Open)
                {
                    OleDbDataAdapter dataApdapter = new OleDbDataAdapter();
                    OleDbCommand command = oleDbConnection.CreateCommand();

                    string selectCmd = @"select * from P:\Test\DSPC-1  where dp_file = '860003'";
                    command.CommandType = CommandType.Text;
                    command.CommandText = selectCmd;

                    dataApdapter.SelectCommand = command;
                    dataApdapter.Fill(resultTable);
                    foreach(DataRow row in resultTable.Rows)
                    {
                        //Write the data of each record 
                    }
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            try
            {
                oleDbConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Failed to close Oledb connection: " + e.Message);
            }
        }

Benzine

C# Database Connection Strings – Lessielessing

NDX is not a file that VFP creates. I think it's a dBase file. – Riddell

Answer 1

ndx files wouldn't be opened by default and those are a thing of the past really, why wouldn't you simply add your index to your CDX. If it is not an option, then ExecScript suggestion by DRapp is what you can do. He was very close. Here is how you could do that:

string myCommand = @"Use ('P:\Test\DSPC-1') alias myData
Set Index To ('P:\Test\DSPC-1_Custom.NDX')
select * from myData ;
  where dp_file = '860003' ;
  into cursor crsResult ;
  nofilter
SetResultset('crsResult')";

DataTable resultTable = new DataTable();
using (oleDbConnection = new OleDbConnection(@"Provider=VFPOLEDB;Data Source=P:\Test"))
{
  oleDbConnection.Open();
  OleDbCommand command = new OleDbCommand("ExecScript", oleDbConnection);
  command.CommandType = CommandType.StoredProcedure;
  command.Parameters.AddWithValue("code", myCommand);

  resultTable.Load(cmd.ExecuteReader());
  oleDbConnection.Close();
}

Jetblack

Thanks @Cetin Basoz, your code run perfectly without error, but the performance is still the same. It takes about 60s to look up for the record '860003' comparing to <1s when running the same query on Foxpro. I figured out that if I open the NDX file in foxpro IDE, and then run the C# code on Visual Studio, it will takes 3s for the query. – Benzine

I also tried with test data on my computer and I get the "faster" results with an index. Maybe your P: drive is remote and it is losing time to bring in the index file? Did you try adding your index to structural CDX? How many rows do you have ??? Even without any index 60s sounds to be slow. BTW there is a known optimization problem in VFP, regarding table's code page being different then OS code page. – Jetblack

My DBF has over 5M records. It was created more than 15 years ago, so the .NDX index file is used in different places in the system. Converting it to structural CDX is possible, but my boss does not like that :(. You are right, my P drive is remote. If I bring it to local, it will take 30s for the query, but I cant still use that performance for production. Anyway, I marked your answer for future references. Thanks @Cetin Basoz! – Benzine

Answer 2

Your connection string should only reference the PATH to where the .dbf files are located.

Then, your query is just by the table name.

new OleDbConnection("Provider=VFPOLEDB.1;Data Source=P:\\Test\\;Exclusive=No"))

selectCmd = @"select * from DSPC-1  where dp_file = '860003'";

As for using the .NDX, how / where was that created... Is that an old dBASE file you are using the Visual Foxpro driver for?

If it is a separate as described, you might need to do via an ExecScript() to explicitly open the file first WITH the index, THEN run your query. This is just a SAMPLE WITH YOUR FIXED value. You would probably have to PARAMETERIZE it otherwise you would be open to sql-injection.

cmd.CommandText = string.Format(
@"EXECSCRIPT('
USE DSPC-1 INDEX YourDSPC-1.NDX
SELECT * from DSPC-1 where dp_file = '860003'" );

Also, you might have issue with your table names being hyphenated, you may need to wrap it in [square-brackets], but not positive if it is an issue.

Mitrewort

Hi, the .NDX is an index file created in Foxpro, it is stored in the same directory with the main DBF file. How can i let SQL utilize this index file ? @Mitrewort – Benzine

@h2nghia, see revised answer offering a scripting option. – Mitrewort

@DRapp, you were very close. VFP string literals are limited to 255 in length and with linefeeds in the literal like that, execsript wouldn't exec succefully. Second, you need to put the result in a cursor and use SetResultSet() to send the result back to the VFPOLEDB client. – Jetblack

@CetinBasoz, having never really used non-.cdx files since they became available, was not positive on it. Also, thanks for the SetResultSet(). I upvoted your answer for solution. – Mitrewort

My guess, very few developers use non-CDX versions :) Probably it was foxbase 2.1 when I last used a non-CDX index (and that was because CDX didn't exist then). – Jetblack