How can i read dbf with .CDX files?

I am writing a .NET application in C#. That application reads a FoxPro .DBF using the Visual FoxPro OLEDB provider.

The function that allows you to read the .DBF files does not detect the .CDX files and makes a slow query.How can i read .dbf file with .cdx file?

   public DataTable ReadDbf(string path, string str_sql)
        {
            string constr = "Provider=VFPOLEDB;Data Source=" + path;
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = constr;
            con.Open();



            OleDbDataAdapter adapter = new OleDbDataAdapter(str_sql, con);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            con.Close();
            return dt;
        }

my path and str_sql example.

path ="C:\Users\kahla.mir\Desktop\201901"

str_sql = Select * From 100307_fct where prodid = "000038" and srcid = "001190"  and perid = "000001"

Solution

The VFP OLEDB provider automatically opens, uses and maintains structural¹ CDX files, just like the VFP IDE and the runtime engine.

There are several things that can cause a CDX not to be used for speeding up queries:

the CDX is present but not structural
Rushmore has been disabled via SET OPTIMIZE OFF
the query does not contain any optimisable predicates that match index keys

Of these three, the latter is the most common by far. The rules governing eligibility for Rushmore optimisation can be found elsewhere on the net, for example in the Fox wiki article named Understanding Rushmore. The essence is that the expressions used in predicates must match available index expressions in order to be optimisable. Hence you cannot write efficient queries unless you know what at the actual index expressions in your indexes are.

In your case the primary index is most likely a compound expression involving multiple fields, like

prodid - '|' - srcid - '|' - perid

if you are lucky, or

prodid + srcid + perid

if you are not. Should you instead find something like alltrim(prodid) + alltrim(srcid) + alltrim(perid) then the only valid recourse would be to grab your shotgun and go find the clueless perpetrator before he does any more damage.

If you are lucky then you can formulate your filter condition like this in order to hit the index:

where prodid - '|' - srcid - '|' - perid == '000038|001190|000001'

The key could be passed to OLEDB as a single parameter (hence doing the concatenation in C# and passing a single string) or - if some inconvenient framework is involved - as three separate parameters that are then concatenated in the query itself:

where prodid - '|' - srcid - '|' - perid == ? - '|' - ? - '|' - ?

In the unlucky case you have to determine the field widths and pad accordingly:

where prodid + srcid + perid == '000038 001190 000001'

or use an expression like:

where prodid + srcid + perid == padr('000038', 8) + padr('001190', 12) + '000001'

¹) 'structural' CDX file means that the CDX is considered as a structural part of the DBF, in which case its name (apart from the extension) will be the same as that of the DBF and bit 0 of the flag byte at offset 0x1C (28 decimal) of the DBF header will be set, indicating the presence of the structural CDX. If the CDX has merely the same name but the CDX bit in the DBF header is not set then the CDX will not be treated as a structural CDX (i.e. it will not be opened und maintained automatically).