How Read DBF File And Save Data Into Database In C#

Here, in this blog, we will read the DBF file using OleDbConnection and will store the data in two tables.

In the first table, we store the columns name and data type containing the DBF file and in the second table, we store the added data.

Use the below namespace.

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;

Declare the given variables.

DataTable dtData, dtColumn;
string rfileName = @"E:\AMIT\DBF_Files\test.dbf";
string conString = "Data Source=.; Initial Catalog=myDataBase; Integrated Security=SSPI";
public IList<DbfFieldDescriptor> FieldDescriptors { get; set; }

On page load, we have to read the columns and data contained in the DBF file using OleDbConnection.

private void Form1_Load(object sender, EventArgs e)
{
string filePath = Path.GetDirectoryName(rfileName);
OleDbConnection connection = new OleDbConnection("Provider=VFPOLEDB.1;Data Source=" + filePath + ";");
connection.Open();
DataTable tables = connection.GetSchema(OleDbMetaDataCollectionNames.Tables);
dtColumn = null;
string fName = Path.GetFileNameWithoutExtension(rfileName);
foreach (DataRow rowTables in tables.Rows)
{
if (rowTables["table_name"].ToString().ToUpper() == fName.ToUpper())
{
DataTable columns = connection.GetSchema(OleDbMetaDataCollectionNames.Columns,
new String[] { null, null, rowTables["table_name"].ToString(), null });
dtColumn = GetColumnDataTable();
foreach (System.Data.DataRow rowColumns in columns.Rows)
{
DataRow dr = dtColumn.NewRow();
dr[0] = rowColumns["column_name"].ToString();
dr[1] = OleDbType(int.Parse(rowColumns["data_type"].ToString()));
dr[2] = rowColumns["data_type"].ToString();
dr[3] = rowColumns["numeric_precision"].ToString();
dtColumn.Rows.Add(dr);
}
break;
}
}
string sql = "SELECT * FROM " + fName;
OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter DA = new OleDbDataAdapter(cmd);
dtData = new DataTable();
DA.Fill(dtData);
connection.Close();
WriteDataToDatabase(dtData, dtColumn);
}
// In this method we have create the table that will be stored the columns name and datatype.
static DataTable GetColumnDataTable()
{
DataTable table = new DataTable();
table.Columns.Add("NAME", typeof(string));
table.Columns.Add("TYPE", typeof(string));
table.Columns.Add("TYPENO", typeof(string));
table.Columns.Add("DEC", typeof(string));
return table;
}
// This method return the datatype name.
public string OleDbType(int type)
{
string dataType;
switch (type)
{
case 10:
dataType = "BigInt";
break;
case 128:
dataType = "Byte";
break;
case 11:
dataType = "Boolean";
break;
case 8:
dataType = "String";
break;
case 129:
dataType = "String";
break;
case 6:
dataType = "Currency";
break;
case 7:
dataType = "DateTime";
break;
case 133:
dataType = "DateTime";
break;
case 134:
dataType = "TimeSpan";
break;
case 135:
dataType = "DateTime";
break;
case 14:
dataType = "Decimal";
break;
case 5:
dataType = "Double";
break;
case 3:
dataType = "Integer";
break;
case 201:
dataType = "String";
break;
case 203:
dataType = "String";
break;
case 204:
dataType = "Byte";
break;
case 200:
dataType = "String";
break;
case 139:
dataType = "Decimal";
break;
case 202:
dataType = "String";
break;
case 130:
dataType = "String";
break;
case 131:
dataType = "Decimal";
break;
case 64:
dataType = "DateTime";
break;
default:
dataType = "";
break;
}
return dataType;
}

After reading the DBF file, here, we need to create the table and save the data.

