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