微软原版SQLHelper类
SQLHelper类：
代码
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 }
2614
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 }
2614