public void WriteDataToDatabase(DataTable dtData, DataTable dtCol)
{
string TableName = Path.GetFileNameWithoutExtension(rfileName);
string FilePath = rfileName;
SqlConnection dbCon = new SqlConnection(conString);
if (dbCon.State == ConnectionState.Closed)
dbCon.Open();
string strQuery = "IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'" + TableName + "')) BEGIN SELECT 1 END ELSE BEGIN SELECT 0 END";
SqlCommand dbCmd = new SqlCommand(strQuery, dbCon);
SqlDataAdapter dbDa = new SqlDataAdapter(dbCmd);
DataTable dtExist = new DataTable();
dbDa.Fill(dtExist);
int valReturn = int.Parse(dtExist.Rows[0][0].ToString());
if (valReturn == 0)
{
ReadFileStream(FilePath, dtCol);
CreateDbTable(TableName);
}
saveToDb(dtData, TableName, dtCol);
}
// this method will create table in the database.
void CreateDbTable(string TableName)
{
try
{
SqlConnection dbCon = new SqlConnection(conString);
if (dbCon.State == ConnectionState.Closed)
dbCon.Open();
using (SqlCommand cmd = dbCon.CreateCommand())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($"CREATE TABLE [{TableName}] (");
bool first = true;
foreach (var fieldDescriptor in FieldDescriptors)
{
if (first)
first = false;
else
sb.Append(", ");
sb.AppendLine($"[{fieldDescriptor.Name}] {fieldDescriptor.GetSqlDataType()}");
}
sb.Append(", ");
sb.AppendLine($"[{"lactivestatus"}] {"bit"}");
sb.AppendLine($")");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
throw new Exception($"Failed to create table {TableName}", e);
}
}
// Here we save records to the database.
public void saveToDb(DataTable dtOne, string TableName, DataTable dtColumn)
{
if (dtOne.Rows.Count > 0)
{
SqlConnection dbCon = new SqlConnection(conString);
for (int n = 0; n < dtOne.Rows.Count; n++)
{
if (dbCon.State == ConnectionState.Closed)
dbCon.Open();
string strQry = "";
strQry = "INSERT INTO [" + TableName + "] VALUES(";
for (int i = 0; i < dtColumn.Rows.Count; i++)
{
if (i == dtColumn.Rows.Count - 1)
{
if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "boolean" || dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "logical")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "'False','true')";
else
strQry = strQry + "'" + dtOne.Rows[n][i].ToString() + "','true')";
}
else if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "string")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "'','true')";
else
strQry = strQry + "'" + dtOne.Rows[n][i].ToString().Replace("'", "") + "','true')";
}
else if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "byte")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "'0','true')";
else
strQry = strQry + "'" + Encoding.ASCII.GetBytes(dtOne.Rows[n][i].ToString()) + "','true')";
}
else if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "character")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "'','true')";
else
strQry = strQry + "'" + dtOne.Rows[n][i].ToString().Replace("'", "") + "','true')";
}
else if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "datetime" || dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "date")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "null,'true')";
else
strQry = strQry + "'" + DateTime.Parse(dtOne.Rows[n][i].ToString()) + "','true')";
}
else
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "0,'true')";
else
strQry = strQry + dtOne.Rows[n][i].ToString() + ",'true')";
}
}
else
{
if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "boolean" || dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "logical")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "'False',";
else
strQry = strQry + "'" + dtOne.Rows[n][i].ToString() + "',";
}
else if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "string")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "'',";
else
strQry = strQry + "'" + dtOne.Rows[n][i].ToString().Replace("'", "") + "',";
}
else if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "byte")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "'0',";
else
strQry = strQry + "'" + Encoding.ASCII.GetBytes(dtOne.Rows[n][i].ToString()) + "',";
}
else if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "character")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "'',";
else
strQry = strQry + "'" + dtOne.Rows[n][i].ToString().Replace("'", "") + "',";
}
else if (dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "datetime" || dtColumn.Rows[i]["TYPE"].ToString().ToLower() == "date")
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "null,";
else
strQry = strQry + "'" + DateTime.Parse(dtOne.Rows[n][i].ToString()) + "',";
}
else
{
if (string.IsNullOrWhiteSpace(dtOne.Rows[n][i].ToString()))
strQry = strQry + "0,";
else
strQry = strQry + dtOne.Rows[n][i].ToString() + ",";
}
}
}
try
{
SqlCommand dbCmd1 = new SqlCommand(strQry, dbCon);
dbCmd1.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex.Message);
}
}
dbCon.Close();
}
}
void ReadFileStream(string FilePath, DataTable dtCol)
{
FileStream fileStream = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.Read);
BinaryReader binaryReader = new BinaryReader(fileStream);
var fieldDescriptors = new List<DbfFieldDescriptor>();
try
{
int no = 0;
while (true)
{
var fieldDescriptor = ReadFieldDescriptor(binaryReader, no++, dtCol);
if (fieldDescriptor == null)
break;
if (no > 1)
fieldDescriptors.Add(fieldDescriptor);
}
}
catch (Exception e)
{
throw new Exception("Failed to read field descriptors", e);
}
FieldDescriptors = fieldDescriptors;
}
DbfFieldDescriptor ReadFieldDescriptor(BinaryReader br, int fdNo, DataTable dtCol)
{
var fieldDescriptor = new DbfFieldDescriptor();
fieldDescriptor.No = fdNo;
string name = "";
if (fdNo > 0 && fdNo <= dtCol.Rows.Count)
name = dtCol.Rows[fdNo - 1][0].ToString();
try
{
var fieldNameBytes = new byte[11];
fieldNameBytes[0] = br.ReadByte();
if (fieldNameBytes[0] == 0x0D)
return null; // 0x0D means end of field descriptor list
br.Read(fieldNameBytes, 1, 10);
fieldDescriptor.Name = name;
fieldDescriptor.TypeChar = (char)br.ReadByte();
br.ReadByte(); // reserved
br.ReadByte(); // reserved
br.ReadByte(); // reserved
br.ReadByte(); // reserved
fieldDescriptor.Length = br.ReadByte();
fieldDescriptor.DecimalCount = br.ReadByte();
br.ReadBytes(2); // work area id
br.ReadByte(); // example
br.ReadBytes(10); // reserved
br.ReadByte(); // production mdx
return fieldDescriptor;
}
catch (Exception e)
{
if (string.IsNullOrWhiteSpace(fieldDescriptor.Name))
throw new Exception($"Failed to read field descriptor #{fdNo + 1}", e);
else
throw new Exception($"Failed to read field descriptor #{fdNo + 1} ({fieldDescriptor.Name})", e);
}
}

