本文實例講述了C#實現Access通用訪問類OleDbHelper。分享給大家供大家參考，具體如下：
最近在做一個項目數據庫用的是Access,第一次使用Access數據庫，剛開始做有些不順，數據庫的操作和SqlServer稍有些不同，而異常跟蹤得到的信息也沒有什么意義，經過幾天的反復尋找問題，總算解決了一些問題，為了訪問Access 數據庫，我寫了一個用于專門訪問的類來操作數據庫，其中包括，執行數據庫命令，返回 DataSet，返回單條記錄，返回DataReader，通用分頁方法等幾個常用的的操作方法。請各位提出意見，以便我完善這個類。雖是參考SqlHelper 但是比其簡單的多，所有的代碼如下：
|
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
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
|
using System;using System.Collections;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.Common;using System.Data.OleDb;namespace Common{ /// <summary> /// OleDb 書庫訪問類 /// </summary> public static class OleDbHelper { /// <summary> /// Access 的數據庫連接字符串格式. /// </summary> public const string ACCESS_CONNECTIONSTRING_TEMPLATE = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};"; // Hashtable to store cached parameters private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// <summary> /// 針對 System.Data.OleDb.OleDbCommand.Connection 執行 SQL 語句并返回受影響的行數. /// </summary> /// <param name="connString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 針對 System.Data.OleDb.OleDbCommand.Connection 執行 SQL 語句并返回受影響的行數. /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 針對 System.Data.OleDb.OleDbCommand.Connection 執行 SQL 語句并返回受影響的行數. /// </summary> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms, ConnectionActionType.None); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 將 System.Data.OleDb.OleDbCommand.CommandText 發送到 System.Data.OleDb.OleDbCommand.Connection 并生成一個 System.Data.OleDb.OleDbDataReader. /// </summary> /// <param name="connString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); OleDbConnection conn = new OleDbConnection(connString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); OleDbDataReader rdr = cmd.ExecuteReader(); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// 將 System.Data.OleDb.OleDbCommand.CommandText 發送到 System.Data.OleDb.OleDbCommand.Connection 并生成一個 System.Data.OleDb.OleDbDataReader. /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static OleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); OleDbDataReader rdr = cmd.ExecuteReader(); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// 執行查詢，并返回查詢所返回的結果集中第一行的第一列。忽略其他列或行. /// </summary> /// <param name="connString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 執行查詢，并返回查詢所返回的結果集中第一行的第一列。忽略其他列或行. /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// <summary> /// 執行查詢，并返回查詢所返回的結果數據集. /// </summary> /// <param name="connString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cmd.Parameters.Clear(); return ds; } } /// <summary> /// 執行查詢，并返回查詢所返回的結果數據集. /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cmd.Parameters.Clear(); return ds; } /// <summary> /// 緩存查詢的 OleDb 參數對象. /// </summary> /// <param name="cacheKey"></param> /// <param name="cmdParms"></param> public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms) { parmCache[cacheKey] = cmdParms; } /// <summary> /// 從緩存獲取指定的參數對象數組. /// </summary> /// <param name="cacheKey"></param> /// <returns></returns> public static OleDbParameter[] GetCachedParameters(string cacheKey) { OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey]; if (cachedParms == null) return null; OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } /// <summary> /// 準備命令對象. /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <param name="connActionType"></param> private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms, ConnectionActionType connActionType) { if (connActionType == ConnectionActionType.Open) { conn.Open(); } else { if (conn.State != ConnectionState.Open) conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (OleDbParameter parm in cmdParms) cmd.Parameters.Add(parm); } } /// <summary> /// 統一分頁顯示數據記錄 /// </summary> /// <param name="connString">數據庫連接字符串</param> /// <param name="pageIndex">當前頁碼</param> /// <param name="pageSize">每頁顯示的條數</param> /// <param name="fileds">顯示的字段</param> /// <param name="table">查詢的表格</param> /// <param name="where">查詢的條件</param> /// <param name="order">排序的規則</param> /// <param name="pageCount">out：總頁數</param> /// <param name="recordCount">out：總條數</param> /// <param name="id">表的主鍵</param> /// <returns>返回DataTable集合</returns> public static DataTable ExecutePager(string connString, int pageIndex, int pageSize, string fileds, string table, string where, string order, out int pageCount, out int recordCount, string id) { if (pageIndex < 1) pageIndex = 1; if (pageSize < 1) pageSize = 10; if (string.IsNullOrEmpty(fileds)) fileds = "*"; if (string.IsNullOrEmpty(order)) order = "ID desc"; using (OleDbConnection conn = new OleDbConnection(connString)) { string myVw = string.Format(" {0} ", table); string sqlText = string.Format(" select count(0) as recordCount from {0} {1}", myVw, where); OleDbCommand cmdCount = new OleDbCommand(sqlText, conn); if (conn.State == ConnectionState.Closed) conn.Open(); recordCount = Convert.ToInt32(cmdCount.ExecuteScalar()); if ((recordCount % pageSize) > 0) pageCount = recordCount / pageSize + 1; else pageCount = recordCount / pageSize; OleDbCommand cmdRecord; if (pageIndex == 1)//第一頁 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, where, order), conn); } else if (pageIndex > pageCount)//超出總頁數 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, "where 1=2", order), conn); } else { int pageLowerBound = pageSize * pageIndex; int pageUpperBound = pageLowerBound - pageSize; string recordIDs = RecordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, id, myVw, where, order), pageUpperBound, conn); cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn); } OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord); DataTable dt = new DataTable(); dataAdapter.Fill(dt); return dt; } } private static string RecordID(string query, int passCount, OleDbConnection conn) { OleDbCommand cmd = new OleDbCommand(query, conn); string result = string.Empty; using (IDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (passCount < 1) { result += "," + dr.GetInt32(0); } passCount--; } } return result.Substring(1); } /// <summary> /// 連接操作類型枚舉. /// </summary> enum ConnectionActionType { None = 0, AutoDetection = 1, Open = 2 } }} |
希望本文所述對大家C#程序設計有所幫助。
