C# Read from .DBF files into a datatable

Question

I need to connect to a .dbf file in visual Studio using C# and populate a data table. Any ideas? I can currently view the tables in Visual Fox Pro 9.0

Code I have tried and failed, keep getting

External table is not in the expected format.

private OleDbConnection conn;
private OleDbCommand cmd;
private OleDbDataReader dr;
private string sqlStr = "";
private DataSet myDataSet;
private OleDbDataAdapter myAdapter;


void test2()
{
    conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\PC1\Documents\\Visual FoxPro Projects\\;Extended Properties=DBASE IV;");
    conn.Open();
    sqlStr = "Select * from Clients.dbf";
    //Make a DataSet object
    myDataSet = new DataSet();
    //Using the OleDbDataAdapter execute the query
    myAdapter = new OleDbDataAdapter(sqlStr, conn);
    //Build the Update and Delete SQL Statements
    OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdapter);         

    //Fill the DataSet with the Table 'bookstock'
    myAdapter.Fill(myDataSet, "somename");
    // Get  a FileStream object
    FileStream myFs = new FileStream
          ("myXmlData.xml", FileMode.OpenOrCreate, FileAccess.Write);
    // Use the WriteXml method of DataSet object to write XML file from the   DataSet
    //  myDs.WriteXml(myFs);
    myFs.Close();
    conn.Close();
}

There are lots of ways, but you need to show something of what you have researched / attempted. Have you downloaded the VFP OleDB provider? looked into OleDbConnection, OleDbCommand? — DRapp, Mar 14 '14 at 02:27
I have added my code but keep getting that error. Any ideas? — TheCoder, Mar 15 '14 at 11:14
Since others thought too broad, but apparently have no experience with VFP to suggest putting on hold... heck with them. Take a look at this link http://stackoverflow.com/questions/4107067/how-to-create-a-join-across-two-foxpro-databases-using-the-ms-ole-db-provider/4108276#4108276 as a good starting point to query and get the correct VFP .net provider... Then, look at other answers on VFP and parameterizing queries — DRapp, Mar 15 '14 at 12:57
you are combining @ with escaped '\\'s, you really should only use one or the other, because when you combine them you end up with two \'s and not just one — Tim M, Jul 21 '20 at 16:55

score 12 · Accepted Answer · answered Mar 21 '14 at 15:39

This code worked for me!

public DataTable GetYourData()
    {
        DataTable YourResultSet = new DataTable();

        OleDbConnection yourConnectionHandler = new OleDbConnection(
            @"Provider=VFPOLEDB.1;Data Source=C:\Users\PC1\Documents\Visual FoxPro Projects\");

        // if including the full dbc (database container) reference, just tack that on
        //      OleDbConnection yourConnectionHandler = new OleDbConnection(
        //          "Provider=VFPOLEDB.1;Data Source=C:\\SomePath\\NameOfYour.dbc;" );


        // Open the connection, and if open successfully, you can try to query it
        yourConnectionHandler.Open();

        if (yourConnectionHandler.State == ConnectionState.Open)
        {
            string mySQL = "select * from CLIENTS";  // dbf table name

            OleDbCommand MyQuery = new OleDbCommand(mySQL, yourConnectionHandler);
            OleDbDataAdapter DA = new OleDbDataAdapter(MyQuery);

            DA.Fill(YourResultSet);

            yourConnectionHandler.Close();
        }

        return YourResultSet;
    }

Does this require the table to be pre created? What if the DBF file is changing and I want the table to be created right after I read the file — Si8, Jun 04 '14 at 20:15

score 0 · Answer 2 · answered Mar 20 '14 at 12:43

0

Visual FoxPro DBFs are NOT dBase IV DBFs, and as such are unreadable by most versions of Microsoft Access's Jet database engine. (MSDN has some specifics, if you care.)

You'll need to either export the DBF from FoxPro into an actual dBase format, or you'll need to have C# open it using the Visual FoxPro OLEDB provider.

Once you have the provider installed, you'll need to change the "Provider" argument of your connection string to the following, assuming your DBF is in that folder.

Provider=VFPOLEDB.1;Data Source=C:\Users\PC1\Documents\Visual FoxPro Projects\;

(Use an @"" string format; you missed a slash in the code sample, between PC1 and Documents.)

answered Mar 20 '14 at 12:43

DougM

2,512
14
14

I added in a answer, can you help please – TheCoder Mar 21 '14 at 15:21
Thanks I got it right as your connection string helped. – TheCoder Mar 21 '14 at 15:40
It appears that you don't actually have the provider installed. – DougM Mar 21 '14 at 15:56

C# Read from .DBF files into a datatable

2 Answers2

Linked