本文實例講述了C#實現(xiàn)的ACCESS數(shù)據(jù)庫操作類。分享給大家供大家參考，具體如下：
這個是針對ACCESS數(shù)據(jù)庫操作的類，同樣也是從SQLHELPER提取而來，分頁程序的調(diào)用可以參考MSSQL那個類的調(diào)用，差不多的，只是提取所有記錄的數(shù)量的時候有多一個參數(shù)，這個需要注意一下！
|
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
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
|
using System;using System.Text;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.OleDb;using System.Configuration;namespace HoverTree.Web.DBUtility{ /// <summary> /// 數(shù)據(jù)訪問抽象基礎(chǔ)類(ACCESS) /// Copyright (C) 2006-2007 hovertree.net /// All rights reserved /// </summary> public abstract class DbHelperACE { //數(shù)據(jù)庫連接字符串(web.config來配置) //public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; // public static string connectionString = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["AccessConnectionString"]); public static string connectionString = ConfigurationManager.AppSettings["AccessConnectionString"]; public DbHelperACE() { } #region 公用方法 public static int GetMaxID(string FieldName, string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = DbHelperACE.GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } public static bool Exists(string strSql) { object obj = DbHelperACE.GetSingle(strSql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } public static bool Exists(string strSql, params OleDbParameter[] cmdParms) { object obj = DbHelperACE.GetSingle(strSql, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 執(zhí)行簡單SQL語句 /// <summary> /// 執(zhí)行SQL語句，返回影響的記錄數(shù) /// </summary> /// <param name="SQLString">SQL語句</param> /// <returns>影響的記錄數(shù)</returns> public static int ExecuteSql(string SQLString) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { using (OleDbCommand cmd = new OleDbCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.OleDb.OleDbException E) { connection.Close(); throw new Exception(E.Message); } } } } /// <summary> /// 執(zhí)行SQL語句，設(shè)置命令的執(zhí)行等待時間 /// </summary> /// <param name="SQLString"></param> /// <param name="Times"></param> /// <returns></returns> public static int ExecuteSqlByTime(string SQLString, int Times) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { using (OleDbCommand cmd = new OleDbCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.OleDb.OleDbException E) { connection.Close(); throw new Exception(E.Message); } } } } /// <summary> /// 執(zhí)行多條SQL語句，實現(xiàn)數(shù)據(jù)庫事務(wù)。 /// </summary> /// <param name="SQLStringList">多條SQL語句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; OleDbTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.OleDb.OleDbException E) { tx.Rollback(); throw new Exception(E.Message); } } } /// <summary> /// 向數(shù)據(jù)庫里插入圖像格式的字段(和上面情況類似的另一種實例) /// </summary> /// <param name="strSQL">SQL語句</param> /// <param name="fs">圖像字節(jié),數(shù)據(jù)庫的字段類型為image的情況</param> /// <returns>影響的記錄數(shù)</returns> public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand cmd = new OleDbCommand(strSQL, connection); System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.OleDb.OleDbException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 執(zhí)行一條計算查詢結(jié)果語句，返回查詢結(jié)果（object）。 /// </summary> /// <param name="SQLString">計算查詢結(jié)果語句</param> /// <returns>查詢結(jié)果（object）</returns> public static object GetSingle(string SQLString) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { using (OleDbCommand cmd = new OleDbCommand(SQLString, connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.OleDb.OleDbException e) { connection.Close(); throw new Exception(e.Message); } } } } /// <summary> /// 執(zhí)行查詢語句，返回SqlDataReader(使用該方法切記要手工關(guān)閉SqlDataReader和連接) /// </summary> /// <param name="strSQL">查詢語句</param> /// <returns>SqlDataReader</returns> public static OleDbDataReader ExecuteReader(string strSQL) { OleDbConnection connection = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(strSQL, connection); try { connection.Open(); OleDbDataReader myReader = cmd.ExecuteReader(); return myReader; } catch (System.Data.OleDb.OleDbException e) { throw new Exception(e.Message); } //finally //不能在此關(guān)閉，否則，返回的對象將無法使用 //{ // cmd.Dispose(); // connection.Close(); //} } /// <summary> /// 執(zhí)行查詢語句，返回DataSet /// </summary> /// <param name="SQLString">查詢語句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } return ds; } } /// <summary> /// 執(zhí)行查詢語句，返回DataSet,設(shè)置命令的執(zhí)行等待時間 /// </summary> /// <param name="SQLString"></param> /// <param name="Times"></param> /// <returns></returns> public static DataSet Query(string SQLString, int Times) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection); command.SelectCommand.CommandTimeout = Times; command.Fill(ds, "ds"); } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } return ds; } } #endregion #region 執(zhí)行帶參數(shù)的SQL語句 /// <summary> /// 執(zhí)行SQL語句，返回影響的記錄數(shù) /// </summary> /// <param name="SQLString">SQL語句</param> /// <returns>影響的記錄數(shù)</returns> public static int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { using (OleDbCommand cmd = new OleDbCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (System.Data.OleDb.OleDbException E) { throw new Exception(E.Message); } } } } /// <summary> /// 執(zhí)行多條SQL語句，實現(xiàn)數(shù)據(jù)庫事務(wù)。 /// </summary> /// <param name="SQLStringList">SQL語句的哈希表（key為sql語句，value是該語句的OleDbParameter[]）</param> public static void ExecuteSqlTran(Hashtable SQLStringList) { using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { OleDbCommand cmd = new OleDbCommand(); try { //循環(huán) foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); trans.Commit(); } } catch { trans.Rollback(); throw; } } } } /// <summary> /// 執(zhí)行一條計算查詢結(jié)果語句，返回查詢結(jié)果（object）。 /// </summary> /// <param name="SQLString">計算查詢結(jié)果語句</param> /// <returns>查詢結(jié)果（object）</returns> public static object GetSingle(string SQLString, params OleDbParameter[] cmdParms) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { using (OleDbCommand cmd = new OleDbCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.OleDb.OleDbException e) { throw new Exception(e.Message); } } } } /// <summary> /// 執(zhí)行查詢語句，返回SqlDataReader (使用該方法切記要手工關(guān)閉SqlDataReader和連接) /// </summary> /// <param name="strSQL">查詢語句</param> /// <returns>SqlDataReader</returns> public static OleDbDataReader ExecuteReader(string SQLString, params OleDbParameter[] cmdParms) { OleDbConnection connection = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(); try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); OleDbDataReader myReader = cmd.ExecuteReader(); cmd.Parameters.Clear(); return myReader; } catch (System.Data.OleDb.OleDbException e) { throw new Exception(e.Message); } //finally //不能在此關(guān)閉，否則，返回的對象將無法使用 //{ // cmd.Dispose(); // connection.Close(); //} } /// <summary> /// 執(zhí)行查詢語句，返回DataSet /// </summary> /// <param name="SQLString">查詢語句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString, params OleDbParameter[] cmdParms) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (OleDbDataAdapter da = new OleDbDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } return ds; } } } private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (OleDbParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } #endregion #region 獲取根據(jù)指定字段排序并分頁查詢。 /// <summary> /// 分頁查詢數(shù)據(jù)記錄總數(shù)獲取 /// </summary> /// <param name="_tbName">----要顯示的表或多個表的連接</param> /// <param name="_ID">----主表的主鍵</param> /// <param name="_strCondition">----查詢條件,不需where</param> /// <param name="_Dist">----是否添加查詢字段的 DISTINCT 默認(rèn)0不添加/1添加</param> /// <returns></returns> public static string getPageListCounts(string _ID, string _tbName, string _strCondition, int _Dist) { //---存放取得查詢結(jié)果總數(shù)的查詢語句 //---對含有DISTINCT的查詢進行SQL構(gòu)造 //---對含有DISTINCT的總數(shù)查詢進行SQL構(gòu)造 string strTmp = "", SqlSelect = "", SqlCounts = ""; if (_Dist == 0) { SqlSelect = "Select "; SqlCounts = "COUNT(*)"; } else { SqlSelect = "Select DISTINCT "; SqlCounts = "COUNT(DISTINCT " + _ID + ")"; } if (_strCondition == string.Empty) { strTmp = SqlSelect + " "+ SqlCounts + " FROM " + _tbName; } else { strTmp = SqlSelect + " "+ SqlCounts + " FROM " + " Where (1=1) " + _strCondition; } return strTmp; } /// <summary> /// 智能返回SQL語句 /// </summary> /// <param name="primaryKey">主鍵（不能為空）</param> /// <param name="queryFields">提取字段（不能為空）</param> /// <param name="tableName">表（理論上允許多表）</param> /// <param name="condition">條件（可以空）</param> /// <param name="OrderBy">排序，格式：字段名+""+ASC（可以空）</param> /// <param name="pageSize">分頁數(shù)（不能為空）</param> /// <param name="pageIndex">當(dāng)前頁，起始為：1（不能為空）</param> /// <returns></returns> public static string getPageListSql(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex) { string strTmp = ""; //---strTmp用于返回的SQL語句 string SqlSelect = "", SqlPrimaryKeySelect = "", strOrderBy = "", strWhere = " where 1=1 ", strTop = ""; //0：分頁數(shù)量 //1:提取字段 //2:表 //3:條件 //4:主鍵不存在的記錄 //5:排序 SqlSelect = " select top {0} {1} from {2} {3} {4} {5}"; //0:主鍵 //1:TOP數(shù)量,為分頁數(shù)*(排序號-1) //2:表 //3:條件 //4:排序 SqlPrimaryKeySelect = " and {0} not in (select {1} {0} from {2} {3} {4}) "; if (orderBy != "") strOrderBy = " order by " + orderBy; if (condition != "") strWhere += " and " + condition; int pageindexsize = (pageIndex - 1) * pageSize; if (pageindexsize > 0) { strTop = " top " + pageindexsize.ToString(); SqlPrimaryKeySelect = String.Format(SqlPrimaryKeySelect, primaryKey, strTop, tableName, strWhere, strOrderBy); strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, SqlPrimaryKeySelect, strOrderBy); } else { strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, "", strOrderBy); } return strTmp; } /// <summary> /// 獲取根據(jù)指定字段排序并分頁查詢。DataSet /// </summary> /// <param name="pageSize">每頁要顯示的記錄的數(shù)目</param> /// <param name="pageIndex">要顯示的頁的索引</param> /// <param name="tableName">要查詢的數(shù)據(jù)表</param> /// <param name="queryFields">要查詢的字段,如果是全部字段請?zhí)顚懀?</param> /// <param name="primaryKey">主鍵字段，類似排序用到</param> /// <param name="orderBy">是否為升序排列：0為升序，1為降序</param> /// <param name="condition">查詢的篩選條件</param> /// <returns>返回排序并分頁查詢的DataSet</returns> public static DataSet GetPagingList(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex) { string sql = getPageListSql(primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex); return Query(sql); } public static string GetPagingListSQL(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex) { string sql = getPageListSql(primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex); return sql; } public static int GetRecordCount(string _ID, string _tbName, string _strCondition, int _Dist) { string sql = getPageListCounts( _ID, _tbName, _strCondition, _Dist); object obj = DbHelperACE.GetSingle(sql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } #endregion }} |
希望本文所述對大家C#程序設(shè)計有所幫助。
