释放双眼，带上耳机，听听看~！
首先把Access数据库文件放在项目的/bin/Debug的文件夹里面
下面是我Access文件中的两张测试表：Table1，Table2
DAL文件代码
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
2using System.Data;
3using System.Data.OleDb;
4
5namespace access
6{
7 /// <summary>
8 /// 操作Access数据库
9 /// </summary>
10 class AccessOperation
11 {
12 //连接字符
13 //Environment.CurrentDirectory：获取或设置当前工作目录的完全限定路径
14 static string connStr = @"Provider= Microsoft.Jet.OLEDB.4.0;Data Source = " + Environment.CurrentDirectory + @"\DataWarehouse.mdb";
15
16 #region 执行查询指令，获取返回DataTable
17 /// <summary>
18 /// 执行查询指令，获取返回DataTable
19 /// </summary>
20 /// <param name="sql">查询sql语句</param>
21 /// <param name="param">sql语句的参数</param>
22 /// <returns></returns>
23 public static DataTable ExecuteDataTable(string sql, params OleDbParameter[] param)
24 {
25 using (OleDbConnection conn = new OleDbConnection(connStr))
26 {
27 using (OleDbCommand cmd = new OleDbCommand(sql, conn))
28 {
29 if (param != null)
30 {
31 cmd.Parameters.AddRange(param);
32 }
33 DataTable dt = new DataTable();
34 OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
35 sda.Fill(dt);
36 return (dt);
37 }
38 }
39 }
40
41 #endregion
42
43 #region 执行增加、删除、修改指令
44 /// <summary>
45 /// 执行增加、删除、修改指令
46 /// </summary>
47 /// <param name="sql">增加、删除、修改的sql语句</param>
48 /// <param name="param">sql语句的参数</param>
49 /// <returns></returns>
50 public static int ExecuteNonQuery(string sql, params OleDbParameter[] param)
51 {
52 using (OleDbConnection conn = new OleDbConnection(connStr))
53 {
54 using (OleDbCommand cmd = new OleDbCommand(sql, conn))
55 {
56 if (param != null)
57 {
58 cmd.Parameters.AddRange(param);
59 }
60 conn.Open();
61 return (cmd.ExecuteNonQuery());
62 }
63 }
64 }
65
66 #endregion
67
68 #region 执行查询指令，获取返回的首行首列的值
69 /// <summary>
70 /// 执行查询指令，获取返回的首行首列的值
71 /// </summary>
72 /// <param name="sql">查询sql语句</param>
73 /// <param name="param">sql语句的参数</param>
74 /// <returns></returns>
75 public static object ExecuteScalar(string sql, params OleDbParameter[] param)
76 {
77 using (OleDbConnection conn = new OleDbConnection(connStr))
78 {
79 using (OleDbCommand cmd = new OleDbCommand(sql, conn))
80 {
81 if (param != null)
82 {
83 cmd.Parameters.AddRange(param);
84 }
85 conn.Open();
86 return (cmd.ExecuteScalar());
87 }
88 }
89 }
90 #endregion
91
92 #region 执行查询指令，获取返回的datareader
93 /// <summary>
94 /// 执行查询指令，获取返回的datareader
95 /// </summary>
96 /// <param name="sql">查询sql语句</param>
97 /// <param name="param">sql语句的参数</param>
98 /// <returns></returns>
99 public static OleDbDataReader ExecuteReader(string sql, params OleDbParameter[] param)
100 {
101 OleDbConnection conn = new OleDbConnection(connStr);
102 OleDbCommand cmd = conn.CreateCommand();
103 cmd.CommandText = sql;
104 cmd.CommandType = CommandType.Text;
105 if (param != null)
106 {
107 cmd.Parameters.AddRange(param);
108 }
109 conn.Open();
110 return (cmd.ExecuteReader(CommandBehavior.CloseConnection));
111 }
112 #endregion
113 }
114}
115
116
查询代码：
2
3
4
5
6
7
8
2 //语句与SQL相同，此处不直接使用字符串拼接是因为如果查询多个字段或有多个条件使用拼接难找到你想要找的字符
3 //若不直接用string 那么在此的每一个append中的值最后加上空格防止出错
4 sb.Append("SELECT Table1.UID,Table1.Name,Table1.Sex,Table2.SID,Table2.Laborage,Table2.Month ");
5 sb.Append("FROM Table1 INNER JOIN Table2 ON Table1.UID = Table2.UID");
6 dgvData.DataSource = AccessOperation.ExecuteDataTable(sb.ToString());
7
8
效果：
新增代码：
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2 //此处需注意，字段名需用中括号括起来，字符类型值需要用单引号括起来，时间类型值用前后加#
3 sb.Append("INSERT INTO Table2([Laborage], [Month], [UID]) ");
4 sb.Append("VALUES ('110+10',1,1)");
5 ///int dt = Convert.ToInt32(ExecuteScalar("select @@identity as id")); 不支持。。
6
7 int Insert = AccessOperation.ExecuteNonQuery(sb.ToString());
8 if (Insert > 0)
9 {
10 sb.Clear();
11 //存在并发问题。。
12 sb.Append("SELECT SID ");
13 sb.Append("FROM Table2 ");
14 sb.Append("ORDER BY SID desc");
15 //返回新增的ID
16 MessageBox.Show(AccessOperation.ExecuteScalar(sb.ToString()).ToString());
17 }
18
19
效果：
修改代码：
2
3
4
5
6
7
8
9
10
11
2 int SID = Convert.ToInt32(dgvData.SelectedRows[0].Cells["SID"].Value.ToString());
3 StringBuilder sb = new StringBuilder();
4 //注意与新增一样
5 sb.Append("UPDATE Table2 ");
6 sb.Append("SET [Laborage] = '100.11+0.01', [Month] = 2, [UID] = 2 ");
7 sb.Append("where SID = "+SID);
8 //弹出受影响行数
9 MessageBox.Show(AccessOperation.ExecuteNonQuery(sb.ToString()).ToString());
10
11
效果：
删除代码：
2
3
4
5
6
2 int SID = Convert.ToInt32(dgvData.SelectedRows[0].Cells["SID"].Value.ToString());
3
4 MessageBox.Show(AccessOperation.ExecuteNonQuery("DELETE FROM Table2 WHERE SID=" + SID).ToString());
5
6
Access数据库虽然说已经落伍了，但是技不压身，学一学，用来做一些单机的小程序还是不错的
