C#导出Visual FoxPro(.dbf)数据库文件

前些天由于客户需求,C#开发的程序需要做一个导出Visual FoxPro(.dbf)数据库文件的功能。

不会呀,从网上找了好长时间,终于被我找到了,自己稍作修改,经测试,可以导出,并且导出的文件可以正常打开,前提是已经安装了Visual FoxPro

相关代码:

private void simpleButton1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            DataColumn dc = new DataColumn();
            dc.ColumnName = "CH";
            dt.Columns.Add(dc);
            DataRow row = dt.NewRow();
            row["CH"] = "1234567";
            dt.Rows.Add(row);
            dt.TableName = "testTable";
            string eCode = "010";
            string eCongtent = "文件导出失败";
            new BaseBLL().CreatFile(dt, out eCode, out eCongtent, @"D:\");
        }


public int ExcCommandText(string sqlString,out string errCode,out string errMsg)
        {
            string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\;Extended Properties=dBASE IV;User ID=Admin;Password=";
            OleDbConnection olecon = new OleDbConnection(connStr.ToString());
            OleDbCommand sda = new OleDbCommand();
            sda.CommandText = sqlString;
            sda.Connection = olecon;
            int result = -1;
            errCode = "-1";
            errMsg = "未执行";
            try
            {
                using (olecon)
                {
                    olecon.Open();
                    result = sda.ExecuteNonQuery();
                    errCode = "1";
                    errMsg = "执行成功";
                    olecon.Close();
                }
            }
            catch
            {
                result = -2;
                errCode = "0";
                errMsg = "执行失败";
            }
            return result;
        }






        #region Creat file
        /// <summary>
        /// 创建数据文件
        /// </summary>
        /// <param name="dt">需要创建的数据</param>
        /// <param name="errMsg"></param>
        /// <returns></returns>
        public int CreatFile(DataTable dt, out string errCode, out string errMsg,string filePath)
        {
            errCode = null; errMsg = null; int rtn = 0;
            try
            {
                //需创建的文件名称
                string fileName = dt.TableName + ".dbf";
                //判断文件是否存在
                DirectoryInfo dir = new DirectoryInfo(filePath);
                if (dir != null)
                {
                    if (dir.GetFiles(fileName).Length > 0)
                    {
                        //已经存在则删除原有数据
                        rtn = this.ExcCommandText("drop table " + dt.TableName, out errCode, out errMsg);
                        if (rtn != 0)
                        {
                            return rtn;
                        }
                    }
                }
                //构建创建文件数据结构语句
                string sqlCreat = "create table " + dt.TableName + "({0})";
                string columns = "";
                foreach (DataColumn col in dt.Columns)
                {
                    columns += col.ColumnName;
                    columns += " char(100),";
                }
                columns = columns.Remove(columns.Length - 1, 1);
                sqlCreat = string.Format(sqlCreat, columns);


                rtn = ExcCommandText(sqlCreat, out errCode, out errMsg);
                if (rtn != 0)
                {
                    return rtn;
                }
                //导入数据
                foreach (DataRow row in dt.Rows)
                {
                    string sqlInsert = "insert into " + dt.TableName + " values({0})";
                    string invalues = "";
                    foreach (DataColumn col in dt.Columns)
                    {
                        invalues += "'" + row[col].ToString() + "',";
                    }
                    invalues = invalues.Remove(invalues.Length - 1, 1);
                    sqlInsert = string.Format(sqlInsert, invalues);
                    rtn = ExcCommandText(sqlInsert, out errCode, out errMsg);
                    if (rtn != 0)
                    {
                        return rtn;
                    }
                }
                return rtn;
            }
            catch (System.Exception e)
            {
                errMsg = e.Message + "|" + e.StackTrace;
                return -1;
            }
        }
        #endregion 


c# DBF数据库导入导出实例 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.Odbc; using System.Data.SqlClient; namespace DbfExample { public partial class Form1 : Form { System.Data.Odbc.OdbcConnection conn; public Form1() { InitializeComponent(); } //导出数据 private void btnOut_Click(object sender, EventArgs e) { string connect = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\\; "; OdbcConnection myconn = new OdbcConnection(connect); string sqlt ="CREATE TABLE aa.DBF (cc int(10))"; myconn.Open(); OdbcCommand olec = new OdbcCommand(sqlt, myconn); try { int i = olec.ExecuteNonQuery(); MessageBox.Show("'" + i + "'success"); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { olec.Dispose(); myconn.Close(); } //string ole_connstring = @"Provider=microsoft.jet.oledb.5.0;Data Source=D:\;"; //System.Data.OleDb.OleDbConnection ole_conn = new System.Data.OleDb.OleDbConnection(ole_connstring); //try //{ // ole_conn.Open(); // System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand // ("Create Table TestTable (Field1 int, Field2 char(10),Field float(10,2))", // ole_conn); // System.Data.OleDb.OleDbCommand cmd2 = new System.Data.OleDb.OleDbCommand // ("Insert Into TestTable values (1,'Hello3',520.20)", ole_conn); // System.Data.OleDb.OleDbCommand cmd3 = new System.Data.OleDb.OleDbCommand // ("Insert Into TestTable values (2,'Hello4',18076.60)", ole_conn); // cmd1.ExecuteNonQuery(); // cmd2.ExecuteNonQuery(); // cmd3.ExecuteNonQuery(); //} //catch (Exception ex) //{ // MessageBox.Show(ex.Message); //} //finally //{ // ole_conn.Close(); //} } //导入数据 private void btnIn_Click(object sender, EventArgs e) { } private void Form1_Load(object sender, EventArgs e) { Bind(); } private void Bind() { try { conn = new System.Data.Odbc.OdbcConnection(); string table = @"C:\测试例子\Dbf\prepur.dbf"; string connStr = @"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + table + ";Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO"; conn.ConnectionString = connStr; conn.Open(); string sql = @"select * from " + table; OdbcDataAdapter da = new OdbcDataAdapter(sql, conn); DataTable dt = new DataTable(); da.Fill(dt); this.dataGridView1.DataSource = dt.DefaultView; //MessageBox.Show(dt.Rows[0][0].ToString()); } catch { } finally { conn.Close(); } } private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) { try { conn = new System.Data.Odbc.OdbcConnection(); string table = @"C:\测试例子\Dbf\table1.dbf"; string connStr = @"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + table + ";Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO"; conn.ConnectionString = connStr; conn.Open(); string id = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString(); string sql = @"select * from " + table + " where id='" + id + "'"; OdbcDataAdapter da = new OdbcDataAdapter(sql, conn); DataTable dt = new DataTable(); da.Fill(dt); txtId.Text = id; txtName.Text = dt.Rows[0]["name"].ToString(); txtAddress.Text = dt.Rows[0]["address"].ToString(); } catch { } finally { conn.Close(); } } private void Add() { conn = new System.Data.Odbc.OdbcConnection(); string table = @"C:\temp\Dbf\table1.dbf"; string connStr = @"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + table + ";Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO"; conn.ConnectionString = connStr; conn.Open(); OdbcCommand cmd = new OdbcCommand(); cmd.Connection = conn; string sql = "insert into " + table + " values('" + txtId.Text + "','" + txtName.Text + "','" + txtAddress.Text + "')"; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); Bind(); } private void btnTOSQL_Click(object sender, EventArgs e) { try { string sql = "Insert Into dbftosql select * From openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=C:\\temp\\Dbf','select * from table1.dbf')"; SqlConnection con = new SqlConnection("server=.;database=labelprint;uid=sa;pwd=sa"); con.Open(); SqlCommand cmd = new SqlCommand(sql, con); cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } } }
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值