也谈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 
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. }

    使用方法跟SqlHelper类相似。