How to query a Foxpro .DBF file with .NDX index fi

问题:

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

回答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();
}

回答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.