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