查询结果生成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;
            }
        }