Create a new class DbfFieldDescriptor.

public class DbfFieldDescriptor
{
public int No { get; set; }
public string Name { get; set; }
public char TypeChar { get; set; }
public int Length { get; set; }
public byte DecimalCount { get; set; }
public string GetSqlDataType()
{
switch (TypeChar)
{
case 'C':
return $"VARCHAR({Length})";
case 'I':
return "INT";
case 'N':
return $"DECIMAL({Length + 1}, {DecimalCount})";
case 'L':
return "BIT";
case 'B':
return "BIT";
case 'D':
return "DATETIME";
case 'M':
return "VARCHAR(MAX)";
case 'S':
return "VARCHAR(MAX)";
case 'T':
return "DATETIME";
case 'W': //?
return "VARCHAR(MAX)";
case '0':
return "INT";
case 'G':
return "VARCHAR(MAX)";
case 'F':
return "FLOAT";
case 'Y':
return "NUMERIC(18,4)";
default:
throw new NotSupportedException();
}
}
}

12 Comments

Join the conversation! Your thoughts help the community grow.

Chris W5y ago
How does this translate to VB? "DataTable columns = connection.GetSchema(OleDbMetaDataCollectionNames.Columns, new String[] { null, null, rowTables["table_name"].ToString(), null })"
usha lekkala6y ago
I have no idea about dbf file.. my question is i want to generate dbf file from sql table in windows.
- Amit Mohanty6y ago
  Message me your mail id, I will send an example or refer the link: https://github.com/henck/dBASE.NET
