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

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

Answer 2

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 3

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

Answer 4

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.