也谈OleDbHelper,由SqlHelper类转OleDbHelper类，access操作

今意外看见一贴子谈OleDbHelper，突然想起自已一直用的由SqlHelper类转成的OleDbHelper，在此分享，自己用了二年了，还没发现异常， 代码如下： using System; using System.Data; using System.Data.OleDb; using System.Configuration; using System.Colle

今意外看见一贴子谈OleDbHelper，突然想起自已一直用的由SqlHelper类转成的OleDbHelper，在此分享，自己用了二年了，还没发现异常， 代码如下：

1. using System;
2. using System.Data;
3. using System.Data.OleDb;
4. using System.Configuration;
5. using System.Collections;
6. using System.Data.Sql;
7. using System.Text;
8. namespace Lihui.Common
9. {
10. ///
11. /// Summary description for OleDbHelper
12. ///
13. public class OleDbHelper
14. {
15. //Database connection strings
16. public static readonly string CONN_STRING = ConfigurationManager.AppSettings["OleDbConnectionString"];
17. public static readonly string CONN_STRING1 = ConfigurationManager.AppSettings["OleDbConnectionString1"];
18. // Hashtable to store cached parameters
19. private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
21. #region =ExecuteNonQuery=
23. public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText)
24. {
25. return ExecuteNonQuery(connString, cmdType, cmdText, null);
26. }
28. public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText)
29. {
30. return ExecuteNonQuery(conn, cmdType, cmdText, null);
31. }
33. public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText)
34. {
35. return ExecuteNonQuery(trans, cmdType, cmdText, null);
36. }
38. public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
39. {
41. OleDbCommand cmd = new OleDbCommand();
43. using (OleDbConnection conn = new OleDbConnection(connString))
44. {
45. PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
46. int val = cmd.ExecuteNonQuery();
47. //清除cmd的参数
48. cmd.Parameters.Clear();
49. if (conn.State == ConnectionState.Open)
50. {
51. conn.Close();
52. }
53. return val;
54. }
55. }
57. public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
58. {
60. OleDbCommand cmd = new OleDbCommand();
62. PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
63. int val = cmd.ExecuteNonQuery();
64. cmd.Parameters.Clear();
65. if (conn.State == ConnectionState.Open)
66. {
67. conn.Close();
68. }
69. return val;
70. }
72. public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
73. {
74. OleDbCommand cmd = new OleDbCommand();
75. PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
76. int val = cmd.ExecuteNonQuery();
77. cmd.Parameters.Clear();
78. if (cmd.Connection.State == ConnectionState.Open)
79. {
80. cmd.Connection.Close();
81. }
82. return val;
83. }
85. #endregion
87. #region =ExecuteReader=
89. public static OleDbDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
90. {
91. //pass through the call providing null for the set of OleDbParameters
92. return ExecuteReader(connectionString, commandType, commandText, (OleDbParameter[])null);
93. }
95. public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
96. {
97. OleDbCommand cmd = new OleDbCommand();
98. OleDbConnection conn = new OleDbConnection(connString);
100. try
101. {
102. PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
103. OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
104. cmd.Parameters.Clear();
105. if (conn.State == ConnectionState.Open)
106. {
107. conn.Close();
108. }
109. return rdr;
110. }
111. catch
112. {
113. conn.Close();
114. throw;
115. }
116. }
118. #endregion
120. #region =ExecuteDataset=
122. public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
123. {
124. return ExecuteDataset(connectionString, commandType, commandText, (OleDbParameter[])null);
125. }
127. public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
128. {
129. using (OleDbConnection cn = new OleDbConnection(connectionString))
130. {
131. cn.Open();
133. //调用重载方法
134. return ExecuteDataset(cn, commandType, commandText, commandParameters);
135. }
136. }
140. public static DataSet ExecuteDataset(OleDbConnection connection, CommandType commandType, string commandText)
141. {
142. return ExecuteDataset(connection, commandType, commandText, (OleDbParameter[])null);
143. }
145. public static DataSet ExecuteDataset(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
146. {
147. //创建一个OleDbCommand对象，并对其进行初始化
148. OleDbCommand cmd = new OleDbCommand();
149. PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters);
151. //创建OleDbDataAdapter对象以及DataSet
152. OleDbDataAdapter da = new OleDbDataAdapter(cmd);
153. DataSet ds = new DataSet();
155. //填充ds
156. da.Fill(ds);
158. // 清除cmd的参数集合
159. cmd.Parameters.Clear();
160. if (cmd.Connection.State == ConnectionState.Open)
161. {
162. cmd.Connection.Close();
163. }
164. //返回ds
165. return ds;
166. }
168. #endregion
170. #region =ExecuteDataTable=
172. public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText)
173. {
174. return ExecuteDataTable(connectionString, commandType, commandText, (OleDbParameter[])null);
175. }
177. public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
178. {
179. using (OleDbConnection cn = new OleDbConnection(connectionString))
180. {
181. cn.Open();
183. //调用重载方法
184. return ExecuteDataTable(cn, commandType, commandText, commandParameters);
185. }
186. }
190. public static DataTable ExecuteDataTable(OleDbConnection connection, CommandType commandType, string commandText)
191. {
192. return ExecuteDataTable(connection, commandType, commandText, (OleDbParameter[])null);
193. }
195. public static DataTable ExecuteDataTable(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
196. {
197. //创建一个OleDbCommand对象，并对其进行初始化
198. OleDbCommand cmd = new OleDbCommand();
199. PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters);
201. //创建OleDbDataAdapter对象以及DataSet
202. OleDbDataAdapter da = new OleDbDataAdapter(cmd);
203. DataSet ds = new DataSet();
205. //填充ds
206. da.Fill(ds);
208. // 清除cmd的参数集合
209. cmd.Parameters.Clear();
210. if (cmd.Connection.State == ConnectionState.Open)
211. {
212. cmd.Connection.Close();
213. }
214. //返回ds
215. return ds.Tables[0];
216. }
218. #endregion
220. #region =ExecuteScalar=
222. public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText)
223. {
224. return ExecuteScalar(connString, cmdType, cmdText, null);
225. }
227. public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
228. {
229. OleDbCommand cmd = new OleDbCommand();
231. using (OleDbConnection conn = new OleDbConnection(connString))
232. {
233. PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
234. object val = cmd.ExecuteScalar();
235. cmd.Parameters.Clear();
236. if (conn.State == ConnectionState.Open)
237. {
238. conn.Close();
239. }
240. return val;
241. }
242. }
244. public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText)
245. {
246. return ExecuteScalar(conn, cmdType, cmdText, null);
247. }
249. public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
250. {
252. OleDbCommand cmd = new OleDbCommand();
254. PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
255. object val = cmd.ExecuteScalar();
256. cmd.Parameters.Clear();
257. if (conn.State == ConnectionState.Open)
258. {
259. conn.Close();
260. }
261. return val;
262. }
264. #endregion
266. public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
267. {
268. parmCache[cacheKey] = cmdParms;
269. }
271. public static OleDbParameter[] GetCachedParameters(string cacheKey)
272. {
273. OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
275. if (cachedParms == null)
276. return null;
278. OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
280. for (int i = 0, j = cachedParms.Length; i < j; i++)
281. clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
283. return clonedParms;
284. }
286. public static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
287. {
288. //判断连接的状态。如果是关闭状态，则打开
289. if (conn.State != ConnectionState.Open)
290. conn.Open();
291. //cmd属性赋值
292. cmd.Connection = conn;
293. cmd.CommandText = cmdText;
294. //是否需要用到事务处理
295. if (trans != null)
296. cmd.Transaction = trans;
298. cmd.CommandType = cmdType;
299. //添加cmd需要的存储过程参数
300. if (cmdParms != null)
301. {
302. foreach (OleDbParameter parm in cmdParms)
303. cmd.Parameters.Add(parm);
304. }
305. }
306. }
307. }