C#操作VFP的dbf数据库文件实例

实例中分别使用Oledb和Odbc操作vfp数据库dbf文件，操作包括：读取，增删改。

已测试可直接使用，使用方法：下面代码分两个部分aspx和cs，在vs2005中新建网站项目，分别copy文件到想要的文件中即可。

aspx页面：

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
       <center> <h2>C#操作VFP的dbf数据库文件实例</h2><hr />
        <asp:gridview Width="400px" ID="Gridview1" runat="server"></asp:gridview>
            <asp:Button ID="btnInti" runat="server" OnClick="btnInti_Click" Text="createTable" />
        <br />
        <asp:Button ID="btnInsert" runat="server" OnClick="btnInsert_Click" Text="btnInsert" />
        <asp:Button ID="btnUpdate" runat="server" OnClick="btnUpdate_Click" Text="btnUpdate" />
        <asp:Button ID="btnDelete" runat="server" OnClick="btnDelete_Click" Text="btnDelete" />      
        <br />
        <asp:Button ID="btnShowWithOledb" runat="server" OnClick="btnShowWithOledb_Click"
            Text="showWithOledb" />
        <asp:Button ID="btnShowWithOdbc" runat="server" OnClick="btnShowWithOdbc_Click" Text="showWithOdbc" />
        </center>
    </div>
      <span style="color: red">* 首先:在d盘下创建testDB的VFP数据库.然后可使用vs2005测试.
      其中必须要先创建表，即：点击createTable按钮。</span>
    </form>
</body>
</html>

cs代码：

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page
{
    /// <summary>
    /// 数据文件
    /// </summary>
    private static string table = @"D:/User.dbf";

    /// <summary>
    /// Odbc链接字符串 ,其中SourceDB可以是dbf文件所在的目录也可以制定到文件,作用是一样的,如:SourceDB=d:/;或SourceDB==D:/Allart.dbf
    /// </summary>
    private static string strConn = @"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=d:/;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO";
   
    /// <summary>
    /// Oledb链接字符串
    /// 1,到微软网站下载<Microsoft OLE DB Provider for Visual FoxPro 9.0>并安装,这样oledb才能识别provider=VFPOLEDB.1,
    /// http://www.microsoft.com/downloads/thankyou.aspx?familyId=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displayLang=en
    /// 2,其中SourceDB可以是dbf文件所在的目录也可以制定到文件,作用是一样的,如:data source=d:/;或data source==D:/Allart.dbf
    /// </summary>
    private static string strOledbConn = @"provider=VFPOLEDB.1;data source=d:/;user id=admin;password=";

    protected void Page_Load(object sender, EventArgs e)
    {
       
    }

    /// <summary>
    /// 初始化vfp数据库，创建user表
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnInti_Click(object sender, EventArgs e)
    {
        string strOledbConn = @"provider=VFPOLEDB.1;data source=d:/testDB.dbc;user id=admin;password=";
        OleDbConnection conn = new OleDbConnection(strOledbConn);
        OleDbCommand cmd = new OleDbCommand();
        string sql = "CREATE TABLE User (ID c(6) ,Name C(20),BirthDay t,Salary n(6,2))";
        cmd.CommandText = sql;
        cmd.Connection = conn;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        showDataWithOdbc();
        Response.Write("表格生成成功！");
    }

    /// <summary>
    /// 插入数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        OdbcConnection conn = new OdbcConnection(strConn);
        OdbcCommand cmd = new OdbcCommand();
        //字符c,时间t,数字n
        string sql = "insert into " + table + "(id,name,birthday,salary) values('1','haha',{^1985-09-10},3300.1)";
        cmd.CommandText = sql;
        cmd.Connection = conn;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        showDataWithOdbc();
        Response.Write("表格插入成功！");
    }

    /// <summary>
    /// 更新数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnUpdate_Click(object sender, EventArgs e)
    {

        OdbcConnection conn = new OdbcConnection(strConn);
        OdbcCommand cmd = new OdbcCommand();
        string sql = "update " + table + " set ID ='2',name='haha2'";
        cmd.CommandText = sql;
        cmd.Connection = conn;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        showDataWithOdbc();
        Response.Write("表格更新成功！");
    }

    /// <summary>
    /// 删除数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnDelete_Click(object sender, EventArgs e)
    {

        OdbcConnection conn = new OdbcConnection(strConn);
        OdbcCommand cmd = new OdbcCommand();
        string sql = "delete from  " + table;
        cmd.CommandText = sql;
        cmd.Connection = conn;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        showDataWithOdbc();
        Response.Write("表格删除成功！");
    }

    /// <summary>
    /// 使用Oledb显示数据中数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnShowWithOledb_Click(object sender, EventArgs e)
    {
        showDataWithOledb();
        Response.Write("成功调用Oledb显示数据！");
    }
    /// <summary>
    /// 使用Odbc显示数据中数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnShowWithOdbc_Click(object sender, EventArgs e)
    {
        showDataWithOdbc();
        Response.Write("成功调用Odbc显示数据！");
    }

    /// <summary>
    /// 使用Odbc驱动链接数据库并显示数据
    /// </summary>
    private void showDataWithOdbc()
    {
        OdbcConnection conn = new OdbcConnection();
        conn.ConnectionString = strConn;
        conn.Open();
        string sql = @"select  * from " + table;
        OdbcDataAdapter da = new OdbcDataAdapter(sql, conn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        Gridview1.DataSource = dt.DefaultView;
        Gridview1.DataBind();
        conn.Close();
    }

    /// <summary>
    /// 使用OleDb驱动链接数据库并显示数据
    /// </summary>
    private void showDataWithOledb()
    {
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = strOledbConn;
        conn.Open();
        string sql = @"select  * from " + table ;
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        Gridview1.DataSource = dt.DefaultView;
        Gridview1.DataBind();
        conn.Close();
    }
 
}