正文

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

︶ㄣ米米素材网 メ
米米素材网V管理员 / 阅读
0708

本文主要是讲C#对Access数据库的增删改查操作，想学习C#和Access数据库操作基础的可以参考借鉴，以下代码都经过实践测试可用，下面跟着小编一起来看看。

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
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;
    }
  }
}

定义变量，设置列标题；

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 = "当前状态";
  }

显示数据表全部内容；

?
1
2
3
4
5
6
private void databind1(string sqlstr)
{
  DataTable dt = new DataTable();
  dt = achelp.GetDataTableFromDB(sqlstr);
  dataGridView1.DataSource = dt;
}

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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();
   
}

添加记录；

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 = "";
  }
}

删除记录；

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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);
  }
}

查询；

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;
  }
}

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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;
  }
}

更新数据；

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
  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#能有所帮助。