For a few days I was trying to solve a problem with my WCF Service. The thing was that after publishing the reading of the dbf file, which was implemented using oledb or odbc and using microsoft jet and vfpoledb drivers (a few distinct ways each of which not working) I always got the error VFPOLEDB driver is not registered or the same with microsoft jet. So tried a few things and nothing helped ( Im sure there is a way, just I couldnt find it) so I decided to do a binary read of the DBF file and implement it in the silverlight app. Here Im posting the programm i wrote to test the reading of the dbf. It is a simple forms application that inserts the dbf file data in a datagridview. It is really far from perfect, and it is orientated in only getting the names and numbers of the columns and the records, so many things of the dbf file are not taken into account. Anyway I am not confident it works on many types of dbf, because I couldn test it on too many, but on the few I did it worked Ok. I would be glad if I helped someone who had no other choice but to use binary reading or something ... :)
Here are the specs I used: http://www.dbf2002.com/dbf-file-format.html . So here is the code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
namespace BinaryDBF
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public enum EncodingType
{
ASCII,
Unicode,
UTF7,
UTF8,
UTF32,
BG
}
public class Columns
{
//This is my custom class to hold all the info of the columns,
//i get that the names arent really well deviced but I was in a hurry :)
public string column_name { get; set; }
public int column_number { get; set; }
public string column_property { get; set; }
public byte length { get; set; }
}
public static string ByteArrayToString(byte[] bytes)
{
//If called without giving an ecoding type as an argument it uses Unicode as default.
return ByteArrayToString(bytes, EncodingType.Unicode);
}
public static string ByteArrayToString(byte[] bytes, EncodingType encodingType)
{
//Here is the method that does the actual encoding of the string. There are
//all types of encoding I could think of... ofcourse there are more, if you
//need to add ...
System.Text.Encoding encoding = null;
switch (encodingType)
{
case EncodingType.ASCII:
encoding = new System.Text.ASCIIEncoding();
break;
case EncodingType.Unicode:
encoding = new System.Text.UnicodeEncoding();
break;
case EncodingType.UTF7:
encoding = new System.Text.UTF7Encoding();
break;
case EncodingType.UTF8:
encoding = new System.Text.UTF8Encoding();
break;
case EncodingType.UTF32:
encoding = new System.Text.UTF32Encoding();
break;
case EncodingType.BG:
encoding = Encoding.GetEncoding("Windows-1251");
break;
}
return encoding.GetString(bytes,0,bytes.Length);
}
private void button1_Click(object sender, EventArgs e)
{
//Коментарите са на английски за да го постна в сайта.
try
{
dataGridView1.Columns.Clear();
dataGridView1.Rows.Clear();
OpenFileDialog dialog = new OpenFileDialog();
//dialog.Filter = "dBase Files (.dbf) | *.dbf ";
//For somereason the filter doesnt work, I suspect I have written something wrong :)
dialog.FilterIndex = 1;
dialog.Multiselect = false;
dialog.ShowDialog();
string filePath = dialog.FileName;
//I open the file
FileStream filereader = new FileStream(filePath, FileMode.Open);
//read it in the byte array dbf_file
long fileLength = filereader.Length;
Byte[] dbf_file = new Byte[fileLength];
filereader.Read(dbf_file, 0, (int)fileLength);
filereader.Close();
//Close it.
//the version of the file is stored in the first byte (I dont actually use that but stil ...)
byte version = dbf_file[0];
//Then I get the number of records in the file.
byte[] number_records = new byte[4];
number_records[0] = dbf_file[4];
number_records[1] = dbf_file[5];
number_records[2] = dbf_file[6];
number_records[3] = dbf_file[7];
int numberOF_records = BitConverter.ToInt32(number_records, 0);
//In this list I save the column names, numbers , length and type.
//I dont really make any use of type anyway.
List<Columns> Columns = new List<Columns>();
int separator = 32; //because the first 32bytes are known
//13 is the deciman equivalent of the 0x0D which is the carriage return symbol in
//the ASCII which is used to separate the records of the fields from the actual data.
while (dbf_file[separator] != 13)
{
separator++;
}
//As I begin from 32 the number of the fields should be calculated this way:
int Column_Number = (separator - 32) / 32;
//Here I start to read the neccessery info from the columns.
//In the dbf specification it is said that the first 10 bytes are the field name
//and etc... I am pasting the link with the specs.
for (int startBit = 32; startBit < separator; startBit += 32)
{
byte[] columnName = new byte[10];
for (int i = 0; i < 10; i++)
{
columnName[i] = dbf_file[startBit + i];
}
Columns column = new Columns();
column.column_name = ByteArrayToString(columnName, EncodingType.BG);
column.column_number = startBit / 32;
byte property = dbf_file[startBit + 11];
column.length = dbf_file[startBit + 16];
column.column_property = property.ToString();
Columns.Add(column);
}
//Through here I alwready have the names, length and etc., of the columns in a List.
//In separator I now have the adress of the byte that countains the 0x0d.
//Since I have no interest in nothing else rather than the names of the columns and the
//data in the rows, And I get everything in a string, not caring if it is double or whatever.
//I clear all the not neccessery symbols that I noticed are writen in the strings that I got
//for the column names. Because they have some indent or whatever it was, I just remove them
//because I dont need them.
//Removing the "\0" from the names.
foreach (Columns column in Columns)
{
if (column.column_number == 1)
{
column.length++;
//Well, Here is the thing I am not 100% sure I am doing right but still, it works.
//As in the dbf files specification there is a... byte that cointains a symbol which
//identifies whether the record is deleted, or some other info. Yet I have no need to
//analyze that info cuz I am certain that I need all records. So I increment the
//length of the first column, because it does no harm, and I implement the following
//algorythm easy.
}
column.column_name = column.column_name.Replace("\0", "");
//here is the removing ...
}
//Then I Create the columns in the datagridview. I did this for a test application to visualize
//and see if Im doing the reading correctly. You can use whatever type of container you need to have
//the data from the dbf file in.
foreach (Columns column in Columns)
{
DataGridViewTextBoxColumn colona = new DataGridViewTextBoxColumn();
colona.Name = column.column_name.Trim() + " | " + column.column_property + " | " + column.length;
dataGridView1.Columns.Add(colona);
}
//I noticed there is a 1 byte indent (I didnt pay Too much attention to the dbf specification,
//without which I could have never done it ofcourse, so there might be better explanations for
//the results I am having and the things Im doing. Be sure to read it. )
int readingBegin = separator + 1; //1 байт отстъп.
//Here is hte Actuall reading of the records.
//The iteration I chose is by rows. I read the first record, then I iterate through the
//the columns to use their lengths for measuring how bytes I need to parse and then I save
//the info in a cell, that goes in a row, which after going through every column is added to
//the datagridview. I even didnt change the name of the gridview :)
for (int i = 0; i < numberOF_records; i++)
{
DataGridViewRow row = new DataGridViewRow();
foreach (Columns col in Columns)
{
byte[] nextCol = new byte[col.length];
for (int j = 0; j < col.length; j++)
{
nextCol[j] = dbf_file[readingBegin++];
}
string nextColumn = ByteArrayToString(nextCol, EncodingType.BG).Trim();
DataGridViewTextBoxCell cell = new DataGridViewTextBoxCell();
cell.Value = nextColumn;
row.Cells.Add(cell);
}
dataGridView1.Rows.Add(row);
}
//Well Ok thats it. There are many many things that this doesnt cover, but I am interested only
//in the data in the records, not minding whatkind it is or whatever other property it was set
//in the dbf. Thats all if I have some breaking mistakes or if someone cares to improve the
//algorythm and post it I would appriciate.
}
catch (Exception d)
{
MessageBox.Show(d.Message);
}
}
}
}