快速拥有各种数据访问SqlHelper
时间：2021-07-01 10:21:17
帮助过：6人阅读
using System;
2 using System.Collections;
3 using System.Collections.Generic;
4 using System.Configuration;
5 using System.Data;
6 using System.Linq;
7 using System.Text;
8 using System.Threading.Tasks;
9 using System.Xml;
10 using MySql.Data;
11 using MySql.Data.MySqlClient;
12
13 namespace MySqlDAL
14 {
15 public sealed class MySqlHelper
16 {
17 public static string connectionString = System.Configuration.ConfigurationManager.AppSettings["connectionString"];
18 #region 私有构造函数和方法
19
20 private MySqlHelper() { }
21
22 /// <summary>
23 /// 将MySqlParameter参数数组(参数值)分配给MySqlCommand命令.
24 /// 这个方法将给任何一个参数分配DBNull.Value;
25 /// 该操作将阻止默认值的使用.
26 /// </summary>
27 /// <param name="command">命令名</param>
28 /// <param name="commandParameters">MySqlParameters数组</param>
29 private static void AttachParameters(MySqlCommand command, MySqlParameter[] commandParameters)
30 {
31 if (command == null) throw new ArgumentNullException("command");
32 if (commandParameters != null)
33 {
34 foreach (MySqlParameter p in commandParameters)
35 {
36 if (p != null)
37 {
38 // 检查未分配值的输出参数,将其分配以DBNull.Value.
39 if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
40 (p.Value == null))
41 {
42 p.Value = DBNull.Value;
43 }
44 command.Parameters.Add(p);
45 }
46 }
47 }
48 }
49
50 /// <summary>
51 /// 将DataRow类型的列值分配到MySqlParameter参数数组.
52 /// </summary>
53 /// <param name="commandParameters">要分配值的MySqlParameter参数数组</param>
54 /// <param name="dataRow">将要分配给存储过程参数的DataRow</param>
55 private static void AssignParameterValues(MySqlParameter[] commandParameters, DataRow dataRow)
56 {
57 if ((commandParameters == null) || (dataRow == null))
58 {
59 return;
60 }
61
62 int i = 0;
63 // 设置参数值
64 foreach (MySqlParameter commandParameter in commandParameters)
65 {
66 // 创建参数名称,如果不存在,只抛出一个异常.
67 if (commandParameter.ParameterName == null ||
68 commandParameter.ParameterName.Length <= 1)
69 throw new Exception(
70 string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName));
71 // 从dataRow的表中获取为参数数组中数组名称的列的索引.
72 // 如果存在和参数名称相同的列,则将列值赋给当前名称的参数.
73 if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
74 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
75 i++;
76 }
77 }
78
79 /// <summary>
80 /// 将一个对象数组分配给MySqlParameter参数数组.
81 /// </summary>
82 /// <param name="commandParameters">要分配值的MySqlParameter参数数组</param>
83 /// <param name="parameterValues">将要分配给存储过程参数的对象数组</param>
84 private static void AssignParameterValues(MySqlParameter[] commandParameters, object[] parameterValues)
85 {
86 if ((commandParameters == null) || (parameterValues == null))
87 {
88 return;
89 }
90
91 // 确保对象数组个数与参数个数匹配,如果不匹配,抛出一个异常.
92 if (commandParameters.Length != parameterValues.Length)
93 {
94 throw new ArgumentException("参数值个数与参数不匹配.");
95 }
96
97 // 给参数赋值
98 for (int i = 0, j = commandParameters.Length; i < j; i++)
99 {
100 // If the current array value derives from IDbDataParameter, then assign its Value property
101 if (parameterValues[i] is IDbDataParameter)
102 {
103 IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
104 if (paramInstance.Value == null)
105 {
106 commandParameters[i].Value = DBNull.Value;
107 }
108 else
109 {
110 commandParameters[i].Value = paramInstance.Value;
111 }
112 }
113 else if (parameterValues[i] == null)
114 {
115 commandParameters[i].Value = DBNull.Value;
116 }
117 else
118 {
119 commandParameters[i].Value = parameterValues[i];
120 }
121 }
122 }
123
124 /// <summary>
125 /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
126 /// </summary>
127 /// <param name="command">要处理的MySqlCommand</param>
128 /// <param name="connection">数据库连接</param>
129 /// <param name="transaction">一个有效的事务或者是null值</param>
130 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
131 /// <param name="commandText">存储过程名或都T-SQL命令文本</param>
132 /// <param name="commandParameters">和命令相关联的MySqlParameter参数数组,如果没有参数为‘null‘</param>
133 /// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
134 private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters, out bool mustCloseConnection)
135 {
136 if (command == null) throw new ArgumentNullException("command");
137 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
138
139 // If the provided connection is not open, we will open it
140 if (connection.State != ConnectionState.Open)
141 {
142 mustCloseConnection = true;
143 connection.Open();
144 }
145 else
146 {
147 mustCloseConnection = false;
148 }
149
150 // 给命令分配一个数据库连接.
151 command.Connection = connection;
152
153 // 设置命令文本(存储过程名或SQL语句)
154 command.CommandText = commandText;
155
156 // 分配事务
157 if (transaction != null)
158 {
159 if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
160 command.Transaction = transaction;
161 }
162
163 // 设置命令类型.
164 command.CommandType = commandType;
165
166 // 分配命令参数
167 if (commandParameters != null)
168 {
169 AttachParameters(command, commandParameters);
170 }
171 return;
172 }
173
174 #endregion 私有构造函数和方法结束
175
176 #region 数据库连接
177 /// <summary>
178 /// 一个有效的数据库连接字符串
179 /// </summary>
180 /// <returns></returns>
181 public static string GetConnSting()
182 {
183 return ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
184 }
185 /// <summary>
186 /// 一个有效的数据库连接对象
187 /// </summary>
188 /// <returns></returns>
189 public static MySqlConnection GetConnection()
190 {
191 MySqlConnection Connection = new MySqlConnection(MySqlHelper.GetConnSting());
192 return Connection;
193 }
194 #endregion
195
196 #region ExecuteNonQuery命令
197
198 /// <summary>
199 /// 执行指定连接字符串,类型的MySqlCommand.
200 /// </summary>
201 /// <remarks>
202 /// 示例:
203 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
204 /// </remarks>
205 /// <param name="connectionString">一个有效的数据库连接字符串</param>
206 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
207 /// <param name="commandText">存储过程名称或SQL语句</param>
208 /// <returns>返回命令影响的行数</returns>
209 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
210 {
211 return ExecuteNonQuery(connectionString, commandType, commandText, (MySqlParameter[])null);
212 }
213
214 /// <summary>
215 /// 执行指定连接字符串,类型的MySqlCommand.如果没有提供参数,不返回结果.
216 /// </summary>
217 /// <remarks>
218 /// 示例:
219 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
220 /// </remarks>
221 /// <param name="connectionString">一个有效的数据库连接字符串</param>
222 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
223 /// <param name="commandText">存储过程名称或SQL语句</param>
224 /// <param name="commandParameters">MySqlParameter参数数组</param>
225 /// <returns>返回命令影响的行数</returns>
226 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
227 {
228 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
229
230 using (MySqlConnection connection = new MySqlConnection(connectionString))
231 {
232 connection.Open();
233
234 return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
235 }
236 }
237
238 /// <summary>
239 /// 执行指定连接字符串的存储过程,将对象数组的值赋给存储过程参数,
240 /// 此方法需要在参数缓存方法中探索参数并生成参数.
241 /// </summary>
242 /// <remarks>
243 /// 这个方法没有提供访问输出参数和返回值.
244 /// 示例:
245 /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
246 /// </remarks>
247 /// <param name="connectionString">一个有效的数据库连接字符串/param>
248 /// <param name="spName">存储过程名称</param>
249 /// <param name="parameterValues">分配到存储过程输入参数的对象数组</param>
250 /// <returns>返回受影响的行数</returns>
251 public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
252 {
253 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
254 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
255
256 // 如果存在参数值
257 if ((parameterValues != null) && (parameterValues.Length > 0))
258 {
259 // 从探索存储过程参数(加载到缓存)并分配给存储过程参数数组.
260 MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
261
262 // 给存储过程参数赋值
263 AssignParameterValues(commandParameters, parameterValues);
264
265 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
266 }
267 else
268 {
269 // 没有参数情况下
270 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
271 }
272 }
273
274 /// <summary>
275 /// 执行指定数据库连接对象的命令
276 /// </summary>
277 /// <remarks>
278 /// 示例:
279 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
280 /// </remarks>
281 /// <param name="connection">一个有效的数据库连接对象</param>
282 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
283 /// <param name="commandText">存储过程名称或T-SQL语句</param>
284 /// <returns>返回影响的行数</returns>
285 public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText)
286 {
287 return ExecuteNonQuery(connection, commandType, commandText, (MySqlParameter[])null);
288 }
289
290 /// <summary>
291 /// 执行指定数据库连接对象的命令
292 /// </summary>
293 /// <remarks>
294 /// 示例:
295 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
296 /// </remarks>
297 /// <param name="connection">一个有效的数据库连接对象</param>
298 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
299 /// <param name="commandText">T存储过程名称或T-SQL语句</param>
300 /// <param name="commandParameters">SqlParamter参数数组</param>
301 /// <returns>返回影响的行数</returns>
302 public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
303 {
304 if (connection == null) throw new ArgumentNullException("connection");
305
306 // 创建MySqlCommand命令,并进行预处理
307 MySqlCommand cmd = new MySqlCommand();
308 bool mustCloseConnection = false;
309 PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
310
311 // Finally, execute the command
312 int retval = cmd.ExecuteNonQuery();
313
314 // 清除参数,以便再次使用.
315 cmd.Parameters.Clear();
316 if (mustCloseConnection)
317 connection.Close();
318 return retval;
319 }
320
321 /// <summary>
322 /// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数.
323 /// </summary>
324 /// <remarks>
325 /// 此方法不提供访问存储过程输出参数和返回值
326 /// 示例:
327 /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
328 /// </remarks>
329 /// <param name="connection">一个有效的数据库连接对象</param>
330 /// <param name="spName">存储过程名</param>
331 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
332 /// <returns>返回影响的行数</returns>
333 public static int ExecuteNonQuery(MySqlConnection connection, string spName, params object[] parameterValues)
334 {
335 if (connection == null) throw new ArgumentNullException("connection");
336 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
337
338 // 如果有参数值
339 if ((parameterValues != null) && (parameterValues.Length > 0))
340 {
341 // 从缓存中加载存储过程参数
342 MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection, spName);
343
344 // 给存储过程分配参数值
345 AssignParameterValues(commandParameters, parameterValues);
346
347 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
348 }
349 else
350 {
351 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
352 }
353 }
354
355 /// <summary>
356 /// 执行带事务的MySqlCommand.
357 /// </summary>
358 /// <remarks>
359 /// 示例.:
360 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
361 /// </remarks>
362 /// <param name="transaction">一个有效的数据库连接对象</param>
363 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
364 /// <param name="commandText">存储过程名称或T-SQL语句</param>
365 /// <returns>返回影响的行数/returns>
366 public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText)
367 {
368 return ExecuteNonQuery(transaction, commandType, commandText, (MySqlParameter[])null);
369 }
370
371 /// <summary>
372 /// 执行带事务的MySqlCommand(指定参数).
373 /// </summary>
374 /// <remarks>
375 /// 示例:
376 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24));
377 /// </remarks>
378 /// <param name="transaction">一个有效的数据库连接对象</param>
379 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
380 /// <param name="commandText">存储过程名称或T-SQL语句</param>
381 /// <param name="commandParameters">SqlParamter参数数组</param>
382 /// <returns>返回影响的行数</returns>
383 public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
384 {
385 if (transaction == null) throw new ArgumentNullException("transaction");
