RSS

DBF is a FoxPro database file …

06 Aug
За би зүгээр хүн тус болох гээд хайсан юм. Хамгийн гайгүй жишээ нь энэ байна. Би бичээгүй ээ энэ кодыг. Завгүй л байгаад байна даа. Тиймээс жишээ тавилаа. Асууж байсан хүн нь аваарай. Ойлгохгүй бол харин асууж болно. Тайлбарлаж шаднаа хэхэ.
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.
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
Энэ кодыг бичсэн гарын нэр: Ritesh Kumar Kesharwani
Хэрэг болох байх аа.
 
5 Comments

Posted by on August 6, 2008 in Other

 

5 responses to “DBF is a FoxPro database file …

  1. bmfdskrbn

    April 6, 2012 at 11:52 am

    http://ges756.narod2.ru АСУ-Импульс предлагает базы различной информации для бизнеса маркетинговых исследований и других целей.

     
  2. inojifxy

    March 16, 2012 at 3:45 am

    Рассылка рекламных сообщений на 165000 форумов 50$.Интернет реклама новосибирск
    rekl245(собака) mail(тут точка) ru

     
  3. bokhoo

    December 27, 2008 at 12:14 pm

    zugeeer deee😀

     
  4. zaka

    December 26, 2008 at 6:27 pm

    hey bayarlala nzaa harin ene yag mysql,mssql gd yanz yanz bdagdaa ternii ali ni yum be talarhsan shuu nzaa

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: