Sep 26 2007

Reading DBF files in C#

Posted by admin under .NET

I was recently assigned a task of converting an old DOS system (using dbase or Visual foxpro files or whatever - fileextention was *.dbf) to  Windows Forms and SQL Server.

I have quite a bit of experience of these type of projects - I have been doing a lot of application specific export properiary data -> text files and then writing a snippet reading it and inserting to Excel.

This one looked realy easy, and googling for C# and DBF files led me into using the VFPOLEDB.1 OLEDB driver. However - as it turned out - I didn't have that driver installed. So what to do? Going back to ODBC of course. The code snippet that did the trick loked like this:



            System.Data.Odbc.OdbcConnection oConn = new System.Data.Odbc.OdbcConnection();
            oConn.ConnectionString = @"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=D:\databases\;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
            oConn.Open();
            System.Data.Odbc.OdbcCommand oCmd = oConn.CreateCommand();
            oCmd.CommandText = @"SELECT * FROM D:\databases\thefile.dbf" ;
            DataTable dt = new DataTable();
            dt.Load(oCmd.ExecuteReader());
            oConn.Close();

            dataGridView1.DataSource = dt;

And it had created the datatable with all metadata such as column names etc. I did had some struggle with what to select from -

SELECT * FROM thefile

should work if you ask me. I mean I have specified I want to use the DBF driver (meaning .dbf) - I have also specified the SourceDB (where the files are). But it didn't work. You (or at least I did) have to specify the whole path:

SELECT * FROM D:\databases\thefile.dbf

and to be honest I don't care why and if I did something wrong. I got the data and was happy with that.