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

You must force your program to run in 32-bit mode. Lots of programmers get this wrong, don't change the solution platform. Instead use Project > Properties > Build tab and select the Platform Target. Again for the Release configuration. — Hans Passant, Aug 01 '23 at 11:59
I would say to convert the database to some modern database format using a third party tool. FoxPro is long out of support, and relying on unsupported software can be risky. — JonasH, Aug 01 '23 at 12:43
Is there any _reasonable_ incentive to _not_ migrate to a different database system that has not been dead for years? — Fildor, Aug 01 '23 at 13:45
The plan is to move to SQL Server down the line, but we need to ensure that we develop tests that verify everything with FoxPro database and once the modules of the application be moved to SQL Server, then we can run the same scripts again pointing to SQL Server database. — user22320197, Aug 01 '23 at 17:57
@Fildor-standswithMods FoxPro is an integrated development system. "Migrating" means re-writing the entire system from scratch, not just switching the database. — Christof Wollenhaupt, Aug 08 '23 at 08:07
@ChristofWollenhaupt I know and: so? Dead is dead. It is going to be a piece of work, yes. But it needs to be done. And obviously, it is already in the pipeline, since OP seems to be looking for a way to do regression testing. Which will ensure that anything that worked before the switch still works after. — Fildor, Aug 08 '23 at 08:46
@Fildor-standswithMods The typical FoxPro business application is an investment of 30,000 to 500,000 hours of development. Full replacements are in a similar range. In my world customers need more reasons than "dead is dead" to spend 7 to 8 digits for a replacement. Your world might differ, of course. — Christof Wollenhaupt, Aug 08 '23 at 12:09
@ChristofWollenhaupt Alternatively you can retire the whole business, of course, yes. — Fildor, Aug 08 '23 at 12:30
@ChristofWollenhaupt 30-500K hours 20 years ago is still dead, still needing migration. FoxPro was never COBOL, MS bought it just to incorporate is database engine into Access and essentially abandoned it. Instead of assuming people don't know about FoxPro, assume they had to actually use dBase IV, Clipper and the occasional FoxPro 20 years ago, so they know what they're talking about. — Panagiotis Kanavos, Aug 28 '23 at 07:43
@ChristofWollenhaupt I say 20 years for a reason. I used FoxPro for a bit around 2000 and yes, while a lot better than Access, but there simply was no path forward. I was a SQL Server MVP back in 2005 when I met one of the remaining FoxPro MS devs at an MVP Summit - very few of the MVPs used it by that point. FoxPro was what some of us had used to migrate *Clipper* databases to SQL Server or other server-based databases — Panagiotis Kanavos, Aug 28 '23 at 07:47
In any case, if the files really come from VFP, you can use *Excel* to test connecting to them with either the OLEDB or ODBC driver. This will allow you to test drivers and settings interactively instead of having to recompile and test your application. You have to install drivers that match Excel's bitness. (32- or 64-bit). The bitness of the .NET application also has to match the drivers. — Panagiotis Kanavos, Aug 28 '23 at 07:58
Once the connection works, VFP files can be queries as a linked server from SQL Server. The article [Add a Visual FoxPro database to SQL Server as a linked server](https://learn.microsoft.com/en-us/previous-versions/troubleshoot/visualstudio/foxpro/add-database-sql-server) shows how to do this. There's no built-in connection wizard in SSSM though, which is why using Excel to test settings is still needed — Panagiotis Kanavos, Aug 28 '23 at 08:01
@PanagiotisKanavos I was a VFP MVP from 1998 to 2010. In 2005 the VFP team was fully staffed (it was always small). At the MVP Summit in 2007 MS announced the end of VFP. Anyway, this discussion isn't helping anyone. — Christof Wollenhaupt, Aug 29 '23 at 14:18

Ramy Nabil · Answer 1 · 2023-08-28T08:00:22.960

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.

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

1 Answers1