Create .DBF file from SQL table records

Question

I want to create a .DBF file from SQL table records.

Such as if there is a table named CountryMaster in SQL and it has 5 columns:

1. ID int identity(1,1)
2. Name varchar(100)
3. Details varchar(200)
4. Status bit
5. CreatedDate datetime

And it has 100 rows.

How can I export these records with headers in .DBF file from C#?

NOTE: Created .DBF file size must be very compact.

Answer 1

You can see the Xbase Data file (*.dbf) structure and write your own code but I have done the implementation and have been using it for years. Here you can find it on GitHub

---

How to use the library

There are some write methods in a file named DbfFile.cs. You may use any of them. I will explain some of them:

The First Write Method

Save a DataTable as dbf file:

static void Write(string fileName, System.Data.DataTable table, Encoding encoding)

• fileName: is the location which you want the .dbf output file be saved.
• table: is your data which you have read from the SQL Server or any other source.
• encoding: the encoding to be used when saving the string data

The Second Write Method

Save a List<T> into a dbf file.

static void Write<T>(string fileName,
                                    List<T> values,
                                    List<Func<T, object>> mapping,
                                    List<DbfFieldDescriptor> columns,
                                    Encoding encoding)

Read the database and save the result into some class type then save class value to dbf file using this method. Here is description of it's parameters:

• fileName: the dbf file name to be saved
• values: Your data as a List of objects of type T to be saved into a dbf file
• mapping: A list of functions that tell this method how to retrieve data from the class type.
• columns: dbf column information
• encoding: the encoding of the dbf file.

Example for the Second Write Method

As the first approach is straight forward, I provide you with and example on the second write method. Consider you want to save a List<MyClass> data into a dbf file. Here is the code

class MyClass
{
    public int Id {get;set;}
    public string Name {get;set;}
}

Now you can save a List<MyClass> into a dbf file like this:

var idColumn = DbfFieldDescriptors.GetIntegerField("Id");
var nameColumn = DbfFieldDescriptors.GetStringField("Name");
var columns = new List<DbfFieldDescriptor>() { idColumn, nameColumn };

Func<MyClass, object> mapId = myClass => myClass.Id;
Func<MyClass, object> mapName = myClass => myClass.Name;
var mapping = new List<Func<MyClass, object>>() { mapId, mapName };

List<MyClass> values = new List<MyClass>();
values.Add(new MyClass() { Id = 1, Name = "name1" });

DbfFileFormat.Write(@"C:\yourFile.dbf", values, mapping, columns, Encoding.ASCII);

---

Also using this library you can read dbf files and your code do not depend on Microsoft.Jet.OLEDB or anything else.

enjoy it.

Answer 2

Mostly all of the information you need can be seen at Trouble with Insert Into .dbf file, because it shows how to create a table and insert values into it while creating a .dbf file. You will need to make some modifications for the fields you have specified, but the page describes everything you need.