微软SqlHelper<带中文注释>
1 /* 2 * author: Devil 3 * 4 * 5 * created date : 7/11/2012 5:00:31 PM 6 * */ 7 using System; 8 using System.Data; 9 using System.Xml; 10 using System.Data.SqlClient; 11 using System.Collections; 12 using System.Configuration; 13 14 15 /// <summary> 16 /// SqlServer数据访问帮助类 Devil 17 ///如果要获取连接数据连接对象或字符串的话,先要修改SQLHelper类中GetConnSting() 方法中的ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;才能调用。 18 /// </summary> 19 public sealed class SqlHelper 20 { 21 #region 私有构造函数和方法 22 23 private SqlHelper() { } 24 25 /// <summary> 26 /// 将SqlParameter参数数组(参数值)分配给SqlCommand命令. 27 /// 这个方法将给任何一个参数分配DBNull.Value; 28 /// 该操作将阻止默认值的使用. 29 /// </summary> 30 /// <param name="command">命令名</param> 31 /// <param name="commandParameters">SqlParameters数组</param> 32 private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) 33 { 34 if (command == null) throw new ArgumentNullException("command"); 35 if (commandParameters != null) 36 { 37 foreach (SqlParameter p in commandParameters) 38 { 39 if (p != null) 40 { 41 // 检查未分配值的输出参数,将其分配以DBNull.Value. 42 if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && 43 (p.Value == null)) 44 { 45 p.Value = DBNull.Value; 46 } 47 command.Parameters.Add(p); 48 } 49 } 50 } 51 } 52 53 /// <summary> 54 /// 将DataRow类型的列值分配到SqlParameter参数数组. 55 /// </summary> 56 /// <param name="commandParameters">要分配值的SqlParameter参数数组</param> 57 /// <param name="dataRow">将要分配给存储过程参数的DataRow</param> 58 private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow) 59 { 60 if ((commandParameters == null) || (dataRow == null)) 61 { 62 return; 63 } 64 65 int i = 0; 66 // 设置参数值 67 foreach (SqlParameter commandParameter in commandParameters) 68 { 69 // 创建参数名称,如果不存在,只抛出一个异常. 70 if (commandParameter.ParameterName == null || 71 commandParameter.ParameterName.Length <= 1) 72 throw new Exception( 73 string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName)); 74 // 从dataRow的表中获取为参数数组中数组名称的列的索引. 75 // 如果存在和参数名称相同的列,则将列值赋给当前名称的参数. 76 if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1) 77 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)]; 78 i++; 79 } 80 } 81 82 /// <summary> 83 /// 将一个对象数组分配给SqlParameter参数数组. 84 /// </summary> 85 /// <param name="commandParameters">要分配值的SqlParameter参数数组</param> 86 /// <param name="parameterValues">将要分配给存储过程参数的对象数组</param> 87 private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) 88 { 89 if ((commandParameters == null) || (parameterValues == null)) 90 { 91 return; 92 } 93 94 // 确保对象数组个数与参数个数匹配,如果不匹配,抛出一个异常. 95 if (commandParameters.Length != parameterValues.Length) 96 { 97 throw new ArgumentException("参数值个数与参数不匹配."); 98 } 99 100 // 给参数赋值 101 for (int i = 0, j = commandParameters.Length; i < j; i++) 102 { 103 // If the current array value derives from IDbDataParameter, then assign its Value property 104 if (parameterValues[i] is IDbDataParameter) 105 { 106 IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i]; 107 if (paramInstance.Value == null) 108 { 109 commandParameters[i].Value = DBNull.Value; 110 } 111 else 112 { 113 commandParameters[i].Value = paramInstance.Value; 114 } 115 } 116 else if (parameterValues[i] == null) 117 { 118 commandParameters[i].Value = DBNull.Value; 119 } 120 else 121 { 122 commandParameters[i].Value = parameterValues[i]; 123 } 124 } 125 } 126 127 /// <summary> 128 /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数 129 /// </summary> 130 /// <param name="command">要处理的SqlCommand</param> 131 /// <param name="connection">数据库连接</param> 132 /// <param name="transaction">一个有效的事务或者是null值</param> 133 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param> 134 /// <param name="commandText">存储过程名或都T-SQL命令文本</param> 135 /// <param name="commandParameters">和命令相关联的SqlParameter参数数组,如果没有参数为'null'</param> 136 /// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param> 137 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection) 138 { 139 if (command == null) throw new ArgumentNullException("command"); 140 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); 141 142 // If the provided connection is not open, we will open it 143 if (connection.State != ConnectionState.Open) 144 { 145 mustCloseConnection = true; 146 connection.Open(); 147 } 148 else 149 { 150 mustCloseConnection = false; 151 } 152 153 // 给命令分配一个数据库连接. 154 command.Connection = connection; 155 156 // 设置命令文本(存储过程名或SQL语句) 157 command.CommandText = commandText; 158 159 // 分配事务 160 if (transaction != null) 161 { 162 if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 163 command.Transaction = transaction; 164 } 165 166 // 设置命令类型. 167 command.CommandType = commandType; 168 169 // 分配命令参数 170 if (commandParameters != null) 171 { 172 AttachParameters(command, commandParameters); 173 } 174 return; 175 } 176 177 #endregion 私有构造函数和方法结束 178 179 #region 数据库连接 180 /// <summary> 181 /// 一个有效的数据库连接字符串 182 /// </summary> 183 /// <returns></returns> 184 public static string GetConnSting() 185 { 186 return ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString; 187 } 188 /// <summary> 189 /// 一个有效的数据库连接对象 190 /// </summary> 191 /// <returns></returns> 192 public static SqlConnection GetConnection() 193 { 194 SqlConnection Connection = new SqlConnection(SqlHelper.GetConnSting()); 195 return Connection; 196 } 197 #endregion 198 199 #region ExecuteNonQuery命令 200 201 /// <summary> 202 /// 执行指定连接字符串,类型的SqlCommand. 203 /// </summary> 204 /// <remarks> 205 /// 示例: 206 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); 207 /// </remarks> 208 /// <param name="connectionString">一个有效的数据库连接字符串</param> 209 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param> 210 /// <param name="commandText">存储过程名称或SQL语句</param> 211 /// <returns>返回命令影响的行数</returns> 212 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) 213 { 214 return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); 215 } 216 217 /// <summary> 218 /// 执行指定连接字符串,类型的SqlCommand.如果没有提供参数,不返回结果. 219 /// </summary> 220 /// <remarks> 221 /// 示例: 222 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 223 /// </remarks> 224 /// <param name="connectionString">一个有效的数据库连接字符串</param> 225 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param> 226 /// <param name="commandText">存储过程名称或SQL语句</param> 227 /// <param name="commandParameters">SqlParameter参数数组</param> 228 /// <returns>返回命令影响的行数</returns> 229 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 230 { 231 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 232 233 using (SqlConnection connection = new SqlConnection(connectionString)) 234 { 235 connection.Open(); 236 237 return ExecuteNonQuery(connection, commandType, commandText, commandParameters); 238 } 239 } 240 241 /// <summary> 242 /// 执行指定连接字符串的存储过程,将对象数组的值赋给存储过程参数, 243 /// 此方法需要在参数缓存方法中探索参数并生成参数. 244 /// </summary> 245 /// <remarks> 246 /// 这个方法没有提供访问输出参数和返回值. 247 /// 示例: 248 /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36); 249 /// </remarks> 250 /// <param name="connectionString">一个有效的数据库连接字符串/param> 251 /// <param name="spName">存储过程名称</param> 252 /// <param name="parameterValues">分配到存储过程输入参数的对象数组</param> 253 /// <returns>返回受影响的行数</returns> 254 public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) 255 { 256 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 257 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 258 259 // 如果存在参数值 260 if ((parameterValues != null) && (parameterValues.Length > 0)) 261 { 262 // 从探索存储过程参数(加载到缓存)并分配给存储过程参数数组. 263 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 264 265 // 给存储过程参数赋值 266 AssignParameterValues(commandParameters, parameterValues); 267 268 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); 269 } 270 else 271 { 272 // 没有参数情况下 273 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); 274 } 275 } 276 277 /// <summary> 278 /// 执行指定数据库连接对象的命令 279 /// </summary> 280 /// <remarks> 281 /// 示例: 282 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders"); 283 /// </remarks> 284 /// <param name="connection">一个有效的数据库连接对象</param> 285 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param> 286 /// <param name="commandText">存储过程名称或T-SQL语句</param> 287 /// <returns>返回影响的行数</returns> 288 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) 289 { 290 return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null); 291 } 292 293 /// <summary> 294 /// 执行指定数据库连接对象的命令 295 /// </summary> 296 /// <remarks> 297 /// 示例: 298 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 299 /// </remarks> 300 /// <param name="connection">一个有效的数据库连接对象</param> 301 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param> 302 /// <param name="commandText">T存储过程名称或T-SQL语句</param> 303 /// <param name="commandParameters">SqlParamter参数数组</param> 304 /// <returns>返回影响的行数</returns> 305 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 306 { 307 if (connection == null) throw new ArgumentNullException("connection"); 308 309 // 创建SqlCommand命令,并进行预处理 310 SqlCommand cmd = new SqlCommand(); 311 bool mustCloseConnection = false; 312 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); 313 314 // Finally, execute the command 315 int retval = cmd.ExecuteNonQuery(); 316 317 // 清除参数,以便再次使用. 318 cmd.Parameters.Clear(); 319 if (mustCloseConnection) 320 connection.Close(); 321 return retval; 322 } 323 324 /// <summary> 325 /// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数. 326 /// </summary> 327 /// <remarks> 328 /// 此方法不提供访问存储过程输出参数和返回值 329 /// 示例: 330 /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36); 331 /// </remarks> 332 /// <param name="connection">一个有效的数据库连接对象</param> 333 /// <param name="spName">存储过程名</param> 334 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 335 /// <returns>返回影响的行数</returns> 336 public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues) 337 { 338 if (connection == null) throw new ArgumentNullException("connection"); 339 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 340 341 // 如果有参数值 342 if ((parameterValues != null) && (parameterValues.Length > 0)) 343 { 344 // 从缓存中加载存储过程参数 345 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 346 347 // 给存储过程分配参数值 348 AssignParameterValues(commandParameters, parameterValues); 349 350 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); 351 } 352 else 353 { 354 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); 355 } 356 } 357 358 /// <summary> 359 /// 执行带事务的SqlCommand. 360 /// </summary> 361 /// <remarks> 362 /// 示例.: 363 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); 364 /// </remarks> 365 /// <param name="transaction">一个有效的数据库连接对象</param> 366 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param> 367 /// <param name="commandText">存储过程名称或T-SQL语句</param> 368 /// <returns>返回影响的行数/returns> 369 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText) 370 { 371 return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null); 372 } 373 374 /// <summary> 375 /// 执行带事务的SqlCommand(指定参数). 376 /// </summary> 377 /// <remarks> 378 /// 示例: 379 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 380 /// </remarks> 381 /// <param name="transaction">一个有效的数据库连接对象</param> 382 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param> 383 /// <param name="commandText">存储过程名称或T-SQL语句</param> 384 /// <param name="commandParameters">SqlParamter参数数组</param> 385 /// <returns>返回影响的行数</returns> 386 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 387 { 388 if (transaction == null) throw new ArgumentNullException("transaction"); 389 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 390 391 // 预处理 392 SqlCommand cmd = new SqlCommand(); 393 bool mustCloseConnection = false; 394 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 395 396 // 执行 397 int retval = cmd.ExecuteNonQuery(); 398 399 // 清除参数集,以便再次使用. 400 cmd.Parameters.Clear(); 401 return retval; 402 } 403 404 /// <summary> 405 /// 执行带事务的SqlCommand(指定参数值). 406 /// </summary> 407 /// <remarks> 408 /// 此方法不提供访问存储过程输出参数和返回值 409 /// 示例: 410 /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36); 411 /// </remarks> 412 /// <param name="transaction">一个有效的数据库连接对象</param> 413 /// <param name="spName">存储过程名</param> 414 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 415 /// <returns>返回受影响的行数</returns> 416 public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues) 417 { 418 if (transaction == null) throw new ArgumentNullException("transaction"); 419 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 420 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 421 422 // 如果有参数值 423 if ((parameterValues != null) && (parameterValues.Length > 0)) 424 { 425 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 426 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 427 428 // 给存储过程参数赋值 429 AssignParameterValues(commandParameters, parameterValues); 430 431 // 调用重载方法 432 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); 433 } 434 else 435 { 436 // 没有参数值 437 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); 438 } 439 } 440 441 #endregion ExecuteNonQuery方法结束 442 443 #region ExecuteDataset方法 444 445 /// <summary> 446 /// 执行指定数据库连接字符串的命令,返回DataSet. 447 /// </summary> 448 /// <remarks> 449 /// 示例: 450 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders"); 451 /// </remarks> 452 /// <param name="connectionString">一个有效的数据库连接字符串</param> 453 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 454 /// <param name="commandText">存储过程名称或T-SQL语句</param> 455 /// <returns>返回一个包含结果集的DataSet</returns> 456 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) 457 { 458 return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null); 459 } 460 461 /// <summary> 462 /// 执行指定数据库连接字符串的命令,返回DataSet. 463 /// </summary> 464 /// <remarks> 465 /// 示例: 466 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 467 /// </remarks> 468 /// <param name="connectionString">一个有效的数据库连接字符串</param> 469 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 470 /// <param name="commandText">存储过程名称或T-SQL语句</param> 471 /// <param name="commandParameters">SqlParamters参数数组</param> 472 /// <returns>返回一个包含结果集的DataSet</returns> 473 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 474 { 475 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 476 477 // 创建并打开数据库连接对象,操作完成释放对象. 478 using (SqlConnection connection = new SqlConnection(connectionString)) 479 { 480 connection.Open(); 481 482 // 调用指定数据库连接字符串重载方法. 483 return ExecuteDataset(connection, commandType, commandText, commandParameters); 484 } 485 } 486 487 /// <summary> 488 /// 执行指定数据库连接字符串的命令,直接提供参数值,返回DataSet. 489 /// </summary> 490 /// <remarks> 491 /// 此方法不提供访问存储过程输出参数和返回值. 492 /// 示例: 493 /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36); 494 /// </remarks> 495 /// <param name="connectionString">一个有效的数据库连接字符串</param> 496 /// <param name="spName">存储过程名</param> 497 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 498 /// <returns>返回一个包含结果集的DataSet</returns> 499 public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues) 500 { 501 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 502 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 503 504 if ((parameterValues != null) && (parameterValues.Length > 0)) 505 { 506 // 从缓存中检索存储过程参数 507 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 508 509 // 给存储过程参数分配值 510 AssignParameterValues(commandParameters, parameterValues); 511 512 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters); 513 } 514 else 515 { 516 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); 517 } 518 } 519 520 /// <summary> 521 /// 执行指定数据库连接对象的命令,返回DataSet. 522 /// </summary> 523 /// <remarks> 524 /// 示例: 525 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders"); 526 /// </remarks> 527 /// <param name="connection">一个有效的数据库连接对象</param> 528 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 529 /// <param name="commandText">存储过程名或T-SQL语句</param> 530 /// <returns>返回一个包含结果集的DataSet</returns> 531 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) 532 { 533 return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null); 534 } 535 536 /// <summary> 537 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet. 538 /// </summary> 539 /// <remarks> 540 /// 示例: 541 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 542 /// </remarks> 543 /// <param name="connection">一个有效的数据库连接对象</param> 544 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 545 /// <param name="commandText">存储过程名或T-SQL语句</param> 546 /// <param name="commandParameters">SqlParamter参数数组</param> 547 /// <returns>返回一个包含结果集的DataSet</returns> 548 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 549 { 550 if (connection == null) throw new ArgumentNullException("connection"); 551 552 // 预处理 553 SqlCommand cmd = new SqlCommand(); 554 bool mustCloseConnection = false; 555 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); 556 557 // 创建SqlDataAdapter和DataSet. 558 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 559 { 560 DataSet ds = new DataSet(); 561 562 // 填充DataSet. 563 da.Fill(ds); 564 565 cmd.Parameters.Clear(); 566 567 if (mustCloseConnection) 568 connection.Close(); 569 570 return ds; 571 } 572 } 573 574 /// <summary> 575 /// 执行指定数据库连接对象的命令,指定参数值,返回DataSet. 576 /// </summary> 577 /// <remarks> 578 /// 此方法不提供访问存储过程输入参数和返回值. 579 /// 示例.: 580 /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36); 581 /// </remarks> 582 /// <param name="connection">一个有效的数据库连接对象</param> 583 /// <param name="spName">存储过程名</param> 584 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 585 /// <returns>返回一个包含结果集的DataSet</returns> 586 public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues) 587 { 588 if (connection == null) throw new ArgumentNullException("connection"); 589 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 590 591 if ((parameterValues != null) && (parameterValues.Length > 0)) 592 { 593 // 比缓存中加载存储过程参数 594 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 595 596 // 给存储过程参数分配值 597 AssignParameterValues(commandParameters, parameterValues); 598 599 return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters); 600 } 601 else 602 { 603 return ExecuteDataset(connection, CommandType.StoredProcedure, spName); 604 } 605 } 606 607 /// <summary> 608 /// 执行指定事务的命令,返回DataSet. 609 /// </summary> 610 /// <remarks> 611 /// 示例: 612 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders"); 613 /// </remarks> 614 /// <param name="transaction">事务</param> 615 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 616 /// <param name="commandText">存储过程名或T-SQL语句</param> 617 /// <returns>返回一个包含结果集的DataSet</returns> 618 public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText) 619 { 620 return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null); 621 } 622 623 /// <summary> 624 /// 执行指定事务的命令,指定参数,返回DataSet. 625 /// </summary> 626 /// <remarks> 627 /// 示例: 628 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 629 /// </remarks> 630 /// <param name="transaction">事务</param> 631 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 632 /// <param name="commandText">存储过程名或T-SQL语句</param> 633 /// <param name="commandParameters">SqlParamter参数数组</param> 634 /// <returns>返回一个包含结果集的DataSet</returns> 635 public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 636 { 637 if (transaction == null) throw new ArgumentNullException("transaction"); 638 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 639 640 // 预处理 641 SqlCommand cmd = new SqlCommand(); 642 bool mustCloseConnection = false; 643 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 644 645 // 创建 DataAdapter & DataSet 646 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 647 { 648 DataSet ds = new DataSet(); 649 da.Fill(ds); 650 cmd.Parameters.Clear(); 651 return ds; 652 } 653 } 654 655 /// <summary> 656 /// 执行指定事务的命令,指定参数值,返回DataSet. 657 /// </summary> 658 /// <remarks> 659 /// 此方法不提供访问存储过程输入参数和返回值. 660 /// 示例.: 661 /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36); 662 /// </remarks> 663 /// <param name="transaction">事务</param> 664 /// <param name="spName">存储过程名</param> 665 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 666 /// <returns>返回一个包含结果集的DataSet</returns> 667 public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues) 668 { 669 if (transaction == null) throw new ArgumentNullException("transaction"); 670 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 671 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 672 673 if ((parameterValues != null) && (parameterValues.Length > 0)) 674 { 675 // 从缓存中加载存储过程参数 676 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 677 678 // 给存储过程参数分配值 679 AssignParameterValues(commandParameters, parameterValues); 680 681 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters); 682 } 683 else 684 { 685 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName); 686 } 687 } 688 689 #endregion ExecuteDataset数据集命令结束 690 691 #region ExecuteReader 数据阅读器 692 693 /// <summary> 694 /// 枚举,标识数据库连接是由SqlHelper提供还是由调用者提供 695 /// </summary> 696 private enum SqlConnectionOwnership 697 { 698 /// <summary>由SqlHelper提供连接</summary> 699 Internal, 700 /// <summary>由调用者提供连接</summary> 701 External 702 } 703 704 /// <summary> 705 /// 执行指定数据库连接对象的数据阅读器. 706 /// </summary> 707 /// <remarks> 708 /// 如果是SqlHelper打开连接,当连接关闭DataReader也将关闭. 709 /// 如果是调用都打开连接,DataReader由调用都管理. 710 /// </remarks> 711 /// <param name="connection">一个有效的数据库连接对象</param> 712 /// <param name="transaction">一个有效的事务,或者为 'null'</param> 713 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 714 /// <param name="commandText">存储过程名或T-SQL语句</param> 715 /// <param name="commandParameters">SqlParameters参数数组,如果没有参数则为'null'</param> 716 /// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由SqlHelper提供</param> 717 /// <returns>返回包含结果集的SqlDataReader</returns> 718 private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) 719 { 720 if (connection == null) throw new ArgumentNullException("connection"); 721 722 bool mustCloseConnection = false; 723 // 创建命令 724 SqlCommand cmd = new SqlCommand(); 725 try 726 { 727 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 728 729 // 创建数据阅读器 730 SqlDataReader dataReader; 731 732 if (connectionOwnership == SqlConnectionOwnership.External) 733 { 734 dataReader = cmd.ExecuteReader(); 735 } 736 else 737 { 738 dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 739 } 740 741 // 清除参数,以便再次使用.. 742 // HACK: There is a problem here, the output parameter values are fletched 743 // when the reader is closed, so if the parameters are detached from the command 744 // then the SqlReader can磘 set its values. 745 // When this happen, the parameters can磘 be used again in other command. 746 bool canClear = true; 747 foreach (SqlParameter commandParameter in cmd.Parameters) 748 { 749 if (commandParameter.Direction != ParameterDirection.Input) 750 canClear = false; 751 } 752 753 if (canClear) 754 { 755 cmd.Parameters.Clear(); 756 } 757 758 return dataReader; 759 } 760 catch 761 { 762 if (mustCloseConnection) 763 connection.Close(); 764 throw; 765 } 766 } 767 768 /// <summary> 769 /// 执行指定数据库连接字符串的数据阅读器. 770 /// </summary> 771 /// <remarks> 772 /// 示例: 773 /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders"); 774 /// </remarks> 775 /// <param name="connectionString">一个有效的数据库连接字符串</param> 776 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 777 /// <param name="commandText">存储过程名或T-SQL语句</param> 778 /// <returns>返回包含结果集的SqlDataReader</returns> 779 public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) 780 { 781 return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null); 782 } 783 784 /// <summary> 785 /// 执行指定数据库连接字符串的数据阅读器,指定参数. 786 /// </summary> 787 /// <remarks> 788 /// 示例: 789 /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 790 /// </remarks> 791 /// <param name="connectionString">一个有效的数据库连接字符串</param> 792 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 793 /// <param name="commandText">存储过程名或T-SQL语句</param> 794 /// <param name="commandParameters">SqlParamter参数数组(new SqlParameter("@prodid", 24))</param> 795 /// <returns>返回包含结果集的SqlDataReader</returns> 796 public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 797 { 798 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 799 SqlConnection connection = null; 800 try 801 { 802 connection = new SqlConnection(connectionString); 803 connection.Open(); 804 805 return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal); 806 } 807 catch 808 { 809 // If we fail to return the SqlDatReader, we need to close the connection ourselves 810 if (connection != null) connection.Close(); 811 throw; 812 } 813 814 } 815 816 /// <summary> 817 /// 执行指定数据库连接字符串的数据阅读器,指定参数值. 818 /// </summary> 819 /// <remarks> 820 /// 此方法不提供访问存储过程输出参数和返回值参数. 821 /// 示例: 822 /// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36); 823 /// </remarks> 824 /// <param name="connectionString">一个有效的数据库连接字符串</param> 825 /// <param name="spName">存储过程名</param> 826 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 827 /// <returns>返回包含结果集的SqlDataReader</returns> 828 public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues) 829 { 830 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 831 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 832 833 if ((parameterValues != null) && (parameterValues.Length > 0)) 834 { 835 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 836 837 AssignParameterValues(commandParameters, parameterValues); 838 839 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters); 840 } 841 else 842 { 843 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName); 844 } 845 } 846 847 /// <summary> 848 /// 执行指定数据库连接对象的数据阅读器. 849 /// </summary> 850 /// <remarks> 851 /// 示例: 852 /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders"); 853 /// </remarks> 854 /// <param name="connection">一个有效的数据库连接对象</param> 855 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 856 /// <param name="commandText">存储过程名或T-SQL语句</param> 857 /// <returns>返回包含结果集的SqlDataReader</returns> 858 public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText) 859 { 860 return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null); 861 } 862 863 /// <summary> 864 /// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数. 865 /// </summary> 866 /// <remarks> 867 /// 示例: 868 /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 869 /// </remarks> 870 /// <param name="connection">一个有效的数据库连接对象</param> 871 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 872 /// <param name="commandText">命令类型 (存储过程,命令文本或其它)</param> 873 /// <param name="commandParameters">SqlParamter参数数组</param> 874 /// <returns>返回包含结果集的SqlDataReader</returns> 875 public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 876 { 877 return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External); 878 } 879 880 /// <summary> 881 /// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数值. 882 /// </summary> 883 /// <remarks> 884 /// 此方法不提供访问存储过程输出参数和返回值参数. 885 /// 示例: 886 /// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36); 887 /// </remarks> 888 /// <param name="connection">一个有效的数据库连接对象</param> 889 /// <param name="spName">T存储过程名</param> 890 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 891 /// <returns>返回包含结果集的SqlDataReader</returns> 892 public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues) 893 { 894 if (connection == null) throw new ArgumentNullException("connection"); 895 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 896 897 if ((parameterValues != null) && (parameterValues.Length > 0)) 898 { 899 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 900 901 AssignParameterValues(commandParameters, parameterValues); 902 903 return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters); 904 } 905 else 906 { 907 return ExecuteReader(connection, CommandType.StoredProcedure, spName); 908 } 909 } 910 911 /// <summary> 912 /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值. 913 /// </summary> 914 /// <remarks> 915 /// 示例: 916 /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders"); 917 /// </remarks> 918 /// <param name="transaction">一个有效的连接事务</param> 919 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 920 /// <param name="commandText">存储过程名称或T-SQL语句</param> 921 /// <returns>返回包含结果集的SqlDataReader</returns> 922 public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText) 923 { 924 return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null); 925 } 926 927 /// <summary> 928 /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数. 929 /// </summary> 930 /// <remarks> 931 /// 示例: 932 /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 933 /// </remarks> 934 /// <param name="transaction">一个有效的连接事务</param> 935 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 936 /// <param name="commandText">存储过程名称或T-SQL语句</param> 937 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 938 /// <returns>返回包含结果集的SqlDataReader</returns> 939 public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 940 { 941 if (transaction == null) throw new ArgumentNullException("transaction"); 942 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 943 944 return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External); 945 } 946 947 /// <summary> 948 /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值. 949 /// </summary> 950 /// <remarks> 951 /// 此方法不提供访问存储过程输出参数和返回值参数. 952 /// 953 /// 示例: 954 /// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36); 955 /// </remarks> 956 /// <param name="transaction">一个有效的连接事务</param> 957 /// <param name="spName">存储过程名称</param> 958 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 959 /// <returns>返回包含结果集的SqlDataReader</returns> 960 public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues) 961 { 962 if (transaction == null) throw new ArgumentNullException("transaction"); 963 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 964 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 965 966 // 如果有参数值 967 if ((parameterValues != null) && (parameterValues.Length > 0)) 968 { 969 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 970 971 AssignParameterValues(commandParameters, parameterValues); 972 973 return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters); 974 } 975 else 976 { 977 // 没有参数值 978 return ExecuteReader(transaction, CommandType.StoredProcedure, spName); 979 } 980 } 981 982 #endregion ExecuteReader数据阅读器 983 984 #region ExecuteScalar 返回结果集中的第一行第一列 985 986 /// <summary> 987 /// 执行指定数据库连接字符串的命令,返回结果集中的第一行第一列. 988 /// </summary> 989 /// <remarks> 990 /// 示例: 991 /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"); 992 /// </remarks> 993 /// <param name="connectionString">一个有效的数据库连接字符串</param> 994 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 995 /// <param name="commandText">存储过程名称或T-SQL语句</param> 996 /// <returns>返回结果集中的第一行第一列</returns> 997 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText) 998 { 999 // 执行参数为空的方法 1000 return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null); 1001 } 1002 1003 /// <summary> 1004 /// 执行指定数据库连接字符串的命令,指定参数,返回结果集中的第一行第一列. 1005 /// </summary> 1006 /// <remarks> 1007 /// 示例: 1008 /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); 1009 /// </remarks> 1010 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1011 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1012 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1013 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1014 /// <returns>返回结果集中的第一行第一列</returns> 1015 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 1016 { 1017 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1018 // 创建并打开数据库连接对象,操作完成释放对象. 1019 using (SqlConnection connection = new SqlConnection(connectionString)) 1020 { 1021 connection.Open(); 1022 1023 // 调用指定数据库连接字符串重载方法. 1024 return ExecuteScalar(connection, commandType, commandText, commandParameters); 1025 } 1026 } 1027 1028 /// <summary> 1029 /// 执行指定数据库连接字符串的命令,指定参数值,返回结果集中的第一行第一列. 1030 /// </summary> 1031 /// <remarks> 1032 /// 此方法不提供访问存储过程输出参数和返回值参数. 1033 /// 1034 /// 示例: 1035 /// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36); 1036 /// </remarks> 1037 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1038 /// <param name="spName">存储过程名称</param> 1039 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1040 /// <returns>返回结果集中的第一行第一列</returns> 1041 public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues) 1042 { 1043 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1044 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1045 1046 // 如果有参数值 1047 if ((parameterValues != null) && (parameterValues.Length > 0)) 1048 { 1049 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1050 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 1051 1052 // 给存储过程参数赋值 1053 AssignParameterValues(commandParameters, parameterValues); 1054 1055 // 调用重载方法 1056 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); 1057 } 1058 else 1059 { 1060 // 没有参数值 1061 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); 1062 } 1063 } 1064 1065 /// <summary> 1066 /// 执行指定数据库连接对象的命令,返回结果集中的第一行第一列. 1067 /// </summary> 1068 /// <remarks> 1069 /// 示例: 1070 /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); 1071 /// </remarks> 1072 /// <param name="connection">一个有效的数据库连接对象</param> 1073 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1074 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1075 /// <returns>返回结果集中的第一行第一列</returns> 1076 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText) 1077 { 1078 // 执行参数为空的方法 1079 return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null); 1080 } 1081 1082 /// <summary> 1083 /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列. 1084 /// </summary> 1085 /// <remarks> 1086 /// 示例: 1087 /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); 1088 /// </remarks> 1089 /// <param name="connection">一个有效的数据库连接对象</param> 1090 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1091 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1092 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1093 /// <returns>返回结果集中的第一行第一列</returns> 1094 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 1095 { 1096 if (connection == null) throw new ArgumentNullException("connection"); 1097 1098 // 创建SqlCommand命令,并进行预处理 1099 SqlCommand cmd = new SqlCommand(); 1100 1101 bool mustCloseConnection = false; 1102 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); 1103 1104 // 执行SqlCommand命令,并返回结果. 1105 object retval = cmd.ExecuteScalar(); 1106 1107 // 清除参数,以便再次使用. 1108 cmd.Parameters.Clear(); 1109 1110 if (mustCloseConnection) 1111 connection.Close(); 1112 1113 return retval; 1114 } 1115 1116 /// <summary> 1117 /// 执行指定数据库连接对象的命令,指定参数值,返回结果集中的第一行第一列. 1118 /// </summary> 1119 /// <remarks> 1120 /// 此方法不提供访问存储过程输出参数和返回值参数. 1121 /// 1122 /// 示例: 1123 /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36); 1124 /// </remarks> 1125 /// <param name="connection">一个有效的数据库连接对象</param> 1126 /// <param name="spName">存储过程名称</param> 1127 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1128 /// <returns>返回结果集中的第一行第一列</returns> 1129 public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues) 1130 { 1131 if (connection == null) throw new ArgumentNullException("connection"); 1132 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1133 1134 // 如果有参数值 1135 if ((parameterValues != null) && (parameterValues.Length > 0)) 1136 { 1137 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1138 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1139 1140 // 给存储过程参数赋值 1141 AssignParameterValues(commandParameters, parameterValues); 1142 1143 // 调用重载方法 1144 return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters); 1145 } 1146 else 1147 { 1148 // 没有参数值 1149 return ExecuteScalar(connection, CommandType.StoredProcedure, spName); 1150 } 1151 } 1152 1153 /// <summary> 1154 /// 执行指定数据库事务的命令,返回结果集中的第一行第一列. 1155 /// </summary> 1156 /// <remarks> 1157 /// 示例: 1158 /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"); 1159 /// </remarks> 1160 /// <param name="transaction">一个有效的连接事务</param> 1161 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1162 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1163 /// <returns>返回结果集中的第一行第一列</returns> 1164 public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText) 1165 { 1166 // 执行参数为空的方法 1167 return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null); 1168 } 1169 1170 /// <summary> 1171 /// 执行指定数据库事务的命令,指定参数,返回结果集中的第一行第一列. 1172 /// </summary> 1173 /// <remarks> 1174 /// 示例: 1175 /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); 1176 /// </remarks> 1177 /// <param name="transaction">一个有效的连接事务</param> 1178 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1179 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1180 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1181 /// <returns>返回结果集中的第一行第一列</returns> 1182 public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 1183 { 1184 if (transaction == null) throw new ArgumentNullException("transaction"); 1185 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1186 1187 // 创建SqlCommand命令,并进行预处理 1188 SqlCommand cmd = new SqlCommand(); 1189 bool mustCloseConnection = false; 1190 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 1191 1192 // 执行SqlCommand命令,并返回结果. 1193 object retval = cmd.ExecuteScalar(); 1194 1195 // 清除参数,以便再次使用. 1196 cmd.Parameters.Clear(); 1197 return retval; 1198 } 1199 1200 /// <summary> 1201 /// 执行指定数据库事务的命令,指定参数值,返回结果集中的第一行第一列. 1202 /// </summary> 1203 /// <remarks> 1204 /// 此方法不提供访问存储过程输出参数和返回值参数. 1205 /// 1206 /// 示例: 1207 /// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36); 1208 /// </remarks> 1209 /// <param name="transaction">一个有效的连接事务</param> 1210 /// <param name="spName">存储过程名称</param> 1211 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1212 /// <returns>返回结果集中的第一行第一列</returns> 1213 public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues) 1214 { 1215 if (transaction == null) throw new ArgumentNullException("transaction"); 1216 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1217 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1218 1219 // 如果有参数值 1220 if ((parameterValues != null) && (parameterValues.Length > 0)) 1221 { 1222 // PPull the parameters for this stored procedure from the parameter cache () 1223 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1224 1225 // 给存储过程参数赋值 1226 AssignParameterValues(commandParameters, parameterValues); 1227 1228 // 调用重载方法 1229 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters); 1230 } 1231 else 1232 { 1233 // 没有参数值 1234 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName); 1235 } 1236 } 1237 1238 #endregion ExecuteScalar 1239 1240 #region ExecuteXmlReader XML阅读器 1241 /// <summary> 1242 /// 执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回. 1243 /// </summary> 1244 /// <remarks> 1245 /// 示例: 1246 /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders"); 1247 /// </remarks> 1248 /// <param name="connection">一个有效的数据库连接对象</param> 1249 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1250 /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param> 1251 /// <returns>返回XmlReader结果集对象.</returns> 1252 public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText) 1253 { 1254 // 执行参数为空的方法 1255 return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null); 1256 } 1257 1258 /// <summary> 1259 /// 执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数. 1260 /// </summary> 1261 /// <remarks> 1262 /// 示例: 1263 /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 1264 /// </remarks> 1265 /// <param name="connection">一个有效的数据库连接对象</param> 1266 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1267 /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param> 1268 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1269 /// <returns>返回XmlReader结果集对象.</returns> 1270 public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 1271 { 1272 if (connection == null) throw new ArgumentNullException("connection"); 1273 1274 bool mustCloseConnection = false; 1275 // 创建SqlCommand命令,并进行预处理 1276 SqlCommand cmd = new SqlCommand(); 1277 try 1278 { 1279 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); 1280 1281 // 执行命令 1282 XmlReader retval = cmd.ExecuteXmlReader(); 1283 1284 // 清除参数,以便再次使用. 1285 cmd.Parameters.Clear(); 1286 1287 return retval; 1288 } 1289 catch 1290 { 1291 if (mustCloseConnection) 1292 connection.Close(); 1293 throw; 1294 } 1295 } 1296 1297 /// <summary> 1298 /// 执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数值. 1299 /// </summary> 1300 /// <remarks> 1301 /// 此方法不提供访问存储过程输出参数和返回值参数. 1302 /// 1303 /// 示例: 1304 /// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36); 1305 /// </remarks> 1306 /// <param name="connection">一个有效的数据库连接对象</param> 1307 /// <param name="spName">存储过程名称 using "FOR XML AUTO"</param> 1308 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1309 /// <returns>返回XmlReader结果集对象.</returns> 1310 public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues) 1311 { 1312 if (connection == null) throw new ArgumentNullException("connection"); 1313 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1314 1315 // 如果有参数值 1316 if ((parameterValues != null) && (parameterValues.Length > 0)) 1317 { 1318 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1319 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1320 1321 // 给存储过程参数赋值 1322 AssignParameterValues(commandParameters, parameterValues); 1323 1324 // 调用重载方法 1325 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters); 1326 } 1327 else 1328 { 1329 // 没有参数值 1330 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName); 1331 } 1332 } 1333 1334 /// <summary> 1335 /// 执行指定数据库事务的SqlCommand命令,并产生一个XmlReader对象做为结果集返回. 1336 /// </summary> 1337 /// <remarks> 1338 /// 示例: 1339 /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders"); 1340 /// </remarks> 1341 /// <param name="transaction">一个有效的连接事务</param> 1342 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1343 /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param> 1344 /// <returns>返回XmlReader结果集对象.</returns> 1345 public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText) 1346 { 1347 // 执行参数为空的方法 1348 return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null); 1349 } 1350 1351 /// <summary> 1352 /// 执行指定数据库事务的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数. 1353 /// </summary> 1354 /// <remarks> 1355 /// 示例: 1356 /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 1357 /// </remarks> 1358 /// <param name="transaction">一个有效的连接事务</param> 1359 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1360 /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param> 1361 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1362 /// <returns>返回XmlReader结果集对象.</returns> 1363 public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 1364 { 1365 if (transaction == null) throw new ArgumentNullException("transaction"); 1366 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1367 1368 // 创建SqlCommand命令,并进行预处理 1369 SqlCommand cmd = new SqlCommand(); 1370 bool mustCloseConnection = false; 1371 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 1372 1373 // 执行命令 1374 XmlReader retval = cmd.ExecuteXmlReader(); 1375 1376 // 清除参数,以便再次使用. 1377 cmd.Parameters.Clear(); 1378 return retval; 1379 } 1380 1381 /// <summary> 1382 /// 执行指定数据库事务的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数值. 1383 /// </summary> 1384 /// <remarks> 1385 /// 此方法不提供访问存储过程输出参数和返回值参数. 1386 /// 1387 /// 示例: 1388 /// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36); 1389 /// </remarks> 1390 /// <param name="transaction">一个有效的连接事务</param> 1391 /// <param name="spName">存储过程名称</param> 1392 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1393 /// <returns>返回一个包含结果集的DataSet.</returns> 1394 public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues) 1395 { 1396 if (transaction == null) throw new ArgumentNullException("transaction"); 1397 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1398 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1399 1400 // 如果有参数值 1401 if ((parameterValues != null) && (parameterValues.Length > 0)) 1402 { 1403 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1404 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1405 1406 // 给存储过程参数赋值 1407 AssignParameterValues(commandParameters, parameterValues); 1408 1409 // 调用重载方法 1410 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters); 1411 } 1412 else 1413 { 1414 // 没有参数值 1415 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName); 1416 } 1417 } 1418 1419 #endregion ExecuteXmlReader 阅读器结束 1420 1421 #region FillDataset 填充数据集 1422 /// <summary> 1423 /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集. 1424 /// </summary> 1425 /// <remarks> 1426 /// 示例: 1427 /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); 1428 /// </remarks> 1429 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1430 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1431 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1432 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1433 /// <param name="tableNames">表映射的数据表数组 1434 /// 用户定义的表名 (可有是实际的表名.)</param> 1435 public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames) 1436 { 1437 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1438 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1439 1440 // 创建并打开数据库连接对象,操作完成释放对象. 1441 using (SqlConnection connection = new SqlConnection(connectionString)) 1442 { 1443 connection.Open(); 1444 1445 // 调用指定数据库连接字符串重载方法. 1446 FillDataset(connection, commandType, commandText, dataSet, tableNames); 1447 } 1448 } 1449 1450 /// <summary> 1451 /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集.指定命令参数. 1452 /// </summary> 1453 /// <remarks> 1454 /// 示例: 1455 /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 1456 /// </remarks> 1457 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1458 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1459 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1460 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1461 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1462 /// <param name="tableNames">表映射的数据表数组 1463 /// 用户定义的表名 (可有是实际的表名.) 1464 /// </param> 1465 public static void FillDataset(string connectionString, CommandType commandType, 1466 string commandText, DataSet dataSet, string[] tableNames, 1467 params SqlParameter[] commandParameters) 1468 { 1469 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1470 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1471 // 创建并打开数据库连接对象,操作完成释放对象. 1472 using (SqlConnection connection = new SqlConnection(connectionString)) 1473 { 1474 connection.Open(); 1475 1476 // 调用指定数据库连接字符串重载方法. 1477 FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters); 1478 } 1479 } 1480 1481 /// <summary> 1482 /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集,指定存储过程参数值. 1483 /// </summary> 1484 /// <remarks> 1485 /// 此方法不提供访问存储过程输出参数和返回值参数. 1486 /// 1487 /// 示例: 1488 /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24); 1489 /// </remarks> 1490 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1491 /// <param name="spName">存储过程名称</param> 1492 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1493 /// <param name="tableNames">表映射的数据表数组 1494 /// 用户定义的表名 (可有是实际的表名.) 1495 /// </param> 1496 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1497 public static void FillDataset(string connectionString, string spName, 1498 DataSet dataSet, string[] tableNames, 1499 params object[] parameterValues) 1500 { 1501 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1502 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1503 // 创建并打开数据库连接对象,操作完成释放对象. 1504 using (SqlConnection connection = new SqlConnection(connectionString)) 1505 { 1506 connection.Open(); 1507 1508 // 调用指定数据库连接字符串重载方法. 1509 FillDataset(connection, spName, dataSet, tableNames, parameterValues); 1510 } 1511 } 1512 1513 /// <summary> 1514 /// 执行指定数据库连接对象的命令,映射数据表并填充数据集. 1515 /// </summary> 1516 /// <remarks> 1517 /// 示例: 1518 /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); 1519 /// </remarks> 1520 /// <param name="connection">一个有效的数据库连接对象</param> 1521 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1522 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1523 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1524 /// <param name="tableNames">表映射的数据表数组 1525 /// 用户定义的表名 (可有是实际的表名.) 1526 /// </param> 1527 public static void FillDataset(SqlConnection connection, CommandType commandType, 1528 string commandText, DataSet dataSet, string[] tableNames) 1529 { 1530 FillDataset(connection, commandType, commandText, dataSet, tableNames, null); 1531 } 1532 1533 /// <summary> 1534 /// 执行指定数据库连接对象的命令,映射数据表并填充数据集,指定参数. 1535 /// </summary> 1536 /// <remarks> 1537 /// 示例: 1538 /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 1539 /// </remarks> 1540 /// <param name="connection">一个有效的数据库连接对象</param> 1541 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1542 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1543 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1544 /// <param name="tableNames">表映射的数据表数组 1545 /// 用户定义的表名 (可有是实际的表名.) 1546 /// </param> 1547 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1548 public static void FillDataset(SqlConnection connection, CommandType commandType, 1549 string commandText, DataSet dataSet, string[] tableNames, 1550 params SqlParameter[] commandParameters) 1551 { 1552 FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters); 1553 } 1554 1555 /// <summary> 1556 /// 执行指定数据库连接对象的命令,映射数据表并填充数据集,指定存储过程参数值. 1557 /// </summary> 1558 /// <remarks> 1559 /// 此方法不提供访问存储过程输出参数和返回值参数. 1560 /// 1561 /// 示例: 1562 /// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36); 1563 /// </remarks> 1564 /// <param name="connection">一个有效的数据库连接对象</param> 1565 /// <param name="spName">存储过程名称</param> 1566 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1567 /// <param name="tableNames">表映射的数据表数组 1568 /// 用户定义的表名 (可有是实际的表名.) 1569 /// </param> 1570 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1571 public static void FillDataset(SqlConnection connection, string spName, 1572 DataSet dataSet, string[] tableNames, 1573 params object[] parameterValues) 1574 { 1575 if (connection == null) throw new ArgumentNullException("connection"); 1576 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1577 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1578 1579 // 如果有参数值 1580 if ((parameterValues != null) && (parameterValues.Length > 0)) 1581 { 1582 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1583 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1584 1585 // 给存储过程参数赋值 1586 AssignParameterValues(commandParameters, parameterValues); 1587 1588 // 调用重载方法 1589 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters); 1590 } 1591 else 1592 { 1593 // 没有参数值 1594 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames); 1595 } 1596 } 1597 1598 /// <summary> 1599 /// 执行指定数据库事务的命令,映射数据表并填充数据集. 1600 /// </summary> 1601 /// <remarks> 1602 /// 示例: 1603 /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); 1604 /// </remarks> 1605 /// <param name="transaction">一个有效的连接事务</param> 1606 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1607 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1608 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1609 /// <param name="tableNames">表映射的数据表数组 1610 /// 用户定义的表名 (可有是实际的表名.) 1611 /// </param> 1612 public static void FillDataset(SqlTransaction transaction, CommandType commandType, 1613 string commandText, 1614 DataSet dataSet, string[] tableNames) 1615 { 1616 FillDataset(transaction, commandType, commandText, dataSet, tableNames, null); 1617 } 1618 1619 /// <summary> 1620 /// 执行指定数据库事务的命令,映射数据表并填充数据集,指定参数. 1621 /// </summary> 1622 /// <remarks> 1623 /// 示例: 1624 /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 1625 /// </remarks> 1626 /// <param name="transaction">一个有效的连接事务</param> 1627 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1628 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1629 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1630 /// <param name="tableNames">表映射的数据表数组 1631 /// 用户定义的表名 (可有是实际的表名.) 1632 /// </param> 1633 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1634 public static void FillDataset(SqlTransaction transaction, CommandType commandType, 1635 string commandText, DataSet dataSet, string[] tableNames, 1636 params SqlParameter[] commandParameters) 1637 { 1638 FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters); 1639 } 1640 1641 /// <summary> 1642 /// 执行指定数据库事务的命令,映射数据表并填充数据集,指定存储过程参数值. 1643 /// </summary> 1644 /// <remarks> 1645 /// 此方法不提供访问存储过程输出参数和返回值参数. 1646 /// 1647 /// 示例: 1648 /// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36); 1649 /// </remarks> 1650 /// <param name="transaction">一个有效的连接事务</param> 1651 /// <param name="spName">存储过程名称</param> 1652 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1653 /// <param name="tableNames">表映射的数据表数组 1654 /// 用户定义的表名 (可有是实际的表名.) 1655 /// </param> 1656 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1657 public static void FillDataset(SqlTransaction transaction, string spName, 1658 DataSet dataSet, string[] tableNames, 1659 params object[] parameterValues) 1660 { 1661 if (transaction == null) throw new ArgumentNullException("transaction"); 1662 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1663 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1664 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1665 1666 // 如果有参数值 1667 if ((parameterValues != null) && (parameterValues.Length > 0)) 1668 { 1669 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1670 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1671 1672 // 给存储过程参数赋值 1673 AssignParameterValues(commandParameters, parameterValues); 1674 1675 // 调用重载方法 1676 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters); 1677 } 1678 else 1679 { 1680 // 没有参数值 1681 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames); 1682 } 1683 } 1684 1685 /// <summary> 1686 /// [私有方法][内部调用]执行指定数据库连接对象/事务的命令,映射数据表并填充数据集,DataSet/TableNames/SqlParameters. 1687 /// </summary> 1688 /// <remarks> 1689 /// 示例: 1690 /// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 1691 /// </remarks> 1692 /// <param name="connection">一个有效的数据库连接对象</param> 1693 /// <param name="transaction">一个有效的连接事务</param> 1694 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1695 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1696 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1697 /// <param name="tableNames">表映射的数据表数组 1698 /// 用户定义的表名 (可有是实际的表名.) 1699 /// </param> 1700 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1701 private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, 1702 string commandText, DataSet dataSet, string[] tableNames, 1703 params SqlParameter[] commandParameters) 1704 { 1705 if (connection == null) throw new ArgumentNullException("connection"); 1706 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1707 1708 // 创建SqlCommand命令,并进行预处理 1709 SqlCommand command = new SqlCommand(); 1710 bool mustCloseConnection = false; 1711 PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 1712 1713 // 执行命令 1714 using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command)) 1715 { 1716 1717 // 追加表映射 1718 if (tableNames != null && tableNames.Length > 0) 1719 { 1720 string tableName = "Table"; 1721 for (int index = 0; index < tableNames.Length; index++) 1722 { 1723 if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames"); 1724 dataAdapter.TableMappings.Add(tableName, tableNames[index]); 1725 tableName += (index + 1).ToString(); 1726 } 1727 } 1728 1729 // 填充数据集使用默认表名称 1730 dataAdapter.Fill(dataSet); 1731 1732 // 清除参数,以便再次使用. 1733 command.Parameters.Clear(); 1734 } 1735 1736 if (mustCloseConnection) 1737 connection.Close(); 1738 } 1739 #endregion 1740 1741 #region UpdateDataset 更新数据集 1742 /// <summary> 1743 /// 执行数据集更新到数据库,指定inserted, updated, or deleted命令. 1744 /// </summary> 1745 /// <remarks> 1746 /// 示例: 1747 /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); 1748 /// </remarks> 1749 /// <param name="insertCommand">[追加记录]一个有效的T-SQL语句或存储过程</param> 1750 /// <param name="deleteCommand">[删除记录]一个有效的T-SQL语句或存储过程</param> 1751 /// <param name="updateCommand">[更新记录]一个有效的T-SQL语句或存储过程</param> 1752 /// <param name="dataSet">要更新到数据库的DataSet</param> 1753 /// <param name="tableName">要更新到数据库的DataTable</param> 1754 public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName) 1755 { 1756 if (insertCommand == null) throw new ArgumentNullException("insertCommand"); 1757 if (deleteCommand == null) throw new ArgumentNullException("deleteCommand"); 1758 if (updateCommand == null) throw new ArgumentNullException("updateCommand"); 1759 if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName"); 1760 1761 // 创建SqlDataAdapter,当操作完成后释放. 1762 using (SqlDataAdapter dataAdapter = new SqlDataAdapter()) 1763 { 1764 // 设置数据适配器命令 1765 dataAdapter.UpdateCommand = updateCommand; 1766 dataAdapter.InsertCommand = insertCommand; 1767 dataAdapter.DeleteCommand = deleteCommand; 1768 1769 // 更新数据集改变到数据库 1770 dataAdapter.Update(dataSet, tableName); 1771 1772 // 提交所有改变到数据集. 1773 dataSet.AcceptChanges(); 1774 } 1775 } 1776 #endregion 1777 1778 #region CreateCommand 创建一条SqlCommand命令 1779 /// <summary> 1780 /// 创建SqlCommand命令,指定数据库连接对象,存储过程名和参数. 1781 /// </summary> 1782 /// <remarks> 1783 /// 示例: 1784 /// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName"); 1785 /// </remarks> 1786 /// <param name="connection">一个有效的数据库连接对象</param> 1787 /// <param name="spName">存储过程名称</param> 1788 /// <param name="sourceColumns">源表的列名称数组</param> 1789 /// <returns>返回SqlCommand命令</returns> 1790 public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns) 1791 { 1792 if (connection == null) throw new ArgumentNullException("connection"); 1793 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1794 1795 // 创建命令 1796 SqlCommand cmd = new SqlCommand(spName, connection); 1797 cmd.CommandType = CommandType.StoredProcedure; 1798 1799 // 如果有参数值 1800 if ((sourceColumns != null) && (sourceColumns.Length > 0)) 1801 { 1802 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1803 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1804 1805 // 将源表的列到映射到DataSet命令中. 1806 for (int index = 0; index < sourceColumns.Length; index++) 1807 commandParameters[index].SourceColumn = sourceColumns[index]; 1808 1809 // Attach the discovered parameters to the SqlCommand object 1810 AttachParameters(cmd, commandParameters); 1811 } 1812 1813 return cmd; 1814 } 1815 #endregion 1816 1817 #region ExecuteNonQueryTypedParams 类型化参数(DataRow) 1818 /// <summary> 1819 /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回受影响的行数. 1820 /// </summary> 1821 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1822 /// <param name="spName">存储过程名称</param> 1823 /// <param name="dataRow">使用DataRow作为参数值</param> 1824 /// <returns>返回影响的行数</returns> 1825 public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow) 1826 { 1827 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1828 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1829 1830 // 如果row有值,存储过程必须初始化. 1831 if (dataRow != null && dataRow.ItemArray.Length > 0) 1832 { 1833 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1834 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 1835 1836 // 分配参数值 1837 AssignParameterValues(commandParameters, dataRow); 1838 1839 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); 1840 } 1841 else 1842 { 1843 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); 1844 } 1845 } 1846 1847 /// <summary> 1848 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回受影响的行数. 1849 /// </summary> 1850 /// <param name="connection">一个有效的数据库连接对象</param> 1851 /// <param name="spName">存储过程名称</param> 1852 /// <param name="dataRow">使用DataRow作为参数值</param> 1853 /// <returns>返回影响的行数</returns> 1854 public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow) 1855 { 1856 if (connection == null) throw new ArgumentNullException("connection"); 1857 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1858 1859 // 如果row有值,存储过程必须初始化. 1860 if (dataRow != null && dataRow.ItemArray.Length > 0) 1861 { 1862 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1863 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1864 1865 // 分配参数值 1866 AssignParameterValues(commandParameters, dataRow); 1867 1868 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); 1869 } 1870 else 1871 { 1872 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); 1873 } 1874 } 1875 1876 /// <summary> 1877 /// 执行指定连接数据库事物的存储过程,使用DataRow做为参数值,返回受影响的行数. 1878 /// </summary> 1879 /// <param name="transaction">一个有效的连接事务 object</param> 1880 /// <param name="spName">存储过程名称</param> 1881 /// <param name="dataRow">使用DataRow作为参数值</param> 1882 /// <returns>返回影响的行数</returns> 1883 public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 1884 { 1885 if (transaction == null) throw new ArgumentNullException("transaction"); 1886 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1887 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1888 1889 // Sf the row has values, the store procedure parameters must be initialized 1890 if (dataRow != null && dataRow.ItemArray.Length > 0) 1891 { 1892 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1893 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1894 1895 // 分配参数值 1896 AssignParameterValues(commandParameters, dataRow); 1897 1898 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); 1899 } 1900 else 1901 { 1902 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); 1903 } 1904 } 1905 #endregion 1906 1907 #region ExecuteDatasetTypedParams 类型化参数(DataRow) 1908 /// <summary> 1909 /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回DataSet. 1910 /// </summary> 1911 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1912 /// <param name="spName">存储过程名称</param> 1913 /// <param name="dataRow">使用DataRow作为参数值</param> 1914 /// <returns>返回一个包含结果集的DataSet.</returns> 1915 public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow) 1916 { 1917 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1918 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1919 1920 //如果row有值,存储过程必须初始化. 1921 if (dataRow != null && dataRow.ItemArray.Length > 0) 1922 { 1923 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1924 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 1925 1926 // 分配参数值 1927 AssignParameterValues(commandParameters, dataRow); 1928 1929 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters); 1930 } 1931 else 1932 { 1933 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); 1934 } 1935 } 1936 1937 /// <summary> 1938 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回DataSet. 1939 /// </summary> 1940 /// <param name="connection">一个有效的数据库连接对象</param> 1941 /// <param name="spName">存储过程名称</param> 1942 /// <param name="dataRow">使用DataRow作为参数值</param> 1943 /// <returns>返回一个包含结果集的DataSet.</returns> 1944 /// 1945 public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow) 1946 { 1947 if (connection == null) throw new ArgumentNullException("connection"); 1948 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1949 1950 // 如果row有值,存储过程必须初始化. 1951 if (dataRow != null && dataRow.ItemArray.Length > 0) 1952 { 1953 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1954 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1955 1956 // 分配参数值 1957 AssignParameterValues(commandParameters, dataRow); 1958 1959 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters); 1960 } 1961 else 1962 { 1963 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName); 1964 } 1965 } 1966 1967 /// <summary> 1968 /// 执行指定连接数据库事务的存储过程,使用DataRow做为参数值,返回DataSet. 1969 /// </summary> 1970 /// <param name="transaction">一个有效的连接事务 object</param> 1971 /// <param name="spName">存储过程名称</param> 1972 /// <param name="dataRow">使用DataRow作为参数值</param> 1973 /// <returns>返回一个包含结果集的DataSet.</returns> 1974 public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 1975 { 1976 if (transaction == null) throw new ArgumentNullException("transaction"); 1977 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1978 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1979 1980 // 如果row有值,存储过程必须初始化. 1981 if (dataRow != null && dataRow.ItemArray.Length > 0) 1982 { 1983 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1984 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1985 1986 // 分配参数值 1987 AssignParameterValues(commandParameters, dataRow); 1988 1989 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters); 1990 } 1991 else 1992 { 1993 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName); 1994 } 1995 } 1996 1997 #endregion 1998 1999 #region ExecuteReaderTypedParams 类型化参数(DataRow) 2000 /// <summary> 2001 /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回DataReader. 2002 /// </summary> 2003 /// <param name="connectionString">一个有效的数据库连接字符串</param> 2004 /// <param name="spName">存储过程名称</param> 2005 /// <param name="dataRow">使用DataRow作为参数值</param> 2006 /// <returns>返回包含结果集的SqlDataReader</returns> 2007 public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow) 2008 { 2009 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 2010 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2011 2012 // 如果row有值,存储过程必须初始化. 2013 if (dataRow != null && dataRow.ItemArray.Length > 0) 2014 { 2015 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2016 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 2017 2018 // 分配参数值 2019 AssignParameterValues(commandParameters, dataRow); 2020 2021 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters); 2022 } 2023 else 2024 { 2025 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName); 2026 } 2027 } 2028 2029 2030 /// <summary> 2031 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回DataReader. 2032 /// </summary> 2033 /// <param name="connection">一个有效的数据库连接对象</param> 2034 /// <param name="spName">存储过程名称</param> 2035 /// <param name="dataRow">使用DataRow作为参数值</param> 2036 /// <returns>返回包含结果集的SqlDataReader</returns> 2037 public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow) 2038 { 2039 if (connection == null) throw new ArgumentNullException("connection"); 2040 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2041 2042 // 如果row有值,存储过程必须初始化. 2043 if (dataRow != null && dataRow.ItemArray.Length > 0) 2044 { 2045 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2046 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 2047 2048 // 分配参数值 2049 AssignParameterValues(commandParameters, dataRow); 2050 2051 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters); 2052 } 2053 else 2054 { 2055 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName); 2056 } 2057 } 2058 2059 /// <summary> 2060 /// 执行指定连接数据库事物的存储过程,使用DataRow做为参数值,返回DataReader. 2061 /// </summary> 2062 /// <param name="transaction">一个有效的连接事务 object</param> 2063 /// <param name="spName">存储过程名称</param> 2064 /// <param name="dataRow">使用DataRow作为参数值</param> 2065 /// <returns>返回包含结果集的SqlDataReader</returns> 2066 public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 2067 { 2068 if (transaction == null) throw new ArgumentNullException("transaction"); 2069 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 2070 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2071 2072 // 如果row有值,存储过程必须初始化. 2073 if (dataRow != null && dataRow.ItemArray.Length > 0) 2074 { 2075 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2076 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 2077 2078 // 分配参数值 2079 AssignParameterValues(commandParameters, dataRow); 2080 2081 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters); 2082 } 2083 else 2084 { 2085 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName); 2086 } 2087 } 2088 #endregion 2089 2090 #region ExecuteScalarTypedParams 类型化参数(DataRow) 2091 /// <summary> 2092 /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回结果集中的第一行第一列. 2093 /// </summary> 2094 /// <param name="connectionString">一个有效的数据库连接字符串</param> 2095 /// <param name="spName">存储过程名称</param> 2096 /// <param name="dataRow">使用DataRow作为参数值</param> 2097 /// <returns>返回结果集中的第一行第一列</returns> 2098 public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow) 2099 { 2100 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 2101 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2102 2103 // 如果row有值,存储过程必须初始化. 2104 if (dataRow != null && dataRow.ItemArray.Length > 0) 2105 { 2106 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2107 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 2108 2109 // 分配参数值 2110 AssignParameterValues(commandParameters, dataRow); 2111 2112 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); 2113 } 2114 else 2115 { 2116 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); 2117 } 2118 } 2119 2120 /// <summary> 2121 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回结果集中的第一行第一列. 2122 /// </summary> 2123 /// <param name="connection">一个有效的数据库连接对象</param> 2124 /// <param name="spName">存储过程名称</param> 2125 /// <param name="dataRow">使用DataRow作为参数值</param> 2126 /// <returns>返回结果集中的第一行第一列</returns> 2127 public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow) 2128 { 2129 if (connection == null) throw new ArgumentNullException("connection"); 2130 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2131 2132 // 如果row有值,存储过程必须初始化. 2133 if (dataRow != null && dataRow.ItemArray.Length > 0) 2134 { 2135 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2136 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 2137 2138 // 分配参数值 2139 AssignParameterValues(commandParameters, dataRow); 2140 2141 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters); 2142 } 2143 else 2144 { 2145 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName); 2146 } 2147 } 2148 2149 /// <summary> 2150 /// 执行指定连接数据库事务的存储过程,使用DataRow做为参数值,返回结果集中的第一行第一列. 2151 /// </summary> 2152 /// <param name="transaction">一个有效的连接事务 object</param> 2153 /// <param name="spName">存储过程名称</param> 2154 /// <param name="dataRow">使用DataRow作为参数值</param> 2155 /// <returns>返回结果集中的第一行第一列</returns> 2156 public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 2157 { 2158 if (transaction == null) throw new ArgumentNullException("transaction"); 2159 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 2160 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2161 2162 // 如果row有值,存储过程必须初始化. 2163 if (dataRow != null && dataRow.ItemArray.Length > 0) 2164 { 2165 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2166 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 2167 2168 // 分配参数值 2169 AssignParameterValues(commandParameters, dataRow); 2170 2171 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters); 2172 } 2173 else 2174 { 2175 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName); 2176 } 2177 } 2178 #endregion 2179 2180 #region ExecuteXmlReaderTypedParams 类型化参数(DataRow) 2181 /// <summary> 2182 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回XmlReader类型的结果集. 2183 /// </summary> 2184 /// <param name="connection">一个有效的数据库连接对象</param> 2185 /// <param name="spName">存储过程名称</param> 2186 /// <param name="dataRow">使用DataRow作为参数值</param> 2187 /// <returns>返回XmlReader结果集对象.</returns> 2188 public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow) 2189 { 2190 if (connection == null) throw new ArgumentNullException("connection"); 2191 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2192 2193 // 如果row有值,存储过程必须初始化. 2194 if (dataRow != null && dataRow.ItemArray.Length > 0) 2195 { 2196 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2197 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 2198 2199 // 分配参数值 2200 AssignParameterValues(commandParameters, dataRow); 2201 2202 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters); 2203 } 2204 else 2205 { 2206 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName); 2207 } 2208 } 2209 2210 /// <summary> 2211 /// 执行指定连接数据库事务的存储过程,使用DataRow做为参数值,返回XmlReader类型的结果集. 2212 /// </summary> 2213 /// <param name="transaction">一个有效的连接事务 object</param> 2214 /// <param name="spName">存储过程名称</param> 2215 /// <param name="dataRow">使用DataRow作为参数值</param> 2216 /// <returns>返回XmlReader结果集对象.</returns> 2217 public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 2218 { 2219 if (transaction == null) throw new ArgumentNullException("transaction"); 2220 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 2221 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2222 2223 // 如果row有值,存储过程必须初始化. 2224 if (dataRow != null && dataRow.ItemArray.Length > 0) 2225 { 2226 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2227 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 2228 2229 // 分配参数值 2230 AssignParameterValues(commandParameters, dataRow); 2231 2232 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters); 2233 } 2234 else 2235 { 2236 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName); 2237 } 2238 } 2239 #endregion 2240 2241 } 2242 2243 /// <summary> 2244 /// SqlHelperParameterCache提供缓存存储过程参数,并能够在运行时从存储过程中探索参数. 2245 /// </summary> 2246 public sealed class SqlHelperParameterCache 2247 { 2248 #region 私有方法,字段,构造函数 2249 // 私有构造函数,妨止类被实例化. 2250 private SqlHelperParameterCache() { } 2251 2252 // 这个方法要注意 2253 private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable()); 2254 2255 /// <summary> 2256 /// 探索运行时的存储过程,返回SqlParameter参数数组. 2257 /// 初始化参数值为 DBNull.Value. 2258 /// </summary> 2259 /// <param name="connection">一个有效的数据库连接</param> 2260 /// <param name="spName">存储过程名称</param> 2261 /// <param name="includeReturnValueParameter">是否包含返回值参数</param> 2262 /// <returns>返回SqlParameter参数数组</returns> 2263 private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter) 2264 { 2265 if (connection == null) throw new ArgumentNullException("connection"); 2266 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2267 2268 SqlCommand cmd = new SqlCommand(spName, connection); 2269 cmd.CommandType = CommandType.StoredProcedure; 2270 2271 connection.Open(); 2272 // 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中. 2273 SqlCommandBuilder.DeriveParameters(cmd); 2274 connection.Close(); 2275 // 如果不包含返回值参数,将参数集中的每一个参数删除. 2276 if (!includeReturnValueParameter) 2277 { 2278 cmd.Parameters.RemoveAt(0); 2279 } 2280 2281 // 创建参数数组 2282 SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; 2283 // 将cmd的Parameters参数集复制到discoveredParameters数组. 2284 cmd.Parameters.CopyTo(discoveredParameters, 0); 2285 2286 // 初始化参数值为 DBNull.Value. 2287 foreach (SqlParameter discoveredParameter in discoveredParameters) 2288 { 2289 discoveredParameter.Value = DBNull.Value; 2290 } 2291 return discoveredParameters; 2292 } 2293 2294 /// <summary> 2295 /// SqlParameter参数数组的深层拷贝. 2296 /// </summary> 2297 /// <param name="originalParameters">原始参数数组</param> 2298 /// <returns>返回一个同样的参数数组</returns> 2299 private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters) 2300 { 2301 SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length]; 2302 2303 for (int i = 0, j = originalParameters.Length; i < j; i++) 2304 { 2305 clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone(); 2306 } 2307 2308 return clonedParameters; 2309 } 2310 2311 #endregion 私有方法,字段,构造函数结束 2312 2313 #region 缓存方法 2314 2315 /// <summary> 2316 /// 追加参数数组到缓存. 2317 /// </summary> 2318 /// <param name="connectionString">一个有效的数据库连接字符串</param> 2319 /// <param name="commandText">存储过程名或SQL语句</param> 2320 /// <param name="commandParameters">要缓存的参数数组</param> 2321 public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters) 2322 { 2323 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 2324 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); 2325 2326 string hashKey = connectionString + ":" + commandText; 2327 2328 paramCache[hashKey] = commandParameters; 2329 } 2330 2331 /// <summary> 2332 /// 从缓存中获取参数数组. 2333 /// </summary> 2334 /// <param name="connectionString">一个有效的数据库连接字符</param> 2335 /// <param name="commandText">存储过程名或SQL语句</param> 2336 /// <returns>参数数组</returns> 2337 public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText) 2338 { 2339 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 2340 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); 2341 2342 string hashKey = connectionString + ":" + commandText; 2343 2344 SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[]; 2345 if (cachedParameters == null) 2346 { 2347 return null; 2348 } 2349 else 2350 { 2351 return CloneParameters(cachedParameters); 2352 } 2353 } 2354 2355 #endregion 缓存方法结束 2356 2357 #region 检索指定的存储过程的参数集 2358 2359 /// <summary> 2360 /// 返回指定的存储过程的参数集 2361 /// </summary> 2362 /// <remarks> 2363 /// 这个方法将查询数据库,并将信息存储到缓存. 2364 /// </remarks> 2365 /// <param name="connectionString">一个有效的数据库连接字符</param> 2366 /// <param name="spName">存储过程名</param> 2367 /// <returns>返回SqlParameter参数数组</returns> 2368 public static SqlParameter[] GetSpParameterSet(string connectionString, string spName) 2369 { 2370 return GetSpParameterSet(connectionString, spName, false); 2371 } 2372 2373 /// <summary> 2374 /// 返回指定的存储过程的参数集 2375 /// </summary> 2376 /// <remarks> 2377 /// 这个方法将查询数据库,并将信息存储到缓存. 2378 /// </remarks> 2379 /// <param name="connectionString">一个有效的数据库连接字符.</param> 2380 /// <param name="spName">存储过程名</param> 2381 /// <param name="includeReturnValueParameter">是否包含返回值参数</param> 2382 /// <returns>返回SqlParameter参数数组</returns> 2383 public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter) 2384 { 2385 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 2386 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2387 2388 using (SqlConnection connection = new SqlConnection(connectionString)) 2389 { 2390 return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter); 2391 } 2392 } 2393 2394 /// <summary> 2395 /// [内部]返回指定的存储过程的参数集(使用连接对象). 2396 /// </summary> 2397 /// <remarks> 2398 /// 这个方法将查询数据库,并将信息存储到缓存. 2399 /// </remarks> 2400 /// <param name="connection">一个有效的数据库连接字符</param> 2401 /// <param name="spName">存储过程名</param> 2402 /// <returns>返回SqlParameter参数数组</returns> 2403 internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName) 2404 { 2405 return GetSpParameterSet(connection, spName, false); 2406 } 2407 2408 /// <summary> 2409 /// [内部]返回指定的存储过程的参数集(使用连接对象) 2410 /// </summary> 2411 /// <remarks> 2412 /// 这个方法将查询数据库,并将信息存储到缓存. 2413 /// </remarks> 2414 /// <param name="connection">一个有效的数据库连接对象</param> 2415 /// <param name="spName">存储过程名</param> 2416 /// <param name="includeReturnValueParameter"> 2417 /// 是否包含返回值参数 2418 /// </param> 2419 /// <returns>返回SqlParameter参数数组</returns> 2420 internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter) 2421 { 2422 if (connection == null) throw new ArgumentNullException("connection"); 2423 using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone()) 2424 { 2425 return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter); 2426 } 2427 } 2428 2429 /// <summary> 2430 /// [私有]返回指定的存储过程的参数集(使用连接对象) 2431 /// </summary> 2432 /// <param name="connection">一个有效的数据库连接对象</param> 2433 /// <param name="spName">存储过程名</param> 2434 /// <param name="includeReturnValueParameter">是否包含返回值参数</param> 2435 /// <returns>返回SqlParameter参数数组</returns> 2436 private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter) 2437 { 2438 if (connection == null) throw new ArgumentNullException("connection"); 2439 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2440 2441 string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : ""); 2442 2443 SqlParameter[] cachedParameters; 2444 2445 cachedParameters = paramCache[hashKey] as SqlParameter[]; 2446 if (cachedParameters == null) 2447 { 2448 SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter); 2449 paramCache[hashKey] = spParameters; 2450 cachedParameters = spParameters; 2451 } 2452 2453 return CloneParameters(cachedParameters); 2454 } 2455 2456 #endregion 参数集检索结束 2457 2458 }