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 /// <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 {
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