- Amit Mohanty6y ago
  Using dBASE.NET;using System.Data; using System.Data.SqlClient; namespace MyConsoleApp { /// <summary> /// First you have to add dBASE.NET library to your application /// Go to Nuget package manager and add dBASE.NET or open NuGet package manager console and run this /// PM> Install-Package dBASE.NET -Version 1.2.1 /// /// Database table script /*CREATE TABLE[dbo].[Employee] ( [Id][int] IDENTITY(1,1) NOT NULL, [EmpCode] [nvarchar] (50) NULL, [EmpName] [nvarchar] (150) NULL, [PhoneNumber] [nvarchar] (15) NULL, [EmailAddress] [nvarchar] (150) NULL, [Gender] [nvarchar] (1) NULL, [DOB] [date] NULL, [Department] [nvarchar] (50) NULL, [Designation] [nvarchar] (50) NULL, [Country] [nvarchar] (50) NULL, [Salary] [numeric] (18, 2) NULL, [Status] [nvarchar] (10) NULL, [CreatedDate] [datetime] NULL )*/ /// </summary> class DbfReadWrite { static Dbf dbf; public static void CreateDbfFile() { SqlConnection sqlConnection = new SqlConnection("Data Source=.; Initial Catalog=DemoTest; Integrated Security=SSPI"); sqlConnection.Open(); SqlCommand sqlCommand = new SqlCommand("Select * From Employee", sqlConnection); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand); DataTable dataTable = new DataTable(); sqlDataAdapter.Fill(dataTable); DataTable columns = sqlConnection.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, "Employee", null }); DataTable dtColumn = new DataTable(); dtColumn.Columns.Add("NAME", typeof(string)); dtColumn.Columns.Add("DATATYPE", typeof(string)); dtColumn.Columns.Add("DEC", typeof(string)); foreach (System.Data.DataRow rowColumns in columns.Rows) { DataRow dr = dtColumn.NewRow(); dr[0] = rowColumns["column_name"].ToString(); dr[1] = rowColumns["data_type"].ToString(); dr[2] = rowColumns["numeric_precision"].ToString(); dtColumn.Rows.Add(dr); } dbf = new Dbf(); if (dtColumn.Rows.Count > 0) { dbf = new Dbf(); for (int i = 0; i < dtColumn.Rows.Count; i++) { DbfField field; if (dtColumn.Rows[i][1].ToString() == "numeric") { field = new DbfField(dtColumn.Rows[i][0].ToString(), DbfFieldType.Numeric, 16); } else if (dtColumn.Rows[i][1].ToString() == "date" || dtColumn.Rows[i][1].ToString() == "datetime") { field = new DbfField(dtColumn.Rows[i][0].ToString(), DbfFieldType.Date, 12); } // you have to add more condition for other datatypes. else { field = new DbfField(dtColumn.Rows[i][0].ToString(), DbfFieldType.Character, 12); } dbf.Fields.Add(field); } for (int i = 0; i < dataTable.Rows.Count; i++) { DbfRecord record = dbf.CreateRecord(); for (int j = 0; j < dataTable.Columns.Count; j++) { record.Data[j] = dataTable.Rows[i][j].ToString(); } } } dbf.Write("Employee.dbf", DbfVersion.VisualFoxPro); } } }
- usha lekkala6y ago
  Thankyou for giving fast response.
usha lekkala6y ago
Can you please help me how to generate dbf file based on table data in windows.
- Amit Mohanty6y ago
  Sure. What type of help you need?
usha lekkala6y ago
Can you please help me how to generate dbf file based on table data.
Laxmidhar Sahoo7y ago
Thanks a lot
- Amit Mohanty7y ago
  Thank you...