1.SqlHelper
1 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 /// 15 /// SqlServer数据访问帮助类 16 /// 17 public sealed class SqlHelper 18 { 19 #region 私有构造函数和方法 20 21 private SqlHelper() { } 22 23 /// 24 /// 将SqlParameter参数数组(参数值)分配给SqlCommand命令. 25 /// 这个方法将给任何一个参数分配DBNull.Value; 26 /// 该操作将阻止默认值的使用. 27 /// 28 /// 命令名 29 /// SqlParameters数组 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 /// 51 /// 将DataRow类型的列值分配到SqlParameter参数数组. 52 /// 53 /// 要分配值的SqlParameter参数数组 54 /// 将要分配给存储过程参数的DataRow 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 /// 80 /// 将一个对象数组分配给SqlParameter参数数组. 81 /// 82 /// 要分配值的SqlParameter参数数组 83 /// 将要分配给存储过程参数的对象数组 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 else109 {110 commandParameters[i].Value = paramInstance.Value;111 }112 }113 else if (parameterValues[i] == null)114 {115 commandParameters[i].Value = DBNull.Value;116 }117 else118 {119 commandParameters[i].Value = parameterValues[i];120 }121 }122 }123 124 /// 125 /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数126 /// 127 /// 要处理的SqlCommand128 /// 数据库连接129 /// 一个有效的事务或者是null值130 /// 命令类型 (存储过程,命令文本, 其它.)131 /// 存储过程名或都T-SQL命令文本132 /// 和命令相关联的SqlParameter参数数组,如果没有参数为'null'133 /// true 如果连接是打开的,则为true,其它情况下为false.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 it140 if (connection.State != ConnectionState.Open)141 {142 mustCloseConnection = true;143 connection.Open();144 }145 else146 {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 /// 178 /// 一个有效的数据库连接字符串179 /// 180 /// 202 /// 一个有效的数据库连接对象203 /// 204 /// 215 /// 执行指定连接字符串,类型的SqlCommand.216 /// 217 /// 218 /// 示例:219 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");220 /// 主要用在insert,update,delete221 /// 222 /// 一个有效的数据库连接字符串223 /// 命令类型 (存储过程,命令文本, 其它.)224 /// 存储过程名称或SQL语句225 /// 返回命令影响的行数 226 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)227 {228 return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);229 }230 231 /// 232 /// 执行指定连接字符串,类型的SqlCommand.如果没有提供参数,不返回结果.233 /// 234 /// 235 /// 示例:236 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));237 /// 238 /// 一个有效的数据库连接字符串239 /// 命令类型 (存储过程,命令文本, 其它.)240 /// 存储过程名称或SQL语句241 /// SqlParameter参数数组242 /// 返回命令影响的行数 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 /// 256 /// 执行指定连接字符串的存储过程,将对象数组的值赋给存储过程参数,257 /// 此方法需要在参数缓存方法中探索参数并生成参数.258 /// 259 /// 260 /// 这个方法没有提供访问输出参数和返回值.261 /// 示例:262 /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);263 /// 264 /// 一个有效的数据库连接字符串/param>265 /// 存储过程名称266 /// 分配到存储过程输入参数的对象数组267 /// 返回受影响的行数 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 else285 {286 // 没有参数情况下287 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);288 }289 }290 291 /// 292 /// 执行指定数据库连接对象的命令293 /// 294 /// 295 /// 示例:296 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");297 /// 298 /// 一个有效的数据库连接对象299 /// 命令类型(存储过程,命令文本或其它.)300 /// 存储过程名称或T-SQL语句301 /// 返回影响的行数 302 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)303 {304 return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);305 }306 307 /// 308 /// 执行指定数据库连接对象的命令309 /// 310 /// 311 /// 示例:312 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));313 /// 314 /// 一个有效的数据库连接对象315 /// 命令类型(存储过程,命令文本或其它.)316 /// T存储过程名称或T-SQL语句317 /// SqlParamter参数数组318 /// 返回影响的行数 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 command329 int retval = cmd.ExecuteNonQuery();330 331 // 清除参数,以便再次使用.332 cmd.Parameters.Clear();333 if (mustCloseConnection)334 connection.Close();335 return retval;336 }337 338 /// 339 /// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数.340 /// 341 /// 342 /// 此方法不提供访问存储过程输出参数和返回值343 /// 示例:344 /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);345 /// 346 /// 一个有效的数据库连接对象347 /// 存储过程名348 /// 分配给存储过程输入参数的对象数组349 /// 返回影响的行数 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 else367 {368 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);369 }370 }371 372 /// 373 /// 执行带事务的SqlCommand.374 /// 375 /// 376 /// 示例.:377 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");378 /// 379 /// 一个有效的数据库连接对象380 /// 命令类型(存储过程,命令文本或其它.)381 /// 存储过程名称或T-SQL语句382 /// 返回影响的行数/returns>383 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)384 {385 return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);386 }387 388 /// 389 /// 执行带事务的SqlCommand(指定参数).390 /// 391 /// 392 /// 示例:393 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));394 /// 395 /// 一个有效的数据库连接对象396 /// 命令类型(存储过程,命令文本或其它.)397 /// 存储过程名称或T-SQL语句398 /// SqlParamter参数数组399 /// 返回影响的行数 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 /// 419 /// 执行带事务的SqlCommand(指定参数值).420 /// 421 /// 422 /// 此方法不提供访问存储过程输出参数和返回值423 /// 示例:424 /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);425 /// 426 /// 一个有效的数据库连接对象427 /// 存储过程名428 /// 分配给存储过程输入参数的对象数组429 /// 返回受影响的行数 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 else449 {450 // 没有参数值451 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);452 }453 }454 455 #endregion ExecuteNonQuery方法结束456 457 #region ExecuteDataset方法458 459 /// 460 /// 执行指定数据库连接字符串的命令,返回DataSet.461 /// 462 /// 463 /// 示例:464 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");465 /// 466 /// 一个有效的数据库连接字符串467 /// 命令类型 (存储过程,命令文本或其它)468 /// 存储过程名称或T-SQL语句469 /// 返回一个包含结果集的DataSet 470 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)471 {472 return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);473 }474 475 /// 476 /// 执行指定数据库连接字符串的命令,返回DataSet.477 /// 478 /// 479 /// 示例:480 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));481 /// 482 /// 一个有效的数据库连接字符串483 /// 命令类型 (存储过程,命令文本或其它)484 /// 存储过程名称或T-SQL语句485 /// SqlParamters参数数组486 /// 返回一个包含结果集的DataSet 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 /// 502 /// 执行指定数据库连接字符串的命令,直接提供参数值,返回DataSet.503 /// 504 /// 505 /// 此方法不提供访问存储过程输出参数和返回值.506 /// 示例:507 /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);508 /// 509 /// 一个有效的数据库连接字符串510 /// 存储过程名511 /// 分配给存储过程输入参数的对象数组512 /// 返回一个包含结果集的DataSet 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 else529 {530 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);531 }532 }533 534 /// 535 /// 执行指定数据库连接对象的命令,返回DataSet.536 /// 537 /// 538 /// 示例:539 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");540 /// 541 /// 一个有效的数据库连接对象542 /// 命令类型 (存储过程,命令文本或其它)543 /// 存储过程名或T-SQL语句544 /// 返回一个包含结果集的DataSet 545 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)546 {547 return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);548 }549 550 /// 551 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.552 /// 553 /// 554 /// 示例:555 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));556 /// 557 /// 一个有效的数据库连接对象558 /// 命令类型 (存储过程,命令文本或其它)559 /// 存储过程名或T-SQL语句560 /// SqlParamter参数数组561 /// 返回一个包含结果集的DataSet 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 /// 589 /// 执行指定数据库连接对象的命令,指定参数值,返回DataSet.590 /// 591 /// 592 /// 此方法不提供访问存储过程输入参数和返回值.593 /// 示例.:594 /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);595 /// 596 /// 一个有效的数据库连接对象597 /// 存储过程名598 /// 分配给存储过程输入参数的对象数组599 /// 返回一个包含结果集的DataSet 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 else616 {617 return ExecuteDataset(connection, CommandType.StoredProcedure, spName);618 }619 }620 621 /// 622 /// 执行指定事务的命令,返回DataSet.623 /// 624 /// 625 /// 示例:626 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");627 /// 628 /// 事务629 /// 命令类型 (存储过程,命令文本或其它)630 /// 存储过程名或T-SQL语句631 /// 返回一个包含结果集的DataSet 632 public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)633 {634 return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);635 }636 637 /// 638 /// 执行指定事务的命令,指定参数,返回DataSet.639 /// 640 /// 641 /// 示例:642 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));643 /// 644 /// 事务645 /// 命令类型 (存储过程,命令文本或其它)646 /// 存储过程名或T-SQL语句647 /// SqlParamter参数数组648 /// 返回一个包含结果集的DataSet 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 & DataSet660 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 /// 670 /// 执行指定事务的命令,指定参数值,返回DataSet.671 /// 672 /// 673 /// 此方法不提供访问存储过程输入参数和返回值.674 /// 示例.:675 /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);676 /// 677 /// 事务678 /// 存储过程名679 /// 分配给存储过程输入参数的对象数组680 /// 返回一个包含结果集的DataSet 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 else698 {699 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);700 }701 }702 703 #endregion ExecuteDataset数据集命令结束704 705 #region ExecuteReader 数据阅读器706 707 /// 708 /// 枚举,标识数据库连接是由SqlHelper提供还是由调用者提供709 /// 710 private enum SqlConnectionOwnership711 {712 /// 由SqlHelper提供连接 713 Internal,714 /// 由调用者提供连接 715 External716 }717 718 /// 719 /// 执行指定数据库连接对象的数据阅读器.720 /// 721 /// 722 /// 如果是SqlHelper打开连接,当连接关闭DataReader也将关闭.723 /// 如果是调用都打开连接,DataReader由调用都管理.724 /// 725 /// 一个有效的数据库连接对象726 /// 一个有效的事务,或者为 'null'727 /// 命令类型 (存储过程,命令文本或其它)728 /// 存储过程名或T-SQL语句729 /// SqlParameters参数数组,如果没有参数则为'null'730 /// 标识数据库连接对象是由调用者提供还是由SqlHelper提供731 /// 返回包含结果集的SqlDataReader 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 try740 {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 else751 {752 dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);753 }754 755 // 清除参数,以便再次使用..756 // HACK: There is a problem here, the output parameter values are fletched757 // when the reader is closed, so if the parameters are detached from the command758 // 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 { 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