c#操作DBF数据库文件
protected void WriteProofsToFile(IEnumerable<KingDeeProofMdl> datas, string destFileName)
{
var dirFullName = Path.GetDirectoryName(destFileName);
var fileName = Path.GetFileNameWithoutExtension(destFileName);
string[] fields = { "FDATE", "FTRANSDATE", "FPERIOD", "FGROUP", "FNUM", "FENTRYID", "FEXP", "FACCTID", "FCLSNAME1", "FOBJID1", "FOBJNAME1", "FCLSNAME2", "FOBJID2", "FOBJNAME2", "FCYID", "FEXCHRATE", "FDC", "FFCYAMT", "FQTY", "FPRICE", "FDEBIT", "FCREDIT", "FATTCHMENT", "FPOSTED", "FDELETED" };
//string Constring = string.Format("Dsn=dBASE Files;dbq={0};defaultdir={0};driverid=533;maxbuffersize=2048;pagetimeout=10", dirFullName);
//string Constring = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=dBASE IV;User ID=Admin;Password=;", dirFullName);
string Constring = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=dBASE IV;User ID=Admin;", dirFullName);
string cmdstring = string.Format("insert into {0}({1}) values({2})", fileName, string.Join(",", fields), string.Join(",", fields.Select(x => "?")));
using (OleDbConnection OdbcCon = new OleDbConnection(Constring))
using (OleDbCommand Cmd = new OleDbCommand(cmdstring, OdbcCon))
{
OdbcCon.Open();
IDictionary<string, PropertyInfo> propInfos = typeof(KingDeeProofMdl).GetProperties().ToDictionary(x => x.Name);
foreach (var item in datas)
{
Cmd.Parameters.Clear();
foreach (var field in fields)
{
var param = Cmd.CreateParameter();
param.ParameterName = field;
param.Value = propInfos.ContainsKey(field) ? propInfos[field].GetValue(item, null) ?? DBNull.Value : DBNull.Value;
param.OleDbType = param.Value is bool ? OleDbType.Boolean : OleDbType.VarChar;
//不能将decimal映射为OdbcType.Decimal,会抛异常,将除bool外全映射为OdbcType.NVarChar处理。
//System.Data.Odbc.OdbcException : ERROR [07006फ़] [Microsoft][ODBC dBASE Driver]受限的数据类型属性破坏
Cmd.Parameters.Add(param);
}
Cmd.ExecuteNonQuery();
}
OdbcCon.Close();
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Sql;
using System.IO;
//原理:c#操作DBF的时候,会把你刚选择的文件夹当成是一个数据库,而这个另类的“数据库“里的各个DBF文件的名字(不包括扩展名)便是它的表了。
namespace cs_control_dbf_folder
{
public partial class Form1 : Form
{
string dbfPath = string.Empty; //你存放DBF文件的那个文件夹
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(dbfPath))
{
MessageBox.Show("还没选择文件夹!");
return;
}
if (string.IsNullOrEmpty(comboBox1.Text))
{
MessageBox.Show("没有选择数据文件");
return;
}
string connectString = string.Format(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=dBASE IV;User ID=Admin;Password=;"
, dbfPath);
using (OleDbConnection connection = new OleDbConnection(connectString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OleDbDataAdapter command = new OleDbDataAdapter("select * from " + comboBox1.Text, connection);
command.Fill(ds, "ds");
MessageBox.Show(ds.Tables[0].Rows.Count.ToString());
}
catch (Exception ex)
{
MessageBox.Show(string.Format("error:{0}", ex.Message));
}
}
}
private void button2_Click(object sender, EventArgs e)
{
if (folderBrowserDialog1.ShowDialog() == DialogResult.Cancel)
return;
dbfPath = folderBrowserDialog1.SelectedPath;
DirectoryInfo di = new DirectoryInfo(dbfPath);
comboBox1.Items.Clear();
foreach (FileInfo fi in di.GetFiles())
{
if (fi.Extension.ToLower() == ".dbf")
this.comboBox1.Items.Add(fi.Name.Substring(0, fi.Name.LastIndexOf(fi.Extension)));
}
if (comboBox1.Items.Count == 0)
{
MessageBox.Show("此文件夹中没有数据文件,请确认");
}
else
{
comboBox1.SelectedIndex = 0;
}
}
}
}