开发常用工具类-持续更新中 1.SqlHelper 9.SerializerHelper
using System;
2 using System.Data;
3 using System.Xml;
4 using System.Data.SqlClient;
5 using System.Collections;
6 using System.Configuration;
7 using System.Collections.Specialized;
8
9 /********************************************|
10 * SQLHelper数据访问工具类 |
11 ********************************************/
12 namespace Demo1
13 {
14 /// <summary>
15 /// SqlServer数据访问帮助类
16 ///</summary>
17 public sealed class SqlHelper
18 {
19 #region 私有构造函数和方法
20
21 private SqlHelper() { }
22
23 /// <summary>
24 /// 将SqlParameter参数数组(参数值)分配给SqlCommand命令.
25 /// 这个方法将给任何一个参数分配DBNull.Value;
26 /// 该操作将阻止默认值的使用.
27 ///</summary>
28 /// <param name="command">命令名</param>
29 /// <param name="commandParameters">SqlParameters数组</param>
30 private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
31 {
32 if (command == null) throw new ArgumentNullException("command");
33 if (commandParameters != null)
34 {
35 foreach (SqlParameter p in commandParameters)
36 {
37 if (p != null)
38 {
39 // 检查未分配值的输出参数,将其分配以DBNull.Value.
40 if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
41 {
42 p.Value = DBNull.Value;
43 }
44 command.Parameters.Add(p);
45 }
46 }
47 }
48 }
49
50 /// <summary>
51 /// 将DataRow类型的列值分配到SqlParameter参数数组.
52 ///</summary>
53 /// <param name="commandParameters">要分配值的SqlParameter参数数组</param>
54 /// <param name="dataRow">将要分配给存储过程参数的DataRow</param>
55 private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
56 {
57 if ((commandParameters == null) || (dataRow == null))
58 {
59 return;
60 }
61
62 int i = 0;
63 // 设置参数值
64 foreach (SqlParameter 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 /// 将一个对象数组分配给SqlParameter参数数组.
81 ///</summary>
82 /// <param name="commandParameters">要分配值的SqlParameter参数数组</param>
83 /// <param name="parameterValues">将要分配给存储过程参数的对象数组</param>
84 private static void AssignParameterValues(SqlParameter[] 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">要处理的SqlCommand</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">和命令相关联的SqlParameter参数数组,如果没有参数为'null'</param>
133 /// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
134 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] 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 string ab = string.Empty;
184
185 return ConfigurationManager.AppSettings["ConnStr"];
186
187
188 //NameValueCollection nodeName = (NameValueCollection)ConfigurationManager.GetSection("databaseSettings");
189 //if (nodeName["connectionString"] == null || nodeName["connectionString"] == "")
190 //{
191 // return string.Empty;
192 //}
193 //else
194 //{
195 // return nodeName["connectionString"];
196 //}
197
198 //return "server=.,2008;database=gms;uid=sa;pwd=123456;";
199 //return ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
200 }
201 /// <summary>
202 /// 一个有效的数据库连接对象
203 ///</summary>
204 /// <returns></returns>
205 public static SqlConnection GetConnection()
206 {
207 SqlConnection Connection = new SqlConnection(SqlHelper.GetConnSting());
208 return Connection;
209 }
210 #endregion
211
212 #region ExecuteNonQuery命令
213
214 /// <summary>
215 /// 执行指定连接字符串,类型的SqlCommand.
216 ///</summary>
217 /// <remarks>
218 /// 示例:
219 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
220 /// 主要用在insert,update,delete
221 ///</remarks>
222 /// <param name="connectionString">一个有效的数据库连接字符串</param>
223 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
224 /// <param name="commandText">存储过程名称或SQL语句</param>
225 /// <returns>返回命令影响的行数</returns>
226 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
227 {
228 return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
229 }
230
231 /// <summary>
232 /// 执行指定连接字符串,类型的SqlCommand.如果没有提供参数,不返回结果.
233 ///</summary>
234 /// <remarks>
235 /// 示例:
236 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
237 ///</remarks>
238 /// <param name="connectionString">一个有效的数据库连接字符串</param>
239 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
240 /// <param name="commandText">存储过程名称或SQL语句</param>
241 /// <param name="commandParameters">SqlParameter参数数组</param>
242 /// <returns>返回命令影响的行数</returns>
243 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
244 {
245 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
246
247 using (SqlConnection connection = new SqlConnection(connectionString))
248 {
249 connection.Open();
250
251 return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
252 }
253 }
254
255 /// <summary>
256 /// 执行指定连接字符串的存储过程,将对象数组的值赋给存储过程参数,
257 /// 此方法需要在参数缓存方法中探索参数并生成参数.
258 ///</summary>
259 /// <remarks>
260 /// 这个方法没有提供访问输出参数和返回值.
261 /// 示例:
262 /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
263 ///</remarks>
264 /// <param name="connectionString">一个有效的数据库连接字符串/param>
265 /// <param name="spName">存储过程名称</param>
266 /// <param name="parameterValues">分配到存储过程输入参数的对象数组</param>
267 /// <returns>返回受影响的行数</returns>
268 public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
269 {
270 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
271 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
272
273 // 如果存在参数值
274 if ((parameterValues != null) && (parameterValues.Length > 0))
275 {
276 // 从探索存储过程参数(加载到缓存)并分配给存储过程参数数组.
277 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
278
279 // 给存储过程参数赋值
280 AssignParameterValues(commandParameters, parameterValues);
281
282 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
283 }
284 else
285 {
286 // 没有参数情况下
287 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
288 }
289 }
290
291 /// <summary>
292 /// 执行指定数据库连接对象的命令
293 ///</summary>
294 /// <remarks>
295 /// 示例:
296 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
297 ///</remarks>
298 /// <param name="connection">一个有效的数据库连接对象</param>
299 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
300 /// <param name="commandText">存储过程名称或T-SQL语句</param>
301 /// <returns>返回影响的行数</returns>
302 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
303 {
304 return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
305 }
306
307 /// <summary>
308 /// 执行指定数据库连接对象的命令
309 ///</summary>
310 /// <remarks>
311 /// 示例:
312 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
313 ///</remarks>
314 /// <param name="connection">一个有效的数据库连接对象</param>
315 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
316 /// <param name="commandText">T存储过程名称或T-SQL语句</param>
317 /// <param name="commandParameters">SqlParamter参数数组</param>
318 /// <returns>返回影响的行数</returns>
319 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
320 {
321 if (connection == null) throw new ArgumentNullException("connection");
322
323 // 创建SqlCommand命令,并进行预处理
324 SqlCommand cmd = new SqlCommand();
325 bool mustCloseConnection = false;
326 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
327
328 // Finally, execute the command
329 int retval = cmd.ExecuteNonQuery();
330
331 // 清除参数,以便再次使用.
332 cmd.Parameters.Clear();
333 if (mustCloseConnection)
334 connection.Close();
335 return retval;
336 }
337
338 /// <summary>
339 /// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数.
340 ///</summary>
341 /// <remarks>
342 /// 此方法不提供访问存储过程输出参数和返回值
343 /// 示例:
344 /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
345 ///</remarks>
346 /// <param name="connection">一个有效的数据库连接对象</param>
347 /// <param name="spName">存储过程名</param>
348 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
349 /// <returns>返回影响的行数</returns>
350 public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
351 {
352 if (connection == null) throw new ArgumentNullException("connection");
353 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
354
355 // 如果有参数值
356 if ((parameterValues != null) && (parameterValues.Length > 0))
357 {
358 // 从缓存中加载存储过程参数
359 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
360
361 // 给存储过程分配参数值
362 AssignParameterValues(commandParameters, parameterValues);
363
364 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
365 }
366 else
367 {
368 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
369 }
370 }
371
372 /// <summary>
373 /// 执行带事务的SqlCommand.
374 ///</summary>
375 /// <remarks>
376 /// 示例.:
377 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
378 ///</remarks>
379 /// <param name="transaction">一个有效的数据库连接对象</param>
380 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
381 /// <param name="commandText">存储过程名称或T-SQL语句</param>
382 /// <returns>返回影响的行数/returns>
383 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
384 {
385 return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
386 }
387
388 /// <summary>
389 /// 执行带事务的SqlCommand(指定参数).
390 ///</summary>
391 /// <remarks>
392 /// 示例:
393 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
394 ///</remarks>
395 /// <param name="transaction">一个有效的数据库连接对象</param>
396 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
397 /// <param name="commandText">存储过程名称或T-SQL语句</param>
398 /// <param name="commandParameters">SqlParamter参数数组</param>
399 /// <returns>返回影响的行数</returns>
400 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
401 {
402 if (transaction == null) throw new ArgumentNullException("transaction");
403 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
404
405 // 预处理
406 SqlCommand cmd = new SqlCommand();
407 bool mustCloseConnection = false;
408 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
409
410 // 执行
411 int retval = cmd.ExecuteNonQuery();
412
413 // 清除参数集,以便再次使用.
414 cmd.Parameters.Clear();
415 return retval;
416 }
417
418 /// <summary>
419 /// 执行带事务的SqlCommand(指定参数值).
420 ///</summary>
421 /// <remarks>
422 /// 此方法不提供访问存储过程输出参数和返回值
423 /// 示例:
424 /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
425 ///</remarks>
426 /// <param name="transaction">一个有效的数据库连接对象</param>
427 /// <param name="spName">存储过程名</param>
428 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
429 /// <returns>返回受影响的行数</returns>
430 public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
431 {
432 if (transaction == null) throw new ArgumentNullException("transaction");
433 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
434 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
435
436 // 如果有参数值
437 if ((parameterValues != null) && (parameterValues.Length > 0))
438 {
439 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
440 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
441
442 // 给存储过程参数赋值
443 AssignParameterValues(commandParameters, parameterValues);
444
445 // 调用重载方法
446 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
447 }
448 else
449 {
450 // 没有参数值
451 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
452 }
453 }
454
455 #endregion ExecuteNonQuery方法结束
456
457 #region ExecuteDataset方法
458
459 /// <summary>
460 /// 执行指定数据库连接字符串的命令,返回DataSet.
461 ///</summary>
462 /// <remarks>
463 /// 示例:
464 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
465 ///</remarks>
466 /// <param name="connectionString">一个有效的数据库连接字符串</param>
467 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
468 /// <param name="commandText">存储过程名称或T-SQL语句</param>
469 /// <returns>返回一个包含结果集的DataSet</returns>
470 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
471 {
472 return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
473 }
474
475 /// <summary>
476 /// 执行指定数据库连接字符串的命令,返回DataSet.
477 ///</summary>
478 /// <remarks>
479 /// 示例:
480 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
481 ///</remarks>
482 /// <param name="connectionString">一个有效的数据库连接字符串</param>
483 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
484 /// <param name="commandText">存储过程名称或T-SQL语句</param>
485 /// <param name="commandParameters">SqlParamters参数数组</param>
486 /// <returns>返回一个包含结果集的DataSet</returns>
487 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
488 {
489 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
490
491 // 创建并打开数据库连接对象,操作完成释放对象.
492 using (SqlConnection connection = new SqlConnection(connectionString))
493 {
494 connection.Open();
495
496 // 调用指定数据库连接字符串重载方法.
497 return ExecuteDataset(connection, commandType, commandText, commandParameters);
498 }
499 }
500
501 /// <summary>
502 /// 执行指定数据库连接字符串的命令,直接提供参数值,返回DataSet.
503 ///</summary>
504 /// <remarks>
505 /// 此方法不提供访问存储过程输出参数和返回值.
506 /// 示例:
507 /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
508 ///</remarks>
509 /// <param name="connectionString">一个有效的数据库连接字符串</param>
510 /// <param name="spName">存储过程名</param>
511 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
512 /// <returns>返回一个包含结果集的DataSet</returns>
513 public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
514 {
515 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
516 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
517
518 if ((parameterValues != null) && (parameterValues.Length > 0))
519 {
520 // 从缓存中检索存储过程参数
521 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
522
523 // 给存储过程参数分配值
524 AssignParameterValues(commandParameters, parameterValues);
525
526 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
527 }
528 else
529 {
530 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
531 }
532 }
533
534 /// <summary>
535 /// 执行指定数据库连接对象的命令,返回DataSet.
536 ///</summary>
537 /// <remarks>
538 /// 示例:
539 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
540 ///</remarks>
541 /// <param name="connection">一个有效的数据库连接对象</param>
542 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
543 /// <param name="commandText">存储过程名或T-SQL语句</param>
544 /// <returns>返回一个包含结果集的DataSet</returns>
545 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
546 {
547 return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
548 }
549
550 /// <summary>
551 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
552 ///</summary>
553 /// <remarks>
554 /// 示例:
555 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
556 ///</remarks>
557 /// <param name="connection">一个有效的数据库连接对象</param>
558 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
559 /// <param name="commandText">存储过程名或T-SQL语句</param>
560 /// <param name="commandParameters">SqlParamter参数数组</param>
561 /// <returns>返回一个包含结果集的DataSet</returns>
562 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
563 {
564 if (connection == null) throw new ArgumentNullException("connection");
565
566 // 预处理
567 SqlCommand cmd = new SqlCommand();
568 bool mustCloseConnection = false;
569 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
570
571 // 创建SqlDataAdapter和DataSet.
572 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
573 {
574 DataSet ds = new DataSet();
575
576 // 填充DataSet.
577 da.Fill(ds);
578
579 cmd.Parameters.Clear();
580
581 if (mustCloseConnection)
582 connection.Close();
583
584 return ds;
585 }
586 }
587
588 /// <summary>
589 /// 执行指定数据库连接对象的命令,指定参数值,返回DataSet.
590 ///</summary>
591 /// <remarks>
592 /// 此方法不提供访问存储过程输入参数和返回值.
593 /// 示例.:
594 /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
595 ///</remarks>
596 /// <param name="connection">一个有效的数据库连接对象</param>
597 /// <param name="spName">存储过程名</param>
598 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
599 /// <returns>返回一个包含结果集的DataSet</returns>
600 public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
601 {
602 if (connection == null) throw new ArgumentNullException("connection");
603 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
604
605 if ((parameterValues != null) && (parameterValues.Length > 0))
606 {
607 // 比缓存中加载存储过程参数
608 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
609
610 // 给存储过程参数分配值
611 AssignParameterValues(commandParameters, parameterValues);
612
613 return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
614 }
615 else
616 {
617 return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
618 }
619 }
620
621 /// <summary>
622 /// 执行指定事务的命令,返回DataSet.
623 ///</summary>
624 /// <remarks>
625 /// 示例:
626 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
627 ///</remarks>
628 /// <param name="transaction">事务</param>
629 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
630 /// <param name="commandText">存储过程名或T-SQL语句</param>
631 /// <returns>返回一个包含结果集的DataSet</returns>
632 public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
633 {
634 return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
635 }
636
637 /// <summary>
638 /// 执行指定事务的命令,指定参数,返回DataSet.
639 ///</summary>
640 /// <remarks>
641 /// 示例:
642 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
643 ///</remarks>
644 /// <param name="transaction">事务</param>
645 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
646 /// <param name="commandText">存储过程名或T-SQL语句</param>
647 /// <param name="commandParameters">SqlParamter参数数组</param>
648 /// <returns>返回一个包含结果集的DataSet</returns>
649 public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
650 {
651 if (transaction == null) throw new ArgumentNullException("transaction");
652 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
653
654 // 预处理
655 SqlCommand cmd = new SqlCommand();
656 bool mustCloseConnection = false;
657 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
658
659 // 创建 DataAdapter & DataSet
660 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
661 {
662 DataSet ds = new DataSet();
663 da.Fill(ds);
664 cmd.Parameters.Clear();
665 return ds;
666 }
667 }
668
669 /// <summary>
670 /// 执行指定事务的命令,指定参数值,返回DataSet.
671 ///</summary>
672 /// <remarks>
673 /// 此方法不提供访问存储过程输入参数和返回值.
674 /// 示例.:
675 /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
676 ///</remarks>
677 /// <param name="transaction">事务</param>
678 /// <param name="spName">存储过程名</param>
679 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
680 /// <returns>返回一个包含结果集的DataSet</returns>
681 public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
682 {
683 if (transaction == null) throw new ArgumentNullException("transaction");
684 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
685 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
686
687 if ((parameterValues != null) && (parameterValues.Length > 0))
688 {
689 // 从缓存中加载存储过程参数
690 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
691
692 // 给存储过程参数分配值
693 AssignParameterValues(commandParameters, parameterValues);
694
695 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
696 }
697 else
698 {
699 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
700 }
701 }
702
703 #endregion ExecuteDataset数据集命令结束
704
705 #region ExecuteReader 数据阅读器
706
707 /// <summary>
708 /// 枚举,标识数据库连接是由SqlHelper提供还是由调用者提供
709 ///</summary>
710 private enum SqlConnectionOwnership
711 {
712 /// <summary>由SqlHelper提供连接</summary>
713 Internal,
714 /// <summary>由调用者提供连接</summary>
715 External
716 }
717
718 /// <summary>
719 /// 执行指定数据库连接对象的数据阅读器.
720 ///</summary>
721 /// <remarks>
722 /// 如果是SqlHelper打开连接,当连接关闭DataReader也将关闭.
723 /// 如果是调用都打开连接,DataReader由调用都管理.
724 ///</remarks>
725 /// <param name="connection">一个有效的数据库连接对象</param>
726 /// <param name="transaction">一个有效的事务,或者为 'null'</param>
727 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
728 /// <param name="commandText">存储过程名或T-SQL语句</param>
729 /// <param name="commandParameters">SqlParameters参数数组,如果没有参数则为'null'</param>
730 /// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由SqlHelper提供</param>
731 /// <returns>返回包含结果集的SqlDataReader</returns>
732 private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
733 {
734 if (connection == null) throw new ArgumentNullException("connection");
735
736 bool mustCloseConnection = false;
737 // 创建命令
738 SqlCommand cmd = new SqlCommand();
739 try
740 {
741 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
742
743 // 创建数据阅读器
744 SqlDataReader dataReader;
745
746 if (connectionOwnership == SqlConnectionOwnership.External)
747 {
748 dataReader = cmd.ExecuteReader();
749 }
750 else
751 {
752 dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
753 }
754
755 // 清除参数,以便再次使用..
756 // HACK: There is a problem here, the output parameter values are fletched
757 // when the reader is closed, so if the parameters are detached from the command
758 // then the SqlReader can磘 set its values.
759 // When this happen, the parameters can磘 be used again in other command.
760 bool canClear = true;
761 foreach (SqlParameter commandParameter in cmd.Parameters)
762 {
763 if (commandParameter.Direction != ParameterDirection.Input)
764 canClear = false;
765 }
766
767 if (canClear)
768 {
SQLHelper
2.HtmlHelper
3.ConfigHelper
4.StringHelper
5.RegexHelper
6.IpHelper
7.MailHelper
8.YZMHelper
9.SerializerHelper
1 using System; 2 using System.Collections.Generic; 3 using System.IO; 4 using System.Linq; 5 using System.Text; 6 using System.Web; 7 8 namespace Demo1 9 { 10 public static class SerialHelper 11 { 12 public static string ObjToXmlStr(Type type, object obj) 13 { 14 if (obj != null) 15 { 16 type = type != null ? type : obj.GetType(); 17 18 using (MemoryStream stream = new MemoryStream()) 19 { 20 System.Xml.Serialization.XmlSerializer xmlSerializer = new System.Xml.Serialization.XmlSerializer(type); 21 xmlSerializer.Serialize(stream, obj); 22 23 using (StreamReader reader = new StreamReader(stream)) 24 { 25 stream.Position = 0; 26 string str = reader.ReadToEnd(); 27 return str; 28 } 29 } 30 } 31 else 32 { 33 return null; 34 } 35 } 36 public static object XmlStrToObj(Type type, string xml) 37 { 38 byte[] buffer = Encoding.UTF8.GetBytes(xml); 39 using (MemoryStream stream = new MemoryStream(buffer)) 40 { 41 using (StreamReader reader = new StreamReader(stream)) 42 { 43 System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(type); 44 return serializer.Deserialize(reader); 45 } 46 } 47 } 48 } 49 }
10.获取MD5
1 private static string GetMD5(string str) 2 { 3 MD5 md5 = MD5.Create(); 4 byte[] buffer = Encoding.Default.GetBytes(str); 5 byte[] newBuffer = md5.ComputeHash(buffer); 6 string strNew = ""; 7 //每一个字符单独转换成16进制字符串 8 for (int i = 0; i < newBuffer.Length; i++) 9 { 10 strNew += newBuffer[i].ToString("x2"); 11 } 12 return strNew; 13 } 14