Read/Write to FoxPro Database (.DBF files) from C#

Question

We need to connect/read/write to FoxPro database via C# and facing an issue with that.

From our understanding there are three different ways to connect to FoxPro database - Ole Driver, ODBC Driver and VFPOLE Driver.

Visual FoxPro ODBC Driver: as per article on this page https://learn.microsoft.com/en-us/previous-versions/visualstudio/foxpro/mt490121(v=msdn.10) the VFPODBC drive is no more supported and is recommended to use Visual FoxPro OLE DB.

Visual FoxPro Ole Driver: when running on windows x86, we face issue that 'VFPOLEDB' provider is not registered on the local machine. (FYI-The vfpoledb.dll exists at C:\Program Files (x86)\Common Files\System\Ole DB). To our knowledge, it is giving issue as VFPOLEDB driver works on 32-bit machine instead od 64-bit machine.

Access Manager OLE Driver: able to connect to the database, but unable to read the table and gives issue External table is not in the expected format, seems because we are using dBase IV when establishing connection.

Looking forward to immediate help on this.

This is the code snippet:

static void Main(string\[\] args)
{
    OleDbConnection conn = null;

    try
    {
        // Option 1.1) Free table directory - ACE OLE
        conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\AutoParts\\db;Extended Properties=dBase IV;");

        // Option 1.2) Free table directory - VFPOLE
        // conn = new OleDbConnection("Provider=VFPOLEDB;DRIVER=Microsoft Visual FoxPro Driver;Data Source=D:\\AutoParts\\db;Collating Sequence=machine;");

        // Option 2.1) Database Container - ACE OLE
        // conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\AutoParts\\db2;Extended Properties=dBase IV;");

        // Option 2.2) Database Container - VFPOLE
        // conn = new OleDbConnection("Provider=VFPOLEDB;DRIVER=Microsoft Visual FoxPro Driver;Data Source=D:\\AutoParts\\db2;Collating Sequence=machine;");

        conn.Open();
        Console.WriteLine(conn);

        string strQuery = "Select \* from Employee.dbf";

        OleDbCommand myQuery = new OleDbCommand(strQuery, conn);
        OleDbDataAdapter DA = new OleDbDataAdapter(myQuery);
        Console.WriteLine(DA);
 
        using (var reader = myQuery.ExecuteReader())
        {
            while (reader.Read())
            {
                 var str = (string)reader\["empl_id"\];
                 Console.WriteLine(str);
            }
        }
    }
    finally
    {
        conn.Close();  
    }
}

Answer 1

Regarding Option 1.1 and Option 2.1 as you stated it gives error because you are using dBase IV when establishing connection which has different tables files format than that of the VFP9 files.

Regarding Option 1.2 most probably the table files you are trying to connect are not free tables and are associated to a Database.

Regarding Option 2.2 which is the right choice to use but you must specify the Database file name in the connection string as follows:

    conn = new OleDbConnection("Provider=vfpoledb;Data Source=D:\\AutoParts\\db2\\DbContainerFile.dbc;Collating Sequence=machine;");

You may create the connection string using the following steps:

• Start by creating an empty text file and rename it to something like 'tempConString.udl' (don't use the VFP MODIFY FILE to create the file then it won't be empty).

• The file icon will change to considered as a Universal Data Link.

• Double-click on this icon then Windows will open a Data Link Properties window so that you can edit the udl file.

• Open the Provider tab in the properties window and select the OLE DB provider for VFP.

• Now move to the Connection tab and pick the database that you want to use.

• Click the Test Connection button to make sure that all is working properly and then press OK to save the data and close the properties window.

• Open the tempConString.udl file in a text editor to get the connection string from it and then you may delete the tempConString.udl file.

If the vfpoledb is not registered then first run regsvr32 "Complete Path To vfpoledb.dll command in the Command Prompt but open it as administrator.