C#对Access进行增删改查的完整示例_c#

这篇文章整理了c#对access数据库的查询、添加记录、删除记录和更新数据等一系列的操作示例，有需要的可以参考学习。

首先是accesshelper.cs，网上有下载，下面附送一份；

using system;
using system.collections.generic;
using system.linq;
using system.text;
using system.threading.tasks;
using system.data.oledb;
using system.data;
using system.windows.forms;
 
namespace yxdain
{
  public class accesshelper
  {
    private string conn_str = null;
    private oledbconnection ole_connection = null;
    private oledbcommand ole_command = null;
    private oledbdatareader ole_reader = null;
    private datatable dt = null;
 
    /// <summary>
    /// 构造函数
    /// </summary>
    public accesshelper()
    {
      //conn_str = @"provider=microsoft.jet.oledb.4.0;data source='" + environment.currentdirectory + "\\yxdain.accdb'";
      conn_str = @"provider=microsoft.ace.oledb.12.0;data source='" + environment.currentdirectory + "\\yxdain.accdb'";
       
      initdb();
    }
 
    private void initdb()
    {
      ole_connection =new oledbconnection(conn_str);//创建实例
      ole_command =new oledbcommand();
    }
 
    /// <summary>
    /// 构造函数
    /// </summary>
    ///<param name="db_path">数据库路径
    public accesshelper(string db_path)
    {
      //conn_str ="provider=microsoft.jet.oledb.4.0;data source='"+ db_path + "'";
      conn_str = "provider=microsoft.ace.oledb.12.0;data source='" + db_path + "'";
       
      initdb();
    }
 
    /// <summary>
    /// 转换数据格式
    /// </summary>
    ///<param name="reader">数据源
    /// <returns>数据列表</returns>
    private datatable convertoledbreadertodatatable(ref oledbdatareader reader)
    {
      datatable dt_tmp =null;
      datarow dr =null;
      int data_column_count = 0;
      int i = 0;
 
      data_column_count = reader.fieldcount;
      dt_tmp = buildandinitdatatable(data_column_count);
 
      if(dt_tmp == null)
      {
        return null;
      }
 
      while(reader.read())
      {
        dr = dt_tmp.newrow();
 
        for(i = 0; i < data_column_count; ++i)
        {
          dr[i] = reader[i];
        }
 
        dt_tmp.rows.add(dr);
      }
 
      return dt_tmp;
    }
 
    /// <summary>
    /// 创建并初始化数据列表
    /// </summary>
    ///<param name="field_count">列的个数
    /// <returns>数据列表</returns>
    private datatable buildandinitdatatable(int field_count)
    {
      datatable dt_tmp =null;
      datacolumn dc =null;
      int i = 0;
 
      if(field_count <= 0)
      {
        return null;
      }
 
      dt_tmp =new datatable();
 
      for(i = 0; i < field_count; ++i)
      {
        dc =new datacolumn(i.tostring());
        dt_tmp.columns.add(dc);
      }
 
      return dt_tmp;
    }
 
    /// <summary>
    /// 从数据库里面获取数据
    /// </summary>
    ///<param name="strsql">查询语句
    /// <returns>数据列表</returns>
    public datatable getdatatablefromdb(string strsql)
    {
      if(conn_str == null)
      {
        return null;
      }
       
      try
      {
        ole_connection.open();//打开连接
 
        if(ole_connection.state == connectionstate.closed)
        {
          return null;
        }
 
        ole_command.commandtext = strsql;
        ole_command.connection = ole_connection;
 
        ole_reader = ole_command.executereader(commandbehavior.default);
 
        dt = convertoledbreadertodatatable(ref ole_reader);
 
        ole_reader.close();
        ole_reader.dispose();
      }
      catch(system.exception e)
      {
        //console.writeline(e.tostring());
        messagebox.show(e.message);
      }
      finally
      {
        if(ole_connection.state != connectionstate.closed)
        {
          ole_connection.close();
        }
      }
       
      return dt;
    }
 
    /// <summary>
    /// 执行sql语句
    /// </summary>
    ///<param name="strsql">sql语句
    /// <returns>返回结果</returns>
    public int excutesql(string strsql)
    {
      int nresult = 0;
 
      try
      {
        ole_connection.open();//打开数据库连接
        if(ole_connection.state == connectionstate.closed)
        {
          return nresult;
        }
 
        ole_command.connection = ole_connection;
        ole_command.commandtext = strsql;
 
        nresult = ole_command.executenonquery();
      }
      catch(system.exception e)
      {
        //console.writeline(e.tostring());
        messagebox.show(e.message);
        return nresult;
      }
      finally
      {
        if(ole_connection.state != connectionstate.closed)
        {
          ole_connection.close();
        }
      }
 
      return nresult;
    }
  }
}

定义变量，设置列标题；

private accesshelper achelp;
......
  private void form1_load(object sender, eventargs e)
  {
 
    achelp = new accesshelper();
    string sql1 = "select * from ycyx";
    databind1(sql1);
    
    datagridview1.columns[0].visible = false;
    datagridview1.columns[1].headercell.value = "服务号码";
    datagridview1.columns[2].headercell.value = "客户名称";
    datagridview1.columns[3].headercell.value = "归属地区";
    datagridview1.columns[4].headercell.value = "当前品牌";
    datagridview1.columns[5].headercell.value = "当前套餐";
    datagridview1.columns[6].headercell.value = "当前状态";
  }

显示数据表全部内容；

private void databind1(string sqlstr)
{
  datatable dt = new datatable();
  dt = achelp.getdatatablefromdb(sqlstr);
  datagridview1.datasource = dt;
}

读取要更新记录到更新窗体控件；

private void button3_click(object sender, eventargs e)
{
  if (datagridview1.selectedrows.count < 1 || datagridview1.selectedrows[0].cells[1].value == null)
  {
    messagebox.show("没有选中行。", "m营销");
    return;
  }
  //f3.owner = this;
  datatable dt = new datatable();
  object oid = datagridview1.selectedrows[0].cells[0].value;
  string sql = "select * from ycyx where id=" + oid;
  dt = achelp.getdatatablefromdb(sql);
  f3 = new form3();
  f3.id = int.parse(oid.tostring());
  //f3.id = 2;
  f3.text1 = dt.rows[0][1].tostring();
  f3.text2 = dt.rows[0][2].tostring();
  f3.text3 = dt.rows[0][3].tostring();
  f3.text4 = dt.rows[0][4].tostring();
  f3.text5 = dt.rows[0][5].tostring();
  f3.text6 = dt.rows[0][6].tostring();
 
  f3.showdialog();
   
}

添加记录；

private void button4_click(object sender, eventargs e)
{
  if (textbox1.text == "" && textbox2.text == "" && textbox3.text == "" && textbox4.text == "" && textbox5.text == "" && textbox6.text == "")
  {
    messagebox.show("没有要添加的内容", "m营销添加");
    return;
  }
  else
  {
    string sql = "insert into ycyx (fwhm,khmc,gsdq,dqpp,dqtc,dqzt) values ('" + textbox1.text + "','" + textbox2.text + "','"+
      textbox3.text + "','"+ textbox4.text + "','"+ textbox5.text + "','"+ textbox6.text + "')";
    int ret = achelp.excutesql(sql);
    string sql1 = "select * from ycyx";
    databind1(sql1);
    textbox1.text = "";
    textbox2.text = "";
    textbox3.text = "";
    textbox4.text = "";
    textbox5.text = "";
    textbox6.text = "";
  }
}

删除记录；

private void button2_click(object sender, eventargs e)
{
  if (datagridview1.selectedrows.count < 1 || datagridview1.selectedrows[0].cells[1].value == null)
  {
    messagebox.show("没有选中行。", "m营销");
  }
  else
  {
    object oid = datagridview1.selectedrows[0].cells[0].value;
    if (dialogresult.no == messagebox.show("将删除第 " + (datagridview1.currentcell.rowindex + 1).tostring() + " 行，确定？", "m营销", messageboxbuttons.yesno))
    {
      return;
    }
    else
    {
      string sql = "delete from ycyx where id=" + oid;
      int ret = achelp.excutesql(sql);
    }
    string sql1 = "select * from ycyx";
    databind1(sql1);
  }
}

查询；

private void button13_click(object sender, eventargs e)
{
  if (textbox23.text == "")
  {
    messagebox.show("请输入要查询的当前品牌", "m营销");
    return;
  }
  else
  {
    string sql = "select * from ycyx where dqpp='" + textbox23.text + "'";
    datatable dt = new system.data.datatable();
    dt = achelp.getdatatablefromdb(sql);
    datagridview1.datasource = dt;
  }
}

用户确定显示或不显示哪些数据列；

private void button15_click(object sender, eventargs e)
{
  if (checkbox1.checked == true)
  {
    datagridview1.columns[1].visible = true;
  }
  else
  {
    datagridview1.columns[1].visible = false;
  }
 
  if (checkbox2.checked == true)
  {
    datagridview1.columns[2].visible = true;
  }
  else
  {
    datagridview1.columns[2].visible = false;
  }
 
  if (checkbox3.checked == true)
  {
    datagridview1.columns[3].visible = true;
  }
  else
  {
    datagridview1.columns[3].visible = false;
  }
 
  if (checkbox4.checked == true)
  {
    datagridview1.columns[4].visible = true;
  }
  else
  {
    datagridview1.columns[4].visible = false;
  }
 
  if (checkbox5.checked == true)
  {
    datagridview1.columns[5].visible = true;
  }
  else
  {
    datagridview1.columns[5].visible = false;
  }
 
  if (checkbox6.checked == true)
  {
    datagridview1.columns[6].visible = true;
  }
  else
  {
    datagridview1.columns[6].visible = false;
  }
}

更新数据；

  public partial class form3 : form
  {
    private accesshelper achelp;
    private int iid;
 
    public form3()
    {
      initializecomponent();
      achelp = new accesshelper();
      iid = 0;
    }
 
    // 更新
    private void button1_click(object sender, eventargs e)
    {
      try
      {
        //update person set address = 'zhongshan 23', city = 'nanjing'where lastname = 'wilson'
        string sql = "update ycyx set fwhm='"+textbox1.text+"',khmc='"+textbox2.text+"',gsdq='"+textbox3.text+"',dqpp='"+textbox4.text+
          "',dqtc='"+textbox5.text+"',dqzt='"+textbox6.text+"' where id="+iid;
           
 
        int ret = achelp.excutesql(sql);
        if (ret > -1)
        {
          this.hide();
          messagebox.show("更新成功", "m营销");
        }
      }
      catch (exception ex)
      {
        messagebox.show(ex.message);
      }
 
       
 
    }
 
    private void form3_load(object sender, eventargs e)
    {
 
    }
 
    public int id
    {
      get { return this.iid; }
      set { this.iid = value; }
    }
 
 
    public string text1
    {
      get { return this.textbox1.text; }
      set { this.textbox1.text = value; }
    }
 
    public string text2
    {
      get { return this.textbox2.text; }
      set { this.textbox2.text = value; }
    }
 
    public string text3
    {
      get { return this.textbox3.text; }
      set { this.textbox3.text = value; }
    }
 
    public string text4
    {
      get { return this.textbox4.text; }
      set { this.textbox4.text = value; }
    }
 
    public string text5
    {
      get { return this.textbox5.text; }
      set { this.textbox5.text = value; }
    }
 
    public string text6
    {
      get { return this.textbox6.text; }
      set { this.textbox6.text = value; }
    }
 
    //取消
    private void button2_click(object sender, eventargs e)
    {
      this.hide();
    }
  }
}

注意此处有一个技巧；c# winform，在窗体之间传值，或在一个窗体中设置另一个窗体的控件的值时，有多种方式；最好方式是如上代码所示；使用.net的get、set属性；

控件是一个窗体的私有变量，不能在另一个窗体中直接访问；为了在a窗体中设置b窗体的控件的值，对b窗体的控件都添加一个带get、set的公共属性，就可在a中设置b中控件的值，具体看代码；

以上就是c#对access进行增删改查的完整示例代码，希望对大家学习c#能有所帮助。