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

Jaunita

Either a datatable or Dataset – Jaunita

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? – Coursing

I have added my code but keep getting that error. Any ideas? – Jaunita

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 #4107567 as a good starting point to query and get the correct VFP .net provider... Then, look at other answers on VFP and parameterizing queries – Coursing

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 – Ferdy

Answer 1

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

Jaunita

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 – Snap

Answer 2

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.)

Knucklehead

I added in a answer, can you help please – Jaunita

Thanks I got it right as your connection string helped. – Jaunita

It appears that you don't actually have the provider installed. – Knucklehead