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