C#對(duì)Access進(jìn)行增刪改查的完整示例

更新時(shí)間：2016年08月18日 15:22:19 投稿：daisy

本文主要是講C#對(duì)Access數(shù)據(jù)庫(kù)的增刪改查操作，想學(xué)習(xí)C#和Access數(shù)據(jù)庫(kù)操作基礎(chǔ)的可以參考借鑒，以下代碼都經(jīng)過(guò)實(shí)踐測(cè)試可用，下面跟著小編一起來(lái)看看。

這篇文章整理了C#對(duì)Access數(shù)據(jù)庫(kù)的查詢、添加記錄、刪除記錄和更新數(shù)據(jù)等一系列的操作示例，有需要的可以參考學(xué)習(xí)。

首先是AccessHelper.cs，網(wǎng)上有下載，下面附送一份；

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>
    /// 構(gòu)造函數(shù)
    /// </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);//創(chuàng)建實(shí)例
      ole_command =new OleDbCommand();
    }
 
    /// <summary>
    /// 構(gòu)造函數(shù)
    /// </summary>
    ///<param name="db_path">數(shù)據(jù)庫(kù)路徑
    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>
    /// 轉(zhuǎn)換數(shù)據(jù)格式
    /// </summary>
    ///<param name="reader">數(shù)據(jù)源
    /// <returns>數(shù)據(jù)列表</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>
    /// 創(chuàng)建并初始化數(shù)據(jù)列表
    /// </summary>
    ///<param name="Field_Count">列的個(gè)數(shù)
    /// <returns>數(shù)據(jù)列表</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>
    /// 從數(shù)據(jù)庫(kù)里面獲取數(shù)據(jù)
    /// </summary>
    ///<param name="strSql">查詢語(yǔ)句
    /// <returns>數(shù)據(jù)列表</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>
    /// 執(zhí)行sql語(yǔ)句
    /// </summary>
    ///<param name="strSql">sql語(yǔ)句
    /// <returns>返回結(jié)果</returns>
    public int ExcuteSql(string strSql)
    {
      int nResult = 0;
 
      try
      {
        ole_connection.Open();//打開數(shù)據(jù)庫(kù)連接
        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;
    }
  }
}

定義變量，設(shè)置列標(biāo)題；

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 = "服務(wù)號(hào)碼";
    dataGridView1.Columns[2].HeaderCell.Value = "客戶名稱";
    dataGridView1.Columns[3].HeaderCell.Value = "歸屬地區(qū)";
    dataGridView1.Columns[4].HeaderCell.Value = "當(dāng)前品牌";
    dataGridView1.Columns[5].HeaderCell.Value = "當(dāng)前套餐";
    dataGridView1.Columns[6].HeaderCell.Value = "當(dāng)前狀態(tài)";
  }

顯示數(shù)據(jù)表全部?jī)?nèi)容；

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營(yíng)銷");
    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("沒有要添加的內(nèi)容", "M營(yíng)銷添加");
    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營(yíng)銷");
  }
  else
  {
    object oid = dataGridView1.SelectedRows[0].Cells[0].Value;
    if (DialogResult.No == MessageBox.Show("將刪除第 " + (dataGridView1.CurrentCell.RowIndex + 1).ToString() + " 行，確定？", "M營(yíng)銷", 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("請(qǐng)輸入要查詢的當(dāng)前品牌", "M營(yíng)銷");
    return;
  }
  else
  {
    string sql = "select * from ycyx where dqpp='" + textBox23.Text + "'";
    DataTable dt = new System.Data.DataTable();
    dt = achelp.GetDataTableFromDB(sql);
    dataGridView1.DataSource = dt;
  }
}

用戶確定顯示或不顯示哪些數(shù)據(jù)列；

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;
  }
}

更新數(shù)據(jù)；

  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營(yíng)銷");
        }
      }
      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();
    }
  }
}

注意此處有一個(gè)技巧；C# Winform，在窗體之間傳值，或在一個(gè)窗體中設(shè)置另一個(gè)窗體的控件的值時(shí)，有多種方式；最好方式是如上代碼所示；使用.net的get、set屬性；

控件是一個(gè)窗體的私有變量，不能在另一個(gè)窗體中直接訪問；為了在a窗體中設(shè)置b窗體的控件的值，對(duì)b窗體的控件都添加一個(gè)帶get、set的公共屬性，就可在a中設(shè)置b中控件的值，具體看代碼；

以上就是C#對(duì)Access進(jìn)行增刪改查的完整示例代碼，希望對(duì)大家學(xué)習(xí)C#能有所幫助。

您可能感興趣的文章: