AcessHelper类
1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Data.OleDb;
5 using System.Collections;
6
7 namespace MobileHealth
8 {
9 /// <summary>
10 /// AcceHelper 的摘要说明
11 /// </summary>
12 public static class AccessHelper
13 {
14 //数据库连接字符串
15 public static readonly string conn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
16 // 用于缓存参数的HASH表
17 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
18 /// <summary>
19 /// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
20 /// </summary>
21 /// <param name="connectionString">一个有效的连接字符串</param>
22 /// <param name="commandText">存储过程名称或者sql命令语句</param>
23 /// <param name="commandParameters">执行命令所用参数的集合</param>
24 /// <returns>执行命令所影响的行数</returns>
25 public static int ExecuteNonQuery(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
26 {
27 OleDbCommand cmd = new OleDbCommand();
28 using (OleDbConnection conn = new OleDbConnection(connectionString))
29 {
30 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
31 int val = cmd.ExecuteNonQuery();
32 cmd.Parameters.Clear();
33 return val;
34 }
35 }
36 /// <summary>
37 /// 用现有的数据库连接执行一个sql命令（不返回数据集）
38 /// </summary>
39 /// <remarks>
40 ///举例:
41 /// int result = ExecuteNonQuery(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
42 /// </remarks>
43 /// <param name="conn">一个现有的数据库连接</param>
44 /// <param name="commandText">存储过程名称或者sql命令语句</param>
45 /// <param name="commandParameters">执行命令所用参数的集合</param>
46 /// <returns>执行命令所影响的行数</returns>
47 public static int ExecuteNonQuery(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
48 {
49 OleDbCommand cmd = new OleDbCommand();
50 PrepareCommand(cmd, connection, null, cmdText, commandParameters);
51 int val = cmd.ExecuteNonQuery();
52 cmd.Parameters.Clear();
53 return val;
54 }
55 /// <summary>
56 ///使用现有的SQL事务执行一个sql命令（不返回数据集）
57 /// </summary>
58 /// <remarks>
59 ///举例:
60 /// int result = ExecuteNonQuery(trans, "PublishOrders", new OleDbParameter("@prodid", 24));
61 /// </remarks>
62 /// <param name="trans">一个现有的事务</param>
63 /// <param name="commandText">存储过程名称或者sql命令语句</param>
64 /// <param name="commandParameters">执行命令所用参数的集合</param>
65 /// <returns>执行命令所影响的行数</returns>
66 public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] commandParameters)
67 {
68 OleDbCommand cmd = new OleDbCommand();
69 PrepareCommand(cmd, trans.Connection, trans, cmdText, commandParameters);
70 int val = cmd.ExecuteNonQuery();
71 cmd.Parameters.Clear();
72 return val;
73 }
74 /// <summary>
75 /// 用执行的数据库连接执行一个返回数据集的sql命令
76 /// </summary>
77 /// <remarks>
78 /// 举例:
79 /// OleDbDataReader r = ExecuteReader(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
80 /// </remarks>
81 /// <param name="connectionString">一个有效的连接字符串</param>
82 /// <param name="commandText">存储过程名称或者sql命令语句</param>
83 /// <param name="commandParameters">执行命令所用参数的集合</param>
84 /// <returns>包含结果的读取器</returns>
85 public static OleDbDataReader ExecuteReader(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
86 {
87 //创建一个SqlCommand对象
88 OleDbCommand cmd = new OleDbCommand();
89 //创建一个SqlConnection对象
90 OleDbConnection conn = new OleDbConnection(connectionString);
91 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
92 //因此commandBehaviour.CloseConnection 就不会执行
93 try
94 {
95 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数
96 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
97 //调用 SqlCommand 的 ExecuteReader 方法
98 OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
99 //清除参数
100 cmd.Parameters.Clear();
101 return reader;
102 }
103 catch
104 {
105 //关闭连接，抛出异常
106 conn.Close();
107 throw;
108 }
109 }
110 /// <summary>
111 /// 返回一个DataSet数据集
112 /// </summary>
113 /// <param name="connectionString">一个有效的连接字符串</param>
114 /// <param name="cmdText">存储过程名称或者sql命令语句</param>
115 /// <param name="commandParameters">执行命令所用参数的集合</param>
116 /// <returns>包含结果的数据集</returns>
117 public static DataSet ExecuteDataSet(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
118 {
119 //创建一个SqlCommand对象，并对其进行初始化
120 OleDbCommand cmd = new OleDbCommand();
121 using (OleDbConnection conn = new OleDbConnection(connectionString))
122 {
123 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
124 //创建SqlDataAdapter对象以及DataSet
125 OleDbDataAdapter da = new OleDbDataAdapter(cmd);
126 DataSet ds = new DataSet();
127 try
128 {
129 //填充ds
130 da.Fill(ds);
131 // 清除cmd的参数集合
132 cmd.Parameters.Clear();
133 //返回ds
134 return ds;
135 }
136 catch
137 {
138 //关闭连接，抛出异常
139 conn.Close();
140 throw;
141 }
142 }
143 }
144 /// <summary>
145 /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
146 /// </summary>
147 /// <remarks>
148 ///例如:
149 /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
150 /// </remarks>
151 ///<param name="connectionString">一个有效的连接字符串</param>
152 /// <param name="commandText">存储过程名称或者sql命令语句</param>
153 /// <param name="commandParameters">执行命令所用参数的集合</param>
154 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
155 public static object ExecuteScalar(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
156 {
157 OleDbCommand cmd = new OleDbCommand();
158 using (OleDbConnection connection = new OleDbConnection(connectionString))
159 {
160 PrepareCommand(cmd, connection, null, cmdText, commandParameters);
161 object val = cmd.ExecuteScalar();
162 cmd.Parameters.Clear();
163 return val;
164 }
165 }
166 /// <summary>
167 /// 用指定的数据库连接执行一个命令并返回一个数据集的第一列
168 /// </summary>
169 /// <remarks>
170 /// 例如:
171 /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
172 /// </remarks>
173 /// <param name="conn">一个存在的数据库连接</param>
174 /// <param name="commandText">存储过程名称或者sql命令语句</param>
175 /// <param name="commandParameters">执行命令所用参数的集合</param>
176 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
177 public static object ExecuteScalar(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
178 {
179 OleDbCommand cmd = new OleDbCommand();
180 PrepareCommand(cmd, connection, null, cmdText, commandParameters);
181 object val = cmd.ExecuteScalar();
182 cmd.Parameters.Clear();
183 return val;
184 }
185 /// <summary>
186 /// 将参数集合添加到缓存
187 /// </summary>
188 /// <param name="cacheKey">添加到缓存的变量</param>
189 /// <param name="cmdParms">一个将要添加到缓存的sql参数集合</param>
190 public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters)
191 {
192 parmCache[cacheKey] = commandParameters;
193 }
194 /// <summary>
195 /// 找回缓存参数集合
196 /// </summary>
197 /// <param name="cacheKey">用于找回参数的关键字</param>
198 /// <returns>缓存的参数集合</returns>
199 public static OleDbParameter[] GetCachedParameters(string cacheKey)
200 {
201 OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
202 if (cachedParms == null)
203 return null;
204 OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
205 for (int i = 0, j = cachedParms.Length; i < j; i++)
206 clonedParms = (OleDbParameter[])((ICloneable)cachedParms).Clone();
207 return clonedParms;
208 }
209 /// <summary>
210 /// 准备执行一个命令
211 /// </summary>
212 /// <param name="cmd">sql命令</param>
213 /// <param name="conn">Sql连接</param>
214 /// <param name="trans">Sql事务</param>
215 /// <param name="cmdText">命令文本,例如：Select * from Products</param>
216 /// <param name="cmdParms">执行命令的参数</param>
217 private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
218 {
219 //判断连接的状态。如果是关闭状态，则打开
220 if (conn.State != ConnectionState.Open)
221 conn.Open();
222 //cmd属性赋值
223 cmd.Connection = conn;
224 cmd.CommandText = cmdText;
225 //是否需要用到事务处理
226 if (trans != null)
227 cmd.Transaction = trans;
228 cmd.CommandType = CommandType.Text;
229 //添加cmd需要的存储过程参数
230 if (cmdParms != null)
231 {
232 foreach (OleDbParameter parm in cmdParms)
233 cmd.Parameters.Add(parm);
234 }
235 }
236 }
237 }
238
2 using System.Data;
3 using System.Configuration;
4 using System.Data.OleDb;
5 using System.Collections;
6
7 namespace MobileHealth
8 {
9 /// <summary>
10 /// AcceHelper 的摘要说明
11 /// </summary>
12 public static class AccessHelper
13 {
14 //数据库连接字符串
15 public static readonly string conn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
16 // 用于缓存参数的HASH表
17 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
18 /// <summary>
19 /// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
20 /// </summary>
21 /// <param name="connectionString">一个有效的连接字符串</param>
22 /// <param name="commandText">存储过程名称或者sql命令语句</param>
23 /// <param name="commandParameters">执行命令所用参数的集合</param>
24 /// <returns>执行命令所影响的行数</returns>
25 public static int ExecuteNonQuery(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
26 {
27 OleDbCommand cmd = new OleDbCommand();
28 using (OleDbConnection conn = new OleDbConnection(connectionString))
29 {
30 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
31 int val = cmd.ExecuteNonQuery();
32 cmd.Parameters.Clear();
33 return val;
34 }
35 }
36 /// <summary>
37 /// 用现有的数据库连接执行一个sql命令（不返回数据集）
38 /// </summary>
39 /// <remarks>
40 ///举例:
41 /// int result = ExecuteNonQuery(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
42 /// </remarks>
43 /// <param name="conn">一个现有的数据库连接</param>
44 /// <param name="commandText">存储过程名称或者sql命令语句</param>
45 /// <param name="commandParameters">执行命令所用参数的集合</param>
46 /// <returns>执行命令所影响的行数</returns>
47 public static int ExecuteNonQuery(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
48 {
49 OleDbCommand cmd = new OleDbCommand();
50 PrepareCommand(cmd, connection, null, cmdText, commandParameters);
51 int val = cmd.ExecuteNonQuery();
52 cmd.Parameters.Clear();
53 return val;
54 }
55 /// <summary>
56 ///使用现有的SQL事务执行一个sql命令（不返回数据集）
57 /// </summary>
58 /// <remarks>
59 ///举例:
60 /// int result = ExecuteNonQuery(trans, "PublishOrders", new OleDbParameter("@prodid", 24));
61 /// </remarks>
62 /// <param name="trans">一个现有的事务</param>
63 /// <param name="commandText">存储过程名称或者sql命令语句</param>
64 /// <param name="commandParameters">执行命令所用参数的集合</param>
65 /// <returns>执行命令所影响的行数</returns>
66 public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] commandParameters)
67 {
68 OleDbCommand cmd = new OleDbCommand();
69 PrepareCommand(cmd, trans.Connection, trans, cmdText, commandParameters);
70 int val = cmd.ExecuteNonQuery();
71 cmd.Parameters.Clear();
72 return val;
73 }
74 /// <summary>
75 /// 用执行的数据库连接执行一个返回数据集的sql命令
76 /// </summary>
77 /// <remarks>
78 /// 举例:
79 /// OleDbDataReader r = ExecuteReader(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
80 /// </remarks>
81 /// <param name="connectionString">一个有效的连接字符串</param>
82 /// <param name="commandText">存储过程名称或者sql命令语句</param>
83 /// <param name="commandParameters">执行命令所用参数的集合</param>
84 /// <returns>包含结果的读取器</returns>
85 public static OleDbDataReader ExecuteReader(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
86 {
87 //创建一个SqlCommand对象
88 OleDbCommand cmd = new OleDbCommand();
89 //创建一个SqlConnection对象
90 OleDbConnection conn = new OleDbConnection(connectionString);
91 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
92 //因此commandBehaviour.CloseConnection 就不会执行
93 try
94 {
95 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数
96 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
97 //调用 SqlCommand 的 ExecuteReader 方法
98 OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
99 //清除参数
100 cmd.Parameters.Clear();
101 return reader;
102 }
103 catch
104 {
105 //关闭连接，抛出异常
106 conn.Close();
107 throw;
108 }
109 }
110 /// <summary>
111 /// 返回一个DataSet数据集
112 /// </summary>
113 /// <param name="connectionString">一个有效的连接字符串</param>
114 /// <param name="cmdText">存储过程名称或者sql命令语句</param>
115 /// <param name="commandParameters">执行命令所用参数的集合</param>
116 /// <returns>包含结果的数据集</returns>
117 public static DataSet ExecuteDataSet(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
118 {
119 //创建一个SqlCommand对象，并对其进行初始化
120 OleDbCommand cmd = new OleDbCommand();
121 using (OleDbConnection conn = new OleDbConnection(connectionString))
122 {
123 PrepareCommand(cmd, conn, null, cmdText, commandParameters);
124 //创建SqlDataAdapter对象以及DataSet
125 OleDbDataAdapter da = new OleDbDataAdapter(cmd);
126 DataSet ds = new DataSet();
127 try
128 {
129 //填充ds
130 da.Fill(ds);
131 // 清除cmd的参数集合
132 cmd.Parameters.Clear();
133 //返回ds
134 return ds;
135 }
136 catch
137 {
138 //关闭连接，抛出异常
139 conn.Close();
140 throw;
141 }
142 }
143 }
144 /// <summary>
145 /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
146 /// </summary>
147 /// <remarks>
148 ///例如:
149 /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
150 /// </remarks>
151 ///<param name="connectionString">一个有效的连接字符串</param>
152 /// <param name="commandText">存储过程名称或者sql命令语句</param>
153 /// <param name="commandParameters">执行命令所用参数的集合</param>
154 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
155 public static object ExecuteScalar(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
156 {
157 OleDbCommand cmd = new OleDbCommand();
158 using (OleDbConnection connection = new OleDbConnection(connectionString))
159 {
160 PrepareCommand(cmd, connection, null, cmdText, commandParameters);
161 object val = cmd.ExecuteScalar();
162 cmd.Parameters.Clear();
163 return val;
164 }
165 }
166 /// <summary>
167 /// 用指定的数据库连接执行一个命令并返回一个数据集的第一列
168 /// </summary>
169 /// <remarks>
170 /// 例如:
171 /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
172 /// </remarks>
173 /// <param name="conn">一个存在的数据库连接</param>
174 /// <param name="commandText">存储过程名称或者sql命令语句</param>
175 /// <param name="commandParameters">执行命令所用参数的集合</param>
176 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
177 public static object ExecuteScalar(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
178 {
179 OleDbCommand cmd = new OleDbCommand();
180 PrepareCommand(cmd, connection, null, cmdText, commandParameters);
181 object val = cmd.ExecuteScalar();
182 cmd.Parameters.Clear();
183 return val;
184 }
185 /// <summary>
186 /// 将参数集合添加到缓存
187 /// </summary>
188 /// <param name="cacheKey">添加到缓存的变量</param>
189 /// <param name="cmdParms">一个将要添加到缓存的sql参数集合</param>
190 public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters)
191 {
192 parmCache[cacheKey] = commandParameters;
193 }
194 /// <summary>
195 /// 找回缓存参数集合
196 /// </summary>
197 /// <param name="cacheKey">用于找回参数的关键字</param>
198 /// <returns>缓存的参数集合</returns>
199 public static OleDbParameter[] GetCachedParameters(string cacheKey)
200 {
201 OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
202 if (cachedParms == null)
203 return null;
204 OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
205 for (int i = 0, j = cachedParms.Length; i < j; i++)
206 clonedParms = (OleDbParameter[])((ICloneable)cachedParms).Clone();
207 return clonedParms;
208 }
209 /// <summary>
210 /// 准备执行一个命令
211 /// </summary>
212 /// <param name="cmd">sql命令</param>
213 /// <param name="conn">Sql连接</param>
214 /// <param name="trans">Sql事务</param>
215 /// <param name="cmdText">命令文本,例如：Select * from Products</param>
216 /// <param name="cmdParms">执行命令的参数</param>
217 private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
218 {
219 //判断连接的状态。如果是关闭状态，则打开
220 if (conn.State != ConnectionState.Open)
221 conn.Open();
222 //cmd属性赋值
223 cmd.Connection = conn;
224 cmd.CommandText = cmdText;
225 //是否需要用到事务处理
226 if (trans != null)
227 cmd.Transaction = trans;
228 cmd.CommandType = CommandType.Text;
229 //添加cmd需要的存储过程参数
230 if (cmdParms != null)
231 {
232 foreach (OleDbParameter parm in cmdParms)
233 cmd.Parameters.Add(parm);
234 }
235 }
236 }
237 }
238
