查询结果生成DataTable,一般是导出xls或xml格式,但有时也需要直接生成DBF文件,以下代码需要安装VFP PROVIDER驱动,在X86模式下编译,如有更方便的方式,麻烦高手指点下。
public bool exportDataTableToDbf(DataTable dt)
{
if (dt.Rows.Count == 0)
return false;
SaveFileDialog sf = new SaveFileDialog();
sf.InitialDirectory = Application.StartupPath;
sf.Filter = "DBF文件(*.dbf)|*.dbf";
sf.Title = "导出为dbf文件";
DialogResult dResult = sf.ShowDialog();
if (dResult == DialogResult.OK)
{
string strDbfFullPath = sf.FileName;
if (System.IO.File.Exists(strDbfFullPath))
System.IO.File.Delete(strDbfFullPath);
string dbfPath = System.IO.Path.GetDirectoryName(strDbfFullPath);
string dbfName = System.IO.Path.GetFileNameWithoutExtension(strDbfFullPath);
if (dbfPath.Substring(dbfPath.Length - 1) != @"\")
dbfPath += @"\";
//必须安装VFP Provider 驱动,否则无法运行
string connStr = @"Provider=VFPOLEDB.1;Data Source=" + dbfPath;
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr);
conn.Open();
string sql = "create table " + dbfName + " (";
string sqlInsertHead = "insert into " + dbfName + " (";
List<string> columnList = new List<string> { };
foreach (DataColumn dc in dt.Columns)
{
sql += dc.ColumnName + " " + getDbfDataTypeFromDataColumn(dc) + ",";
sqlInsertHead += dc.ColumnName + ",";
columnList.Add(dc.ColumnName);
}
sql = sql.Substring(0, sql.Length - 1) + ")";
sqlInsertHead = sqlInsertHead.Substring(0, sqlInsertHead.Length - 1) + ") values (";
MessageBox.Show(sqlInsertHead);
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
int iResult = cmd.ExecuteNonQuery();
string curSql=string.Empty;
foreach (DataRow drow in dt.Rows)
{
try
{
string sqlInsertTail = sqlInsertHead;
foreach (string colName in columnList)
{
//分字符串和非字符串两种情况处理
if (drow[colName].GetType() == typeof(System.String))
{
sqlInsertTail += "'" + drow[colName].ToString() + "',";
}
else
{
//sqlInsertTail += drow.IsNull(colName) ? DBNull.Value.ToString(): drow[colName].ToString() + ","; //当dataColumn值为空时处理
if (drow[colName].GetType() == typeof(System.DBNull)) //防止有些查询生成的DataTalbe中有空值,导致报错
drow[colName] = 0;
sqlInsertTail += drow[colName].ToString()+",";
}
}
sqlInsertTail = sqlInsertTail.Substring(0, sqlInsertTail.Length - 1) + ")";
curSql = sqlInsertTail;
cmd = new System.Data.OleDb.OleDbCommand(sqlInsertTail, conn);
iResult = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(curSql+e.ToString());
}
}
conn.Close();
return true;
}
else
{
return false;
}
}