How to use GetOleDbSchemaTable method on a long name dbf file

Question

As part of a project I'm working on in C# I need to read in a .dbf file. The first thing I want to do is to get the schema table from the file. I have code that works as long as the filename (without the extension) is not longer than 8 characters.

For example, let's say I have a file named MyLongFilename.dbf. The following code does not work; it throws the following exception: “The Microsoft Jet database engine could not find the object 'MyLongFilename'. Make sure the object exists and that you spell its name and the path name correctly.”

string cxn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyLongFilename;Extended Properties=dBASE 5.0";
OleDbConnection connection = new OleDbConnection(cxn);

To get past this exception, the next step is to use a name the OldDbConnection likes ('MyLongF~1' instead of 'MyLongFilename'), which leads to this:

string cxn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyLongF~1;Extended Properties=dBASE 5.0";
OleDbConnection connection = new OleDbConnection(cxn);

This does successfully return an OleDbConnection. Now to get the schema table I try the following:

connection.Open();
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
    new object[] { null, null, fileNameNoExt, null });

This returns a DataTable with no rows. If I rename the filename to 8 or less characters then this code works and I get back a row for each field in the database.

With the long filename, I know the returned connection is valid because I can use it to fill a DataSet like so:

string selectQuery = "SELECT * FROM [MyLongF~1#DBF];";
OleDbCommand command = new OleDbCommand(selectQuery, connection);
connection.Open();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = command;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);

This gives me back a DataSet containing a DataTable with all of the data from the dbf file.

So the question is how can I get just the schema table for the long named dbf file? Of course I can work around the issue by renaming/copying the file, but that’s a hack I don’t want to have to make. Nor do I want to fill the DataSet with the top 1 record and deduce the schema from columns.

Answer 1

According to MSDN, the folder represents the database and the files represent tables. You should be using the directory path not including the filename in the connection string then, and the name of the table as part of the restrictions to GetOleDbSchemaTable.

Answer 2

Well, i think the connection should be

string cxn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties=dBASE 5.0";
OleDbConnection connection = new OleDbConnection(cxn);

and the other is, maybe you should try with other provider, I boosted a lot along ago when I used like this:

string cxn = "PROVIDER=VFPOLEDB.1;Data Source=C:\;Extended Properties=dBASE 5.0";

But you should have VFP 7 installed

or install Microsoft OLE DB Provider for Visual FoxPro 9.0 from here

        const string connectionString = @"Provider = vfpoledb; Data Source = {0}; Collating Sequence = general;";
        OleDbConnection conn = new OleDbConnection(string.Format(connectionString, dirName));
        conn.Open();
        OleDbCommand cmd = new OleDbCommand(string.Format("select * from {0}", fileName), conn);