How to Read/Write Data from DBF file using C#.net application.
Overview:
This document is useful to create import export utility for DBF file. Some time it does require import/export data from DBF file from your DOTNET application. Currently I am working on same kind of requirement where I have developed Export Import Utility it's include export/import data from various file type. One of toughest type is DBF file import export, I did some research on that and got one reboots solution that I want to share with you all.
This document covers the following
1) How to create DBF file database table
2) How to Insert data in created DBF file
3) How to Read data from DBF file.
Introduction:
DBF is a FoxPro database file, this we can create from C#.net application even we can use basic DDL and DML statement on this like 'Create table' , 'Insert table' etc.
If our requirement is to export your data which in is the Dataset (in C#.net) in DBF file this document will help you. You can also read data from any DBF file using this document. Using the same function with different connection string you can use to read / write excel file too.
How function works.
1) I have created one function for Export DBF file; this will takes one dataset as an input parameter
2) Based on input dataset parameter I am creating the database table using 'create table' statement.
3) Then I open the connection using 'Microsoft.Jet.OLEDB.4.0' provider.
4) Using OleDbDataAdapter object I am filling the dataset (dsFill) this will create one DBF table structure with given table name.
5) Now using the Same connection you can fire the next DML statement 'Insert into ..' to insert the data into the DBF file.
6) And same way you can read the data using 'Select..' statement. Fro Import.
Some points are important here
1) DBF file creates with Table name (TEMP.dbf) given in the 'Create table TEMP...' statement.
2) If you are reading the DBF file using 'Select ...' statement the select statement should be like 'Select * from TEMP'.
3) Key words are not acceptable in the DDL or DML statement. Like table, date etc.
4) This will take data source as folder name where DBF file exist like If you want to export / Import DBF file from "C:\Temp\country.dbf." then your Data source will be "C:\Temp" and Table name will be "country".
Source Code:
Follow the simple steps to create application. You can simply copy and past code from here in your application
1) Create the function which will give you the folder path and table name
private void GetFileNameAndPath(string completePath, ref string fileName, ref string folderPath)
{
string[] fileSep = completePath.Split('\\');
for (int iCount = 0; iCount < fileSep.Length; iCount++)
{
if (iCount == fileSep.Length - 2)
{
if (fileSep.Length == 2)
{
folderPath += fileSep[iCount] + "\\";
}
else
{
folderPath += fileSep[iCount];
}
}
else
{
if (fileSep[iCount].IndexOf(".") > 0)
{
fileName = fileSep[iCount];
fileName = fileName.Substring(0, fileName.IndexOf("."));
}
else
{
folderPath += fileSep[iCount] + "\\";
}
}
}
}
Note: Use the folderPath and DataSource in the connection string and fileName in the 'Create tabel' statement.
1) Create ExportDBF function.
// This function takes Dataset (to be exported) and filePath as input parameter and return // bool status as output parameter
// comments are written inside the function to describe the functionality
public bool EportDBF(DataSet dsExport, string filePath)
{
string tableName = string.Empty;
string folderPath = string.Empty;
bool returnStatus = false;
// This function give the Folder name and table name to use in
// the connection string and create table statement.
GetFileNameAndPath(filePath, ref tableName, ref folderPath);
// here you can use DBASE IV also
string connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + folderPath + "; Extended Properties=DBASE III;";
string createStatement = "Create Table " + tableName + " ( ";
string insertStatement = "Insert Into " + tableName + " Values ( ";
string insertTemp = string.Empty;
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(connString);
if (dsExport.Tables[0].Columns.Count <= 0) { throw new Exception(); }
// This for loop to create "Create table statement" for DBF
// Here I am creating varchar(250) datatype for all column.
// for formatting If you don't have to format data before
// export then you can make a clone of dsExport data and transfer // data in to that no need to add datatable, datarow and
// datacolumn in the code.
for (int iCol = 0; iCol < dsExport.Tables[0].Columns.Count; iCol++)
{
createStatement += dsExport.Tables[0].Columns[iCol].ColumnName.ToString();
if (iCol == dsExport.Tables[0].Columns.Count - 1)
{
createStatement += " varchar(250) )";
}
else
{
createStatement += " varchar(250), ";
}
}
//Create Temp Dateset
DataSet dsCreateTable = new DataSet();
//Open the connection
conn.Open();
//Create the DBF table
DataSet dsFill = new DataSet();
OleDbDataAdapter daInsertTable = new OleDbDataAdapter(createStatement, conn);
daInsertTable.Fill(dsFill);
//Adding One DataTable into the dsCreatedTable dataset
DataTable dt = new DataTable();
dsCreateTable.Tables.Add(dt);
for (int row = 0; row < dsExport.Tables[0].Rows.Count; row++)
{
insertTemp = insertStatement;
//Adding Rows to the dsCreatedTable dataset
DataRow dr = dsCreateTable.Tables[0].NewRow();
dsCreateTable.Tables[0].Rows.Add(dr);
for (int col = 0; col < dsExport.Tables[0].Columns.Count; col++)
{
if (row == 0)
{
//Adding Columns to the dsCreatedTable dataset
DataColumn dc = new DataColumn();
dsCreateTable.Tables[0].Columns.Add(dc);
}
// Remove Special character if any like dot,semicolon,colon,comma // etc
dsExport.Tables[0].Rows[row][col].ToString().Replace("LF", "");
// do the formating if you want like modify the Date symbol , //thousand saperator etc.
dsCreateTable.Tables[0].Rows[row][col] = dsExport.Tables[0].Rows[row][col].ToString().Trim();
} // inner for loop close
// Create Insert Statement
if (col == dsExport.Tables[0].Columns.Count - 1)
{
insertTemp += "'" + dsCreateTable.Tables[0].Rows[row][col] + "' ) ;";
}
else
{
insertTemp += "'" + dsCreateTable.Tables[0].Rows[row][col] + "' , ";
}
// This lines of code insert Row One by one to above created
// datatable.
daInsertTable = new OleDbDataAdapter(insertTemp, conn);
daInsertTable.Fill(dsFill);
} // close outer for loop
MessageBox.Show("Exported done Successfully to DBF File.");
returnStatus = true;
} // close function
Note: for formatting like date and thousand separators add below lines of code while transfer data from one dataset to another.
1) Char decimalSymbol = '/'; /. Or "."
dsCreateTable.Tables[0].Rows[row][col] = Convert.ToString(Convert.ToDecimal(dsExport.Tables[0].Rows[row][col].ToString().Trim())).Replace('.', decimalSymbol);
2) string thousandSeparator = "#" + "" + "###"; // or ","
dsCreateTable.Tables[0].Rows[row][col] = Convert.ToString(Convert.ToDouble(dsExport.Tables[0].Rows[row][col].ToString().Trim()).ToString(thousandSeparator));
3) string dateFormat ="MM/dd/yyyy";
dsCreateTable.Tables[0].Rows[row][col] = Convert.ToString(Convert.ToDateTime(dsExport.Tables[0].Rows[row][col].ToString().Trim()).ToString(dateFormat));
2) Create ImportDBF function.
// This function takes filePath as input parameter and return DataSet as output parameter
// comments are written inside the function to describe the functionality
public DataSet ImportDBF(string filePath)
{
string ImportDirPath = string.Empty;
string tableName = string.Empty;
// This function give the Folder name and table name to use in
// the connection string and create table statement.
GetFileNameAndPath(filePath, ref tableName, ref ImportDirPath);
DataSet dsImport = new DataSet();
string thousandSep = thousandSeparator;
string connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + ImportDirPath + "; Extended Properties=DBASE IV;";
OleDbConnection conn = new OleDbConnection(connString);
DataSet dsGetData = new DataSet();
OleDbDataAdapter daGetTableData = new OleDbDataAdapter("Select * from " + tableName , conn);
// fill all the data in to dataset
daGetTableData.Fill(dsGetData);
DataTable dt = new DataTable(dsGetData.Tables[0].TableName.ToString());
dsImport.Tables.Add(dt);
// here I am copying get Dataset into another dataset because //before return the dataset I want to format the data like change //"datesymbol","thousand symbol" and date format as did while
// exporting. If you do not want to format the data then you can // directly return the dsGetData
for (int row = 0; row < dsGetData.Tables[0].Rows.Count; row++)
{
DataRow dr = dsImport.Tables[0].NewRow();
dsImport.Tables[0].Rows.Add(dr);
for (int col = 0; col < dsGetData.Tables[0].Columns.Count; col++)
{
if (row == 0)
{
DataColumn dc = new DataColumn(dsGetData.Tables[0].Columns[col].ColumnName.ToString());
dsImport.Tables[0].Columns.Add(dc);
}
if (!String.IsNullOrEmpty(dsGetData.Tables[0].Rows[row][col].
ToString()))
{
dsImport.Tables[0].Rows[row][col] = Convert.ToString(dsGetData.Tables[0].Rows[row][col].ToString().Trim());
}
} // close inner for loop
}// close ouer for loop
MessageBox.Show("Import done Successfully to DBF File.");
Return dsImport;
} // close function
Note: Same function you can use to Export Import from Excel file also
Use
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
// filePath = complete path like "C:\Temp\country.xls"
Import Excel
string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filePath + ";Extended Properties=Excel 8.0";
OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + fileName + "$]", strconn);
DataSet dsExcel = new DataSet();
da.Fill(dsExcel);
Export Excel
Same as Export DBF file with different connection string.
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
// filePath = complete path like "C:\Temp\country.xls"
Windows Requirements
Windows 2000, XP and above
Language
C#.NET and ASP.NET, Visual Studio 2003 and above
