项目结构
设置属性
private const string DbfTAbleName = "dbf_user";
private string _saveFileName = string.Empty;
导出DBF
初始化数据
#region 初始化数据 - InitData()
private List<User> InitData()
{
List<User> list = new List<User>();
for (int i = 0; i < 100; i++)
{
User user = new User() { ID = i, Name = "Jack" + i };
list.Add(user);
}
return list;
}
#endregion
保存路径
#region 保存文件 - SaveData()
private void SaveData()
{
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = "所有DBF文件|*.DBF";//设置文件类型
saveFile.Title = "导出DBF数据";//设置标题
saveFile.AddExtension = true;
saveFile.FileName = DbfTAbleName + ".DBF";
saveFile.AutoUpgradeEnabled = true;
if (saveFile.ShowDialog() == DialogResult.OK)
{
_saveFileName = saveFile.FileName;
}
}
#endregion
开始导出(核心方法)
#region 导出文件 - ExportData()
private void ExportData()
{
#region 1.0 创建OLEDB连接
string basePath = string.Format(@"{0}", AppDomain.CurrentDomain.BaseDirectory);
string mStrConn = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + basePath + @"/;Extended Properties=""dBASE IV;HDR=Yes;"";";
string filePath = string.Format(@"{0}\{1}.DBF", basePath, DbfTAbleName);
if (File.Exists(filePath) == true)
{
File.Delete(filePath);
}
#endregion
#region 2.0 创建DBF表文件
#region 2.1 创建表
using (OleDbConnection connection = new OleDbConnection(mStrConn))
{
connection.Open();
string sqlt = string.Format("create table {0} (ID varchar(20),Name varchar(20))", DbfTAbleName);
OleDbCommand OLDBComm = new OleDbCommand(sqlt, connection);
OLDBComm.ExecuteNonQuery();
OLDBComm.Dispose();
}
#endregion
#region 2.2 删除表
using (OleDbConnection connection = new OleDbConnection(mStrConn))
{
connection.Open();
OleDbCommand OLDBCommIn = new OleDbCommand(string.Format("delete * from {0}", DbfTAbleName), connection);
OLDBCommIn.ExecuteNonQuery();
OLDBCommIn.Dispose();
}
#endregion
#endregion 创建DBF表文件
#region 3.0 导出数据
#region 3.1 查询数据
List<User> userList = InitData();
#endregion
#region 3.2 生成数据
using (OleDbConnection connection = new OleDbConnection(mStrConn))
{
connection.Open();
OleDbCommand OLDBCommInsert = new OleDbCommand();
foreach (User user in userList)
{
string sql_add = string.Format("insert into {0} (ID,Name) values({1},'{2}')", DbfTAbleName,
user.ID, user.Name);
OLDBCommInsert.CommandText = sql_add;
OLDBCommInsert.CommandType = CommandType.Text;
OLDBCommInsert.Connection = connection;
OLDBCommInsert.ExecuteNonQuery();
}
File.Copy(filePath, _saveFileName, true); //copy file
MessageBox.Show("导出数据成功!");
}
#endregion
#endregion
}
#endregion
导出按钮
private void btnExport_Click(object sender, EventArgs e)
{
SaveData();
ExportData();
}
导入DBF
获取DBF文件
#region 获取上传的dbf路径 - void GetDbf(out string dbfDir, out string dbfName)
private void GetDbf(out string dbfDir, out string dbfName)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.InitialDirectory = "@D:\\";
ofd.Filter = "dbf文件|*.dbf";
string filePath = string.Empty;
if (ofd.ShowDialog() == DialogResult.OK)
{
filePath = ofd.FileName;
dbfDir = Path.GetDirectoryName(filePath);
dbfName = Path.GetFileNameWithoutExtension(filePath);
}
else
{
dbfDir = string.Empty;
dbfName = string.Empty;
}
}
#endregion
上传数据
#region 上传数据 - void ImportData(string dbfDir,string dbfName)
private void ImportData(string dbfDir, string dbfName)
{
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = "Driver={Microsoft dBase Driver (*.dbf)};DefaultDir=" + dbfDir;
conn.Open();
string strQuery = "select * from " + dbfName;
OdbcDataAdapter da = new OdbcDataAdapter(strQuery, conn.ConnectionString);
DataTable table = new DataTable();
da.Fill(table);
dgv.DataSource = table;
}
#endregion
导入按钮
private void btnImport_Click(object sender, EventArgs e)
{
string dbfDir = "";
string dbfName = "";
GetDbf(out dbfDir, out dbfName);
if (MessageBox.Show("导入数据", "请确认信息", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
ImportData(dbfDir, dbfName);
}
}