How can I read a DBF file with incorrectly defined column data types using ADO.NET?
- by Jason
I have a several DBF files generated by a third party that I need to be able to query. I am having trouble because all of the column types have been defined as characters, but the data within some of these fields actually contain binary data. If I try to read these fields using an OleDbDataReader as anything other than a string or character array, I get an InvalidCastException thrown, but I need to be able to read them as a binary value or at least cast/convert them after they are read. The columns that actually DO contain text are being returned as expected.
For example, the very first column is defined as a character field with a length of 2 bytes, but the field contains a 16-bit integer.
I have written the following test code to read the first column and convert it to the appropriate data type, but the value is not coming out right.
The first row of the database has a value of 17365 (0x43D5) in the first column. Running the following code, what I end up getting is 17215 (0x433F). I'm pretty sure it has to do with using the ASCII encoding to get the bytes from the string returned by the data reader, but I'm not sure of another way to get the value into the format that I need, other that to write my own DBF reader and bypass ADO.NET altogether which I don't want to do unless I absolutely have to. Any help would be greatly appreciated.
byte[] c0;
int i0;
string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ASTM;Extended Properties=dBASE III;User ID=Admin;Password=;";
using (OleDbConnection c = new OleDbConnection(con))
{
c.Open();
OleDbCommand cmd = c.CreateCommand();
cmd.CommandText = "SELECT * FROM astm2007";
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
c0 = Encoding.ASCII.GetBytes(dr.GetValue(0).ToString());
i0 = BitConverter.ToInt16(c0, 0);
}
dr.Dispose();
}
