ACCESS数据库C#操作类，SQLHELPER修改版——转自网上

ACCESS数据库C#操作类（SQLHELPER修改版）

这个是针对ACCESS数据库操作的类，同样也是从SQLHELPER提取而来，分页程序的调用可以参考MSSQL那个类的调用，差不多的，只是提取所有记录的数量的时候有多一个参数，这个需要注意一下！

c# 代码

1. using System;
2. using System.Text;
3. using System.Collections;
4. using System.Collections.Specialized;
5. using System.Data;
6. using System.Data.OleDb;
7. using System.Configuration;
8. namespace NMJU.Web.DBUtility
9. { /// <summary>
10. /// 数据访问抽象基础类(ACCESS)
11. /// Copyright (C) 2006-2007 NMJU.NET
12. /// All rights reserved
13. /// </summary>
14. public abstract class DbHelperACE
15. {
16. //数据库连接字符串(web.config来配置)
17. //public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
18. // public static string connectionString = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["AccessConnectionString"]);
19. public static string connectionString = ConfigurationManager.AppSettings["AccessConnectionString"];
20. public DbHelperACE()
21. {
22. }
23. #region 公用方法
24. public static int GetMaxID(string FieldName, string TableName)
25. {
26. string strsql = "select max(" + FieldName + ")+1 from " + TableName;
27. object obj = DbHelperACE.GetSingle(strsql);
28. if (obj == null)
29. {
30. return 1;
31. }
32. else
33. {
34. return int.Parse(obj.ToString());
35. }
36. }
37. public static bool Exists(string strSql)
38. {
39. object obj = DbHelperACE.GetSingle(strSql);
40. int cmdresult;
41. if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
42. {
43. cmdresult = 0;
44. }
45. else
46. {
47. cmdresult = int.Parse(obj.ToString());
48. }
49. if (cmdresult == 0)
50. {
51. return false;
52. }
53. else
54. {
55. return true;
56. }
57. }
58. public static bool Exists(string strSql, params OleDbParameter[] cmdParms)
59. {
60. object obj = DbHelperACE.GetSingle(strSql, cmdParms);
61. int cmdresult;
62. if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
63. {
64. cmdresult = 0;
65. }
66. else
67. {
68. cmdresult = int.Parse(obj.ToString());
69. }
70. if (cmdresult == 0)
71. {
72. return false;
73. }
74. else
75. {
76. return true;
77. }
78. }
79. #endregion
80. #region 执行简单SQL语句
81. /// <summary>
82. /// 执行SQL语句，返回影响的记录数
83. /// </summary>
84. /// <param name="SQLString">SQL语句</param>
85. /// <returns>影响的记录数</returns>
86. public static int ExecuteSql(string SQLString)
87. {
88. using (OleDbConnection connection = new OleDbConnection(connectionString))
89. {
90. using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
91. {
92. try
93. {
94. connection.Open();
95. int rows = cmd.ExecuteNonQuery();
96. return rows;
97. }
98. catch (System.Data.OleDb.OleDbException E)
99. {
100. connection.Close();
101. throw new Exception(E.Message);
102. }
103. }
104. }
105. }
106. /// <summary>
107. /// 执行SQL语句，设置命令的执行等待时间
108. /// </summary>
109. /// <param name="SQLString"></param>
110. /// <param name="Times"></param>
111. /// <returns></returns>
112. public static int ExecuteSqlByTime(string SQLString, int Times)
113. {
114. using (OleDbConnection connection = new OleDbConnection(connectionString))
115. {
116. using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
117. {
118. try
119. {
120. connection.Open();
121. cmd.CommandTimeout = Times;
122. int rows = cmd.ExecuteNonQuery();
123. return rows;
124. }
125. catch (System.Data.OleDb.OleDbException E)
126. {
127. connection.Close();
128. throw new Exception(E.Message);
129. }
130. }
131. }
132. }
133. /// <summary>
134. /// 执行多条SQL语句，实现数据库事务。
135. /// </summary>
136. /// <param name="SQLStringList">多条SQL语句</param>
137. public static void ExecuteSqlTran(ArrayList SQLStringList)
138. {
139. using (OleDbConnection conn = new OleDbConnection(connectionString))
140. {
141. conn.Open();
142. OleDbCommand cmd = new OleDbCommand();
143. cmd.Connection = conn;
144. OleDbTransaction tx = conn.BeginTransaction();
145. cmd.Transaction = tx;
146. try
147. {
148. for (int n = 0; n < SQLStringList.Count; n++)
149. {
150. string strsql = SQLStringList[n].ToString();
151. if (strsql.Trim().Length > 1)
152. {
153. cmd.CommandText = strsql;
154. cmd.ExecuteNonQuery();
155. }
156. }
157. tx.Commit();
158. }
159. catch (System.Data.OleDb.OleDbException E)
160. {
161. tx.Rollback();
162. throw new Exception(E.Message);
163. }
164. }
165. }
166. /// <summary>
167. /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
168. /// </summary>
169. /// <param name="strSQL">SQL语句</param>
170. /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
171. /// <returns>影响的记录数</returns>
172. public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
173. {
174. using (OleDbConnection connection = new OleDbConnection(connectionString))
175. {
176. OleDbCommand cmd = new OleDbCommand(strSQL, connection);
177. System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", SqlDbType.Image);
178. myParameter.Value = fs;
179. cmd.Parameters.Add(myParameter);
180. try
181. {
182. connection.Open();
183. int rows = cmd.ExecuteNonQuery();
184. return rows;
185. }
186. catch (System.Data.OleDb.OleDbException E)
187. {
188. throw new Exception(E.Message);
189. }
190. finally
191. {
192. cmd.Dispose();
193. connection.Close();
194. }
195. }
196. }
197. /// <summary>
198. /// 执行一条计算查询结果语句，返回查询结果（object）。
199. /// </summary>
200. /// <param name="SQLString">计算查询结果语句</param>
201. /// <returns>查询结果（object）</returns>
202. public static object GetSingle(string SQLString)
203. {
204. using (OleDbConnection connection = new OleDbConnection(connectionString))
205. {
206. using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
207. {
208. try
209. {
210. connection.Open();
211. object obj = cmd.ExecuteScalar();
212. if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
213. {
214. return null;
215. }
216. else
217. {
218. return obj;
219. }
220. }
221. catch (System.Data.OleDb.OleDbException e)
222. {
223. connection.Close();
224. throw new Exception(e.Message);
225. }
226. }
227. }
228. }
229. /// <summary>
230. /// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
231. /// </summary>
232. /// <param name="strSQL">查询语句</param>
233. /// <returns>SqlDataReader</returns>
234. public static OleDbDataReader ExecuteReader(string strSQL)
235. {
236. OleDbConnection connection = new OleDbConnection(connectionString);
237. OleDbCommand cmd = new OleDbCommand(strSQL, connection);
238. try
239. {
240. connection.Open();
241. OleDbDataReader myReader = cmd.ExecuteReader();
242. return myReader;
243. }
244. catch (System.Data.OleDb.OleDbException e)
245. {
246. throw new Exception(e.Message);
247. }
248. //finally //不能在此关闭，否则，返回的对象将无法使用
249. //{
250. // cmd.Dispose();
251. // connection.Close();
252. //}
253. }
254. /// <summary>
255. /// 执行查询语句，返回DataSet
256. /// </summary>
257. /// <param name="SQLString">查询语句</param>
258. /// <returns>DataSet</returns>
259. public static DataSet Query(string SQLString)
260. {
261. using (OleDbConnection connection = new OleDbConnection(connectionString))
262. {
263. DataSet ds = new DataSet();
264. try
265. {
266. connection.Open();
267. OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
268. command.Fill(ds, "ds");
269. }
270. catch (System.Data.OleDb.OleDbException ex)
271. {
272. throw new Exception(ex.Message);
273. }
274. return ds;
275. }
276. }
277. /// <summary>
278. /// 执行查询语句，返回DataSet,设置命令的执行等待时间
279. /// </summary>
280. /// <param name="SQLString"></param>
281. /// <param name="Times"></param>
282. /// <returns></returns>
283. public static DataSet Query(string SQLString, int Times)
284. {
285. using (OleDbConnection connection = new OleDbConnection(connectionString))
286. {
287. DataSet ds = new DataSet();
288. try
289. {
290. connection.Open();
291. OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
292. command.SelectCommand.CommandTimeout = Times;
293. command.Fill(ds, "ds");
294. }
295. catch (System.Data.OleDb.OleDbException ex)
296. {
297. throw new Exception(ex.Message);
298. }
299. return ds;
300. }
301. }
302. #endregion
303. #region 执行带参数的SQL语句
304. /// <summary>
305. /// 执行SQL语句，返回影响的记录数
306. /// </summary>
307. /// <param name="SQLString">SQL语句</param>
308. /// <returns>影响的记录数</returns>
309. public static int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms)
310. {
311. using (OleDbConnection connection = new OleDbConnection(connectionString))
312. {
313. using (OleDbCommand cmd = new OleDbCommand())
314. {
315. try
316. {
317. PrepareCommand(cmd, connection, null, SQLString, cmdParms);
318. int rows = cmd.ExecuteNonQuery();
319. cmd.Parameters.Clear();
320. return rows;
321. }
322. catch (System.Data.OleDb.OleDbException E)
323. {
324. throw new Exception(E.Message);
325. }
326. }
327. }
328. }
329. /// <summary>
330. /// 执行多条SQL语句，实现数据库事务。
331. /// </summary>
332. /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OleDbParameter[]）</param>
333. public static void ExecuteSqlTran(Hashtable SQLStringList)
334. {
335. using (OleDbConnection conn = new OleDbConnection(connectionString))
336. {
337. conn.Open();
338. using (OleDbTransaction trans = conn.BeginTransaction())
339. {
340. OleDbCommand cmd = new OleDbCommand();
341. try
342. {
343. //循环
344. foreach (DictionaryEntry myDE in SQLStringList)
345. {
346. string cmdText = myDE.Key.ToString();
347. OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value;
348. PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
349. int val = cmd.ExecuteNonQuery();
350. cmd.Parameters.Clear();
351. trans.Commit();
352. }
353. }
354. catch
355. {
356. trans.Rollback();
357. throw;
358. }
359. }
360. }
361. }
362. /// <summary>
363. /// 执行一条计算查询结果语句，返回查询结果（object）。
364. /// </summary>
365. /// <param name="SQLString">计算查询结果语句</param>
366. /// <returns>查询结果（object）</returns>
367. public static object GetSingle(string SQLString, params OleDbParameter[] cmdParms)
368. {
369. using (OleDbConnection connection = new OleDbConnection(connectionString))
370. {
371. using (OleDbCommand cmd = new OleDbCommand())
372. {
373. try
374. {
375. PrepareCommand(cmd, connection, null, SQLString, cmdParms);
376. object obj = cmd.ExecuteScalar();
377. cmd.Parameters.Clear();
378. if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
379. {
380. return null;
381. }
382. else
383. {
384. return obj;
385. }
386. }
387. catch (System.Data.OleDb.OleDbException e)
388. {
389. throw new Exception(e.Message);
390. }
391. }
392. }
393. }
394. /// <summary>
395. /// 执行查询语句，返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接)
396. /// </summary>
397. /// <param name="strSQL">查询语句</param>
398. /// <returns>SqlDataReader</returns>
399. public static OleDbDataReader ExecuteReader(string SQLString, params OleDbParameter[] cmdParms)
400. {
401. OleDbConnection connection = new OleDbConnection(connectionString);
402. OleDbCommand cmd = new OleDbCommand();
403. try
404. {
405. PrepareCommand(cmd, connection, null, SQLString, cmdParms);
406. OleDbDataReader myReader = cmd.ExecuteReader();
407. cmd.Parameters.Clear();
408. return myReader;
409. }
410. catch (System.Data.OleDb.OleDbException e)
411. {
412. throw new Exception(e.Message);
413. }
414. //finally //不能在此关闭，否则，返回的对象将无法使用
415. //{
416. // cmd.Dispose();
417. // connection.Close();
418. //}
419. }
420. /// <summary>
421. /// 执行查询语句，返回DataSet
422. /// </summary>
423. /// <param name="SQLString">查询语句</param>
424. /// <returns>DataSet</returns>
425. public static DataSet Query(string SQLString, params OleDbParameter[] cmdParms)
426. {
427. using (OleDbConnection connection = new OleDbConnection(connectionString))
428. {
429. OleDbCommand cmd = new OleDbCommand();
430. PrepareCommand(cmd, connection, null, SQLString, cmdParms);
431. using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
432. {
433. DataSet ds = new DataSet();
434. try
435. {
436. da.Fill(ds, "ds");
437. cmd.Parameters.Clear();
438. }
439. catch (System.Data.OleDb.OleDbException ex)
440. {
441. throw new Exception(ex.Message);
442. }
443. return ds;
444. }
445. }
446. }
447. private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
448. {
449. if (conn.State != ConnectionState.Open)
450. conn.Open();
451. cmd.Connection = conn;
452. cmd.CommandText = cmdText;
453. if (trans != null)
454. cmd.Transaction = trans;
455. cmd.CommandType = CommandType.Text;//cmdType;
456. if (cmdParms != null)
457. {
458. foreach (OleDbParameter parameter in cmdParms)
459. {
460. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
461. (parameter.Value == null))
462. {
463. parameter.Value = DBNull.Value;
464. }
465. cmd.Parameters.Add(parameter);
466. }
467. }
468. }
469. #endregion
470. #region 获取根据指定字段排序并分页查询。
471. /**/
472. /// <summary>
473. /// 分页查询数据记录总数获取
474. /// </summary>
475. /// <param name="_tbName">----要显示的表或多个表的连接</param>
476. /// <param name="_ID">----主表的主键</param>
477. /// <param name="_strCondition">----查询条件,不需where</param>
478. /// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
479. /// <returns></returns>
480. public static string getPageListCounts(string _ID, string _tbName, string _strCondition, int _Dist)
481. {
482. //---存放取得查询结果总数的查询语句
483. //---对含有DISTINCT的查询进行SQL构造
484. //---对含有DISTINCT的总数查询进行SQL构造
485. string strTmp = "", SqlSelect = "", SqlCounts = "";
486. if (_Dist == 0)
487. {
488. SqlSelect = "Select ";
489. SqlCounts = "COUNT(*)";
490. }
491. else
492. {
493. SqlSelect = "Select DISTINCT ";
494. SqlCounts = "COUNT(DISTINCT " + _ID + ")";
495. }
496. if (_strCondition == string.Empty)
497. {
498. strTmp = SqlSelect + " "+ SqlCounts + " FROM " + _tbName;
499. }
500. else
501. {
502. strTmp = SqlSelect + " "+ SqlCounts + " FROM " + " Where (1=1) " + _strCondition;
503. }
504. return strTmp;
505. }
506. /// <summary>
507. /// 智能返回SQL语句
508. /// </summary>
509. /// <param name="primaryKey">主键（不能为空）</param>
510. /// <param name="queryFields">提取字段（不能为空）</param>
511. /// <param name="tableName">表（理论上允许多表）</param>
512. /// <param name="condition">条件（可以空）</param>
513. /// <param name="OrderBy">排序，格式：字段名+""+ASC（可以空）</param>
514. /// <param name="pageSize">分页数（不能为空）</param>
515. /// <param name="pageIndex">当前页，起始为：1（不能为空）</param>
516. /// <returns></returns>
517. public static string getPageListSql(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex)
518. {
519. string strTmp = ""; //---strTmp用于返回的SQL语句
520. string SqlSelect = "", SqlPrimaryKeySelect = "", strOrderBy = "", strWhere = " where 1=1 ", strTop = "";
521. //0：分页数量
522. //1:提取字段
523. //2:表
524. //3:条件
525. //4:主键不存在的记录
526. //5:排序
527. SqlSelect = " select top {0} {1} from {2} {3} {4} {5}";
528. //0:主键
529. //1:TOP数量,为分页数*(排序号-1)
530. //2:表
531. //3:条件
532. //4:排序
533. SqlPrimaryKeySelect = " and {0} not in (select {1} {0} from {2} {3} {4}) ";
534. if (orderBy != "")
535. strOrderBy = " order by " + orderBy;
536. if (condition != "")
537. strWhere += " and " + condition;
538. int pageindexsize = (pageIndex - 1) * pageSize;
539. if (pageindexsize > 0)
540. {
541. strTop = " top " + pageindexsize.ToString();
542. SqlPrimaryKeySelect = String.Format(SqlPrimaryKeySelect, primaryKey, strTop, tableName, strWhere, strOrderBy);
543. strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, SqlPrimaryKeySelect, strOrderBy);
544. }
545. else
546. {
547. strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, "", strOrderBy);
548. }
549. return strTmp;
550. }
551. /// <summary>
552. /// 获取根据指定字段排序并分页查询。DataSet
553. /// </summary>
554. /// <param name="pageSize">每页要显示的记录的数目</param>
555. /// <param name="pageIndex">要显示的页的索引</param>
556. /// <param name="tableName">要查询的数据表</param>
557. /// <param name="queryFields">要查询的字段,如果是全部字段请填写：*</param>
558. /// <param name="primaryKey">主键字段，类似排序用到</param>
559. /// <param name="orderBy">是否为升序排列：0为升序，1为降序</param>
560. /// <param name="condition">查询的筛选条件</param>
561. /// <returns>返回排序并分页查询的DataSet</returns>
562. public static DataSet GetPagingList(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex)
563. {
564. string sql = getPageListSql(primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex);
565. return Query(sql);
566. }
567. public static string GetPagingListSQL(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex)
568. {
569. string sql = getPageListSql(primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex);
570. return sql;
571. }
572. public static int GetRecordCount(string _ID, string _tbName, string _strCondition, int _Dist)
573. {
574. string sql = getPageListCounts( _ID, _tbName, _strCondition, _Dist);
575. object obj = DbHelperACE.GetSingle(sql);
576. if (obj == null)
577. {
578. return 1;
579. }
580. else
581. {
582. return int.Parse(obj.ToString());
583. }
584. }
585. #endregion
586. }
587. }