C# Open DBF file

Question

I'm having a problem opening a DBF file - I need to open it, read everything and process it. I tried several solutions (ODBC/OLEDB), several connection string, but nothing worked so far.

The problem is, when I execute the SQL command to get everything from the file, nothing gets returned - no rows. What's even more odd, the content of the DBF file being opened get deleted.

See the code I have:

public override bool OpenFile(string fileName, string subFileName = "")
{
    OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.GetDirectoryName(fileName) + ";Extended Properties=dBASE IV;User ID=;Password=;");
    try
    {
        if (con.State == ConnectionState.Closed) { con.Open(); }
        OleDbDataAdapter da = new OleDbDataAdapter("select * from " + Path.GetFileName(fileName), con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        int i = ds.Tables[0].Rows.Count;
        return true;
    }
    catch
    {
        return false;
    }             
}

I debugged the code and watched the file being opend in Windows Explorer. When it reached this line:

da.Fill(ds);

the size of the file dropped to only a few Bytes (from hundreds of kB).

My next thought was to make the DBF file read only. That however cause an "unexpected exception from an external driver".

So my question is - what the heck? I'm sure the file is not corrupt, it is a direct export from some DB. (No, I do not have access to that DB). I can also open that file in MS Office no problem.

^{I cannot share the DBF file - it contains confidential data.}

Answer 1

Two things... just because its a .DBF file extension might night mean its a Dbase IV file. It might actually be that of Visual Foxpro. That said, I would look into downloading and installing the Visual Foxpro OleDB driver from Microsoft download. Next, the OleDbConnection is pointing to the path that has the actual tables (you already have that).

The query itself, shouldn't care about the extension, so I would change your call to get just then name via "Path.GetFileNameWithoutExtension"

It might be a combination of the two.

Connection string for VFP provider

"Provider=VFPOLEDB.1;Data Source=" + FullPathToDatabase

Answer 2

This is not the exact answer but it will help you to find the issue.

Try to give an inline connection string and select query to make sure problem is not with building those. Catch the exception and check the details of it.

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=;Password=;"); // give your path directly 
try
{
    con.Open();
    OleDbDataAdapter da = new OleDbDataAdapter("select * from tblCustomers.DBF", con); // update this query with your table name 
    DataSet ds = new DataSet();
    da.Fill(ds);
    con.Close();
    int i = ds.Tables[0].Rows.Count;
    return true;
}
catch(Exception e)
{
    var error = e.ToString();
    // check error details 
    return false;
}