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.