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:
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.
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
There are some write methods in a file named DbfFile.cs. You may use any of them. I will explain some of them:
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 dataSave 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 savedvalues: Your data as a List of objects of type T 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.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.OLEDBor anything else.
enjoy it.
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.
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
CREATE TABLE "C:\test.dbf" ([ID] Numeric(18,0), [Name] Char(100) NULL, [Details] Char(200) NULL, [Status] Logical NULL, [CreateDate] DATETIME NULL)
Then, you can call this one from an OLE DB script like as used below.
string vpfScript = "CREATE TABLE \"C:\test.dbf\" ([ID] Numeric(18,0), [Name] Char(100) NULL, [Details] Char(200) NULL, [Status] Logical NULL, [CreateDate] DATETIME NULL)";
string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\test.dbf";
OleDbConnection connection = new OleDbConnection(connectionString);
using (OleDbCommand scriptCommand = connection.CreateCommand())
{
connection.Open();
scriptCommand.CommandType = CommandType.StoredProcedure;
scriptCommand.CommandText = "ExecScript";
scriptCommand.Parameters.Add("myScript", OleDbType.Char).Value = vfpScript;
scriptCommand.ExecuteNonQuery();
}
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.
string vpfScript = "INSERT INTO \"C:\test.dbf\" ([ID], [Name], [Details], [Status], [CreateDate]) VALUES (1,'test john','test details',.t.,{^2015-09-15)";
string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\test.dbf";
OleDbConnection connection = new OleDbConnection(connectionString);
using (OleDbCommand scriptCommand = connection.CreateCommand())
{
connection.Open();
scriptCommand.CommandType = CommandType.StoredProcedure;
scriptCommand.CommandText = "ExecScript";
scriptCommand.Parameters.Add("myScript", OleDbType.Char).Value = vfpScript;
scriptCommand.ExecuteNonQuery();
}
You can just now change the values what fits your need, just like the usage below.
DataTable dt = new DataTable(); // assuming this is already populated from your SQL Database.
StringBuilder buildInsert = new StringBuilder();
string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\test.dbf";
OleDbConnection connection = new OleDbConnection(connectionString);
using (OleDbCommand scriptCommand = connection.CreateCommand())
{
connection.Open();
foreach(DataRow dr in dt.Rows)
{
string id = dr["ID"].ToString();
string name = dr["Name"].ToString();
string details = dr["Details"].ToString();
string status = Convert.ToBoolean(dr["Status"]) ? ".t." : ".f.";
string createDate = "{^" + Convert.ToDateTime(dr["CreateDate"]).ToString("yyyy-MM-dd") + "}";
builderInsert.Append("INSERT INTO \"C:\test.dbf\" ([ID], [Name], [Details], [Status], [CreateDate]) VALUES (" + id + ",\"" + name + "\",\"" + details + "\"," + status + "," + createDate + ")" + Environment.NewLine);
scriptCommand.CommandType = CommandType.StoredProcedure;
scriptCommand.CommandText = "ExecScript";
scriptCommand.Parameters.Add("myScript", OleDbType.Char).Value = builderInsert;
scriptCommand.ExecuteNonQuery();
builderInsert = "";
}
}
Please let me know if you have other concerns. Don't forget to install this one in your machine. VFP OLE DB
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
using System.Data.SqlClient;
using System.Data.OleDb;
Then, within a method to move the data
void MoveFromSQLToDBF()
{
// have a table to pull down your SQL Data...
var dataFromSQL = new DataTable();
// However your connection to your SQL-Server
using (var sqlConn = new SqlConnection("YourSQLConnectionString"))
{
using (var sqlCmd = new SqlCommand("", sqlConn))
{
// Get all records from your table
sqlCmd.CommandText = "select * from CountryMaster";
sqlConn.Open();
var sqlDA = new SqlDataAdapter();
// populate into a temp C# table
sqlDA.Fill(dataFromSQL);
sqlConn.Close();
}
}
// Now, create a connection to VFP
// connect to a PATH where you want the data.
using (var vfpConn = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=C:\SomePathOnYourMachine\"))
{
using (var vfpCmd = new OleDbCommand("", vfpConn))
{
// Create table command for VFP
vfpCmd.CommandText = "CREATE TABLE testFromSQL ( ID Numeric(18,0), [Name] Char(100) NULL, [Details] Char(200) NULL, [Status] Logical NULL, [CreateDate] DATETIME NULL)";
vfpConn.Open();
vfpCmd.ExecuteNonQuery();
// Now, change the command to a SQL-Insert command, but PARAMETERIZE IT.
// "?" is a place-holder for the data
vfpCmd.CommandText = "insert into testFromSQL "
+ "( ID, [Name], [Details], [Status], [CreateDate]) values ( ?, ?, ?, ?, ? )";
// Parameters added in order of the INSERT command above..
// SAMPLE values just to establish a basis of the column types
vfpCmd.Parameters.Add( new OleDbParameter( "parmID", 10000000 ));
vfpCmd.Parameters.Add( new OleDbParameter( "parmName", "sample string" ));
vfpCmd.Parameters.Add(new OleDbParameter( "parmDetails", "sample string" ));
vfpCmd.Parameters.Add(new OleDbParameter( "parmStatus", "sample string" ));
vfpCmd.Parameters.Add( new OleDbParameter( "parmCreateDate", DateTime.Now ));
// Now, for each row in the ORIGINAL SQL table, apply the insert to VFP
foreach (DataRow dr in dataFromSQL.Rows)
{
// set the parameters based on whatever current record is
vfpCmd.Parameters[0].Value = dr["ID"];
vfpCmd.Parameters[1].Value = dr["Name"];
vfpCmd.Parameters[2].Value = dr["Details"];
vfpCmd.Parameters[3].Value = dr["Status"];
vfpCmd.Parameters[4].Value = dr["CreateDate"];
// execute it
vfpCmd.ExecuteNonQuery();
}
// Finally, for compactness, use a VFP Script to copy to Excel (CSV) format
using (var vfpCmd2 = new OleDbCommand("", vfpConn))
{
vfpCmd2.CommandType = CommandType.StoredProcedure;
vfpCmd2.CommandText = "ExecScript";
vfpCmd2.Parameters.Add(new OleDbParameter( "csvScript",
@"Use testFromSQL
copy to YourCompactFile.csv type csv
use" ));
vfpCmd2.ExecuteNonQuery();
}
// close VFP connection
vfpConn.Close();
}
}
}
Since the OleDb does not support copy TYPE CSV, I have found this post on S/O to dump into CSV format for you
