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:
- ID int identity(1,1)
- Name varchar(100)
- Details varchar(200)
- Status bit
- 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.
To offer a C# solution for you I would start by downloading Microsoft Visual Foxpro OleDb Provider which works with .DBF tables.
at the top of your C# code, add
Then, within a method to move the data
Since the OleDb does not support copy TYPE CSV, I have found this post on S/O to dump into CSV format for you
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.
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: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 dataThe Second Write Method
Save a
List<T>
into a dbf file.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 savedvalues
: Your data as a List of objects of typeT
to be saved into a dbf filemapping
: A list of functions that tell this method how to retrieve data from the class type.columns
: dbf column informationencoding
: 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 codeNow you can save a
List<MyClass>
into a dbf file like this:enjoy it.
Id like to help. However, id stick with the plain process with OLEDB for VPF.
CREATE TABLE DBF
This could be your create table script for .DBF
Then, you can call this one from an OLE DB script like as used below.
IMPORT ROW IN TABLE
To import rows in a DBF table, its not as far as the usual SQL scripts we do in other DB's.
You can just now change the values what fits your need, just like the usage below.
Please let me know if you have other concerns. Don't forget to install this one in your machine. VFP OLE DB