About DAAB Operation No.1
MAAB
常看到有朋友在坛子里问关于SQL SERVER & ORACLE 如何连接操作，哪里有相应的类库，是否有源码。我这边收集了一些，希望能对大家有所帮助。（这可是我的处女贴哦～）
SQLHelper — 当前使用最广泛的SQL SERVER操作类，转自MS MAAB
1
//===============================================================================
2// Microsoft Data Access Application Block for .NET
3// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
4//
5// SQLHelper.cs
6//
7// This file contains a preliminary implementations of the SqlHelper and
8// SqlHelperParameterCache classes.
9//
10// For more information see the Data Access Application Block Implementation Overview.
11//
12//===============================================================================
13// Copyright (C) 2000-2001 Microsoft Corporation
14// All rights reserved.
15// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
16// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
17// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
18// FITNESS FOR A PARTICULAR PURPOSE.
19//==============================================================================
20
21using System;
22using System.Data;
23using System.Xml;
24using System.Data.SqlClient;
25using System.Collections;
26
27
28namespace Microsoft.ApplicationBlocks.Data
29
{
30
/**//// <summary>
31
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
32 /// common uses of SqlClient.
33
/// </summary>
34 public sealed class SqlHelper
35 {
36 private utility methods & constructors#region private utility methods & constructors
37
38 //Since this class provides only static methods, make the default constructor private to prevent
39 //instances from being created with "new SqlHelper()".
40 private SqlHelper() {}
41
42
43
44 /**//// <summary>
45 /// This method is used to attach array's of SqlParameters to a SqlCommand.
46 ///
47 /// This method will assign a value of DbNull to any parameter with a direction of
48 /// InputOutput and a value of null.
49 ///
50 /// This behavior will prevent default values from being used, but
51 /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
52 /// where the user provided no input value.
53 /// </summary>
54 /// <param name="command">The command to which the parameters will be added</param>
55 /// <param name="commandParameters">an array of SqlParameters tho be added to command</param>
56 private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
57 {
58 foreach (SqlParameter p in commandParameters)
59 {
60 //check for derived output value with no value assigned
61 if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
62 {
63 p.Value = DBNull.Value;
64 }
65
66 command.Parameters.Add(p);
67 }
68 }
69
70 /**//// <summary>
71 /// This method assigns an array of values to an array of SqlParameters.
72 /// </summary>
73 /// <param name="commandParameters">array of SqlParameters to be assigned values</param>
74 /// <param name="parameterValues">array of objects holding the values to be assigned</param>
75 private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
76 {
77 if ((commandParameters == null) || (parameterValues == null))
78 {
79 //do nothing if we get no data
80 return;
81 }
82
83 // we must have the same number of values as we pave parameters to put them in
84 if (commandParameters.Length != parameterValues.Length)
85 {
86 throw new ArgumentException("Parameter count does not match Parameter Value count.");
87 }
88
89 //iterate through the SqlParameters, assigning the values from the corresponding position in the
90 //value array
91 for (int i = 0, j = commandParameters.Length; i < j; i++)
92 {
93 commandParameters[i].Value = parameterValues[i];
94 }
95 }
96
97
98 /**//// <summary>
99 /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
100 /// to the provided command.
101 /// </summary>
102 /// <param name="command">the SqlCommand to be prepared</param>
103 /// <param name="connection">a valid SqlConnection, on which to execute this command</param>
104 /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
105 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
106 /// <param name="commandText">the stored procedure name or T-SQL command</param>
107 /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
108 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
109 {
110 //if the provided connection is not open, we will open it
111 if (connection.State != ConnectionState.Open)
112 {
113 connection.Open();
114 }
115
116 //associate the connectoin with the command
117 command.Connection = connection;
118
119 //set the command text (stored procedure name or SQL statement)
120 command.CommandText = commandText;
121
122 //if we were provided a transaction, assign it.
123 if (transaction != null)
124 {
125 command.Transaction = transaction;
126 }
127
128 //set the command type
129 command.CommandType = commandType;
130
131 //attach the command parameters if they are provided
132 if (commandParameters != null)
133 {
134 AttachParameters(command, commandParameters);
135 }
136
137 return;
138 }
139
140
141 #endregion private utility methods & constructors
142
143 ExecuteNonQuery#region ExecuteNonQuery
144
145 /**//// <summary>
146 /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
147 /// the connection string.
148 /// </summary>
149 /// <remarks>
150 /// e.g.:
151 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
152 /// </remarks>
153 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
154 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
155 /// <param name="commandText">the stored procedure name or T-SQL command</param>
156 /// <returns>an int representing the number of rows affected by the command</returns>
157 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
158 {
159 //pass through the call providing null for the set of SqlParameters
160 return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
161 }
162
163 /**//// <summary>
164 /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
165 /// using the provided parameters.
166 /// </summary>
167 /// <remarks>
168 /// e.g.:
169 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
170 /// </remarks>
171 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
172 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
173 /// <param name="commandText">the stored procedure name or T-SQL command</param>
174 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
175 /// <returns>an int representing the number of rows affected by the command</returns>
176 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
177 {
178 //create & open a SqlConnection, and dispose of it after we are done.
179 using (SqlConnection cn = new SqlConnection(connectionString))
180 {
181 cn.Open();
182
183 //call the overload that takes a connection in place of the connection string
184 return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
185 }
186 }
187
188 /**//// <summary>
189 /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
190 /// the conneciton string using the provided parameter values. This method will discover the parameters for the
191 /// stored procedure, and assign the values based on parameter order.
192 /// </summary>
193 /// <remarks>
194 /// This method provides no access to output parameters or the stored procedure's return value parameter.
195 ///
196 /// e.g.:
197 /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
198 /// </remarks>
199 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
200 /// <param name="spName">the name of the stored prcedure</param>
201 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
202 /// <returns>an int representing the number of rows affected by the command</returns>
203 public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
204 {
205 //if we got parameter values, we need to figure out where they go
206 if ((parameterValues != null) && (parameterValues.Length > 0))
207 {
208 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
209 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
210
211 //assign the provided values to these parameters based on parameter order
212 AssignParameterValues(commandParameters, parameterValues);
213
214 //call the overload that takes an array of SqlParameters
215 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
216 }
217 //otherwise we can just call the SP without params
218 else
219 {
220 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
221 }
222 }
223
224 /**//// <summary>
225 /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
226 /// </summary>
227 /// <remarks>
228 /// e.g.:
229 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
230 /// </remarks>
231 /// <param name="connection">a valid SqlConnection</param>
232 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
233 /// <param name="commandText">the stored procedure name or T-SQL command</param>
234 /// <returns>an int representing the number of rows affected by the command</returns>
235 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
236 {
237 //pass through the call using a null transaction value
238 return ExecuteNonQuery(connection, null, commandType, commandText);
239 }
240
241 /**//// <summary>
242 /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
243 /// using the provided parameters.
244 /// </summary>
245 /// <remarks>
246 /// e.g.:
247 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
248 /// </remarks>
249 /// <param name="connection">a valid SqlConnection</param>
250 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
251 /// <param name="commandText">the stored procedure name or T-SQL command</param>
252 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
253 /// <returns>an int representing the number of rows affected by the command</returns>
254 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
255 {
256 //pass through the call using a null transaction value
257 return ExecuteNonQuery(connection, null, commandType, commandText, commandParameters);
258 }
259
260 /**//// <summary>
261 /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
262 /// using the provided parameter values. This method will discover the parameters for the
263 /// stored procedure, and assign the values based on parameter order.
264 /// </summary>
265 /// <remarks>
266 /// This method provides no access to output parameters or the stored procedure's return value parameter.
267 ///
268 /// e.g.:
269 /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
270 /// </remarks>
271 /// <param name="connection">a valid SqlConnection</param>
272 /// <param name="spName">the name of the stored prcedure</param>
273 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
274 /// <returns>an int representing the number of rows affected by the command</returns>
275 public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
276 {
277 //pass through the call using a null transaction value
278 return ExecuteNonQuery(connection, null, spName, parameterValues);
279 }
280
281 //these three method overloads currently take both connection and transaction. In post-beta2 builds, only
282 //transaction will need to be passed in, and the .Connection property will be available from that transaction
283
284 /**//// <summary>
285 /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection
286 /// and SqlTransaction.
287 /// </summary>
288 /// <remarks>
289 /// e.g.:
290 /// int result = ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "PublishOrders");
291 /// </remarks>
292 /// <param name="connection">a valid SqlConnection</param>
293 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
294 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
295 /// <param name="commandText">the stored procedure name or T-SQL command</param>
296 /// <returns>an int representing the number of rows affected by the command</returns>
297 public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)
298 {
299 //pass through the call providing null for the set of SqlParameters
300 return ExecuteNonQuery(connection, transaction, commandType, commandText, (SqlParameter[])null);
301 }
302
303 /**//// <summary>
304 /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection and SqlTransaction
305 /// using the provided parameters.
306 /// </summary>
307 /// <remarks>
308 /// e.g.:
309 /// int result = ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
310 /// </remarks>
311 /// <param name="connection">a valid SqlConnection</param>
312 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
313 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
314 /// <param name="commandText">the stored procedure name or T-SQL command</param>
315 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
316 /// <returns>an int representing the number of rows affected by the command</returns>
317 public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
318 {
319 //create a command and prepare it for execution
320 SqlCommand cmd = new SqlCommand();
321 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
322
323 //finally, execute the command.
324 return cmd.ExecuteNonQuery();
325 }
326
327 /**//// <summary>
328 /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
329 /// and SqlTransaction using the provided parameter values. This method will discover the parameters for the
330 /// stored procedure, and assign the values based on parameter order.
331 /// </summary>
332 /// <remarks>
333 /// This method provides no access to output parameters or the stored procedure's return value parameter.
334 ///
335 /// e.g.:
336 /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
337 /// </remarks>
338 /// <param name="connection">a valid SqlConnection</param>
339 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
340 /// <param name="spName">the name of the stored prcedure</param>
341 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
342 /// <returns>an int representing the number of rows affected by the command</returns>
343 public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
344 {
345 //if we got parameter values, we need to figure out where they go
346 if ((parameterValues != null) && (parameterValues.Length > 0))
347 {
348 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
349 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
350
351 //assign the provided values to these parameters based on parameter order
352 AssignParameterValues(commandParameters, parameterValues);
353
354 //call the overload that takes an array of SqlParameters
355 return ExecuteNonQuery(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);
356 }
357 //otherwise we can just call the SP without params
358 else
359 {
360 return ExecuteNonQuery(connection, transaction, CommandType.StoredProcedure, spName);
361 }
362 }
363
364
365 #endregion ExecuteNonQuery
366
367 ExecuteDataSet#region ExecuteDataSet
368
369 /**//// <summary>
370 /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
371 /// the connection string.
372 /// </summary>
373 /// <remarks>
374 /// e.g.:
375 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
376 /// </remarks>
377 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
378 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
379 /// <param name="commandText">the stored procedure name or T-SQL command</param>
380 /// <returns>a dataset containing the resultset generated by the command</returns>
381 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
382 {
383 //pass through the call providing null for the set of SqlParameters
384 return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
385 }
386
387 /**//// <summary>
388 /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
389 /// using the provided parameters.
390 /// </summary>
391 /// <remarks>
392 /// e.g.:
393 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
394 /// </remarks>
395 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
396 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
397 /// <param name="commandText">the stored procedure name or T-SQL command</param>
398 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
399 /// <returns>a dataset containing the resultset generated by the command</returns>
400 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
401 {
402 //create & open a SqlConnection, and dispose of it after we are done.
403 using (SqlConnection cn = new SqlConnection(connectionString))
404 {
405 cn.Open();
406
407 //call the overload that takes a connection in place of the connection string
408 return ExecuteDataset(cn, commandType, commandText, commandParameters);
409 }
410 }
411
412 /**//// <summary>
413 /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
414 /// the conneciton string using the provided parameter values. This method will discover the parameters for the
415 /// stored procedure, and assign the values based on parameter order.
416 /// </summary>
417 /// <remarks>
418 /// This method provides no access to output parameters or the stored procedure's return value parameter.
419 ///
420 /// e.g.:
421 /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
422 /// </remarks>
423 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
424 /// <param name="spName">the name of the stored prcedure</param>
425 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
426 /// <returns>a dataset containing the resultset generated by the command</returns>
427 public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
428 {
429 //if we got parameter values, we need to figure out where they go
430 if ((parameterValues != null) && (parameterValues.Length > 0))
431 {
432 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
433 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
434
435 //assign the provided values to these parameters based on parameter order
436 AssignParameterValues(commandParameters, parameterValues);
437
438 //call the overload that takes an array of SqlParameters
439 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
440 }
441 //otherwise we can just call the SP without params
442 else
443 {
444 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
445 }
446 }
447
448 /**//// <summary>
449 /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
450 /// </summary>
451 /// <remarks>
452 /// e.g.:
453 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
454 /// </remarks>
455 /// <param name="connection">a valid SqlConnection</param>
456 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
457 /// <param name="commandText">the stored procedure name or T-SQL command</param>
458 /// <returns>a dataset containing the resultset generated by the command</returns>
459 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
460 {
461 //pass through the call using a null transaction value
462 return ExecuteDataset(connection, null, commandType, commandText);
463 }
464
465
466 /**//// <summary>
467 /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
468 /// using the provided parameters.
469 /// </summary>
470 /// <remarks>
471 /// e.g.:
472 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
473 /// </remarks>
474 /// <param name="connection">a valid SqlConnection</param>
475 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
476 /// <param name="commandText">the stored procedure name or T-SQL command</param>
477 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
478 /// <returns>a dataset containing the resultset generated by the command</returns>
479 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
480 {
481 //pass through the call using a null transaction value
482 return ExecuteDataset(connection, null, commandType, commandText, commandParameters);
483 }
484
485
486 /**//// <summary>
487 /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
488 /// using the provided parameter values. This method will discover the parameters for the
489 /// stored procedure, and assign the values based on parameter order.
490 /// </summary>
491 /// <remarks>
492 /// This method provides no access to output parameters or the stored procedure's return value parameter.
493 ///
494 /// e.g.:
495 /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
496 /// </remarks>
497 /// <param name="connection">a valid SqlConnection</param>
498 /// <param name="spName">the name of the stored prcedure</param>
499 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
500 /// <returns>a dataset containing the resultset generated by the command</returns>
501 public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
502 {
503 //pass through the call using a null transaction value
504 return ExecuteDataset(connection, null, spName, parameterValues);
505 }
506
507 //these three method overloads currently take both connection and transaction. In post-beta2 builds, only
508 //transaction will need to be passed in, and the .Connection property will be available from that transaction
509
510 /**//// <summary>
511 /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection
512 /// and SqlTransaction.
513 /// </summary>
514 /// <remarks>
515 /// e.g.:
516 /// DataSet ds = ExecuteDataset(conn, trans, CommandType.StoredProcedure, "GetOrders");
517 /// </remarks>
518 /// <param name="connection">a valid SqlConnection</param>
519 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
520 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
521 /// <param name="commandText">the stored procedure name or T-SQL command</param>
522 /// <returns>a dataset containing the resultset generated by the command</returns>
523 public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)
524 {
525 //pass through the call providing null for the set of SqlParameters
526 return ExecuteDataset(connection, transaction, commandType, commandText, (SqlParameter[])null);
527 }
528
529 /**//// <summary>
530 /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection and SqlTransaction
531 /// using the provided parameters.
532 /// </summary>
533 /// <remarks>
534 /// e.g.:
535 /// DataSet ds = ExecuteDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
536 /// </remarks>
537 /// <param name="connection">a valid SqlConnection</param>
538 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
539 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
540 /// <param name="commandText">the stored procedure name or T-SQL command</param>
541 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
542 /// <returns>a dataset containing the resultset generated by the command</returns>
543 public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
544 {
545 //create a command and prepare it for execution
546 SqlCommand cmd = new SqlCommand();
547 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
548
549 //create the DataAdapter & DataSet
550 SqlDataAdapter da = new SqlDataAdapter(cmd);
551 DataSet ds = new DataSet();
552
553 //fill the DataSet using default values for DataTable names, etc.
554 da.Fill(ds);
555
556 //return the dataset
557 return ds;
558 }
559
560 /**//// <summary>
561 /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
562 /// and SqlTransaction using the provided parameter values. This method will discover the parameters for the
563 /// stored procedure, and assign the values based on parameter order.
564 /// </summary>
565 /// <remarks>
566 /// This method provides no access to output parameters or the stored procedure's return value parameter.
567 ///
568 /// e.g.:
569 /// DataSet ds = ExecuteDataset(conn, trans, "GetOrders", 24, 36);
570 /// </remarks>
571 /// <param name="connection">a valid SqlConnection</param>
572 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
573 /// <param name="spName">the name of the stored prcedure</param>
574 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
575 /// <returns>a dataset containing the resultset generated by the command</returns>
576 public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
577 {
578 //if we got parameter values, we need to figure out where they go
579 if ((parameterValues != null) && (parameterValues.Length > 0))
580 {
581 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
582 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
583
584 //assign the provided values to these parameters based on parameter order
585 AssignParameterValues(commandParameters, parameterValues);
586
587 //call the overload that takes an array of SqlParameters
588 return ExecuteDataset(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);
589 }
590 //otherwise we can just call the SP without params
591 else
592 {
593 return ExecuteDataset(connection, transaction, CommandType.StoredProcedure, spName);
594 }
595 }
596
597 #endregion ExecuteDataSet
598
599 ExecuteReader#region ExecuteReader
600
601 /**//// <summary>
602 /// this enum is used to indicate weather the connection was provided by the caller, or created by SqlHelper, so that
603 /// we can set the appropriate CommandBehavior when calling ExecuteReader()
604 /// </summary>
605 private enum SqlConnectionOwnership
606 {
607 /**//// <summary>Connection is owned and managed by SqlHelper</summary>
608 Internal,
609 /**//// <summary>Connection is owned and managed by the caller</summary>
610 External
611 }
612
613 /**//// <summary>
614 /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
615 /// </summary>
616 /// <remarks>
617 /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
618 ///
619 /// If the caller provided the connection, we want to leave it to them to manage.
620 /// </remarks>
621 /// <param name="connection">a valid SqlConnection, on which to execute this command</param>
622 /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
623 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
624 /// <param name="commandText">the stored procedure name or T-SQL command</param>
625 /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
626 /// <param name="connectionOwnership">indicates weather the connection parameter was provided by the caller, or created by SqlHelper</param>
627 /// <returns>SqlDataReader containing the results of the command</returns>
628 private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
629 {
630 //create a command and prepare it for execution
631 SqlCommand cmd = new SqlCommand();
632 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
633
634 //create a reader
635 SqlDataReader dr;
636
637 // call ExecuteReader with the appropriate CommandBehavior
638 if (connectionOwnership == SqlConnectionOwnership.External)
639 {
640 dr = cmd.ExecuteReader();
641 }
642 else
643 {
644 dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
645 }
646
647 return dr;
648 }
649
650
651 /**//// <summary>
652 /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
653 /// the connection string.
654 /// </summary>
655 /// <remarks>
656 /// e.g.:
657 /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
658 /// </remarks>
659 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
660 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
661 /// <param name="commandText">the stored procedure name or T-SQL command</param>
662 /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
663 public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
664 {
665 //pass through the call providing null for the set of SqlParameters
666 return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
667 }
668
669
670 /**//// <summary>
671 /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
672 /// using the provided parameters.
673 /// </summary>
674 /// <remarks>
675 /// e.g.:
676 /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
677 /// </remarks>
678 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
679 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
680 /// <param name="commandText">the stored procedure name or T-SQL command</param>
681 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
682 /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
683 public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
684 {
685 //create & open a SqlConnection
686 SqlConnection cn = new SqlConnection(connectionString);
687 cn.Open();
688
689 try
690 {
691 //call the private overload that takes an internally owned connection in place of the connection string
692 return ExecuteReader(cn, null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal);
693 }
694 catch
695 {
696 //if we fail to return the SqlDatReader, we neeed to close the connection ourselves
697 cn.Close();
698 throw;
699 }
700 }
701
702 /**//// <summary>
703 /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
704 /// the conneciton string using the provided parameter values. This method will discover the parameters for the
705 /// stored procedure, and assign the values based on parameter order.
706 /// </summary>
707 /// <remarks>
708 /// This method provides no access to output parameters or the stored procedure's return value parameter.
709 ///
710 /// e.g.:
711 /// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
712 /// </remarks>
713 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
714 /// <param name="spName">the name of the stored prcedure</param>
715 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
716 /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
717 public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
718 {
719 //if we got parameter values, we need to figure out where they go
720 if ((parameterValues != null) && (parameterValues.Length > 0))
721 {
722 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
723 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
724
725 //assign the provided values to these parameters based on parameter order
726 AssignParameterValues(commandParameters, parameterValues);
727
728 //call the overload that takes an array of SqlParameters
729 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
730 }
731 //otherwise we can just call the SP without params
732 else
733 {
734 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
735 }
736 }
737
738 /**//// <summary>
739 /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
740 /// </summary>
741 /// <remarks>
742 /// e.g.:
743 /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
744 /// </remarks>
745 /// <param name="connection">a valid SqlConnection</param>
746 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
747 /// <param name="commandText">the stored procedure name or T-SQL command</param>
748 /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
749 public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
750 {
751 //pass through the call using a null transaction value
752 return ExecuteReader(connection, null, commandType, commandText);
753 }
754
755 /**//// <summary>
756 /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
757 /// using the provided parameters.
758 /// </summary>
759 /// <remarks>
760 /// e.g.:
761 /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
762 /// </remarks>
763 /// <param name="connection">a valid SqlConnection</param>
764 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
765 /// <param name="commandText">the stored procedure name or T-SQL command</param>
766 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
767 /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
768 public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
769 {
770 //pass through the call using a null transaction value
771 return ExecuteReader(connection, null, commandType, commandText, commandParameters);
772 }
773
774 /**//// <summary>
775 /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
776 /// using the provided parameter values. This method will discover the parameters for the
777 /// stored procedure, and assign the values based on parameter order.
778 /// </summary>
779 /// <remarks>
780 /// This method provides no access to output parameters or the stored procedure's return value parameter.
781 ///
782 /// e.g.:
783 /// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
784 /// </remarks>
785 /// <param name="connection">a valid SqlConnection</param>
786 /// <param name="spName">the name of the stored prcedure</param>
787 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
788 /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
789 public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
790 {
791 //pass through the call using a null transaction value
792 return ExecuteReader(connection, null, spName, parameterValues);
793 }
794
795 //these three method overloads currently take both connection and transaction. In post-beta2 builds, only
796 //transaction will need to be passed in, and the .Connection property will be available from that transaction
797
798 /**//// <summary>
799 /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection
800 /// and SqlTransaction.
801 /// </summary>
802 /// <remarks>
803 /// e.g.:
804 /// SqlDataReader dr = ExecuteReader(conn, trans, CommandType.StoredProcedure, "GetOrders");
805 /// </remarks>
806 /// <param name="connection">a valid SqlConnection</param>
807 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
808 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
809 /// <param name="commandText">the stored procedure name or T-SQL command</param>
810 /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
811 public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)
812 {
813 //pass through the call providing null for the set of SqlParameters
814 return ExecuteReader(connection, transaction, commandType, commandText, (SqlParameter[])null);
815 }
816
817 /**//// <summary>
818 /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection and SqlTransaction
819 /// using the provided parameters.
820 /// </summary>
821 /// <remarks>
822 /// e.g.:
823 /// SqlDataReader dr = ExecuteReader(conn, trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
824 /// </remarks>
825 /// <param name="connection">a valid SqlConnection</param>
826 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
827 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
828 /// <param name="commandText">the stored procedure name or T-SQL command</param>
829 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
830 /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
831 public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
832 {
833 //pass through to private overload, indicating that the connection is owned by the caller
834 return ExecuteReader(connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
835 }
836
837 /**//// <summary>
838 /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
839 /// and SqlTransaction using the provided parameter values. This method will discover the parameters for the
840 /// stored procedure, and assign the values based on parameter order.
841 /// </summary>
842 /// <remarks>
843 /// This method provides no access to output parameters or the stored procedure's return value parameter.
844 ///
845 /// e.g.:
846 /// SqlDataReader dr = ExecuteReader(conn, trans, "GetOrders", 24, 36);
847 /// </remarks>
848 /// <param name="connection">a valid SqlConnection</param>
849 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
850 /// <param name="spName">the name of the stored prcedure</param>
851 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
852 /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
853 public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
854 {
855 //if we got parameter values, we need to figure out where they go
856 if ((parameterValues != null) && (parameterValues.Length > 0))
857 {
858 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
859
860 AssignParameterValues(commandParameters, parameterValues);
861
862 return ExecuteReader(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);
863 }
864 //otherwise we can just call the SP without params
865 else
866 {
867 return ExecuteReader(connection, transaction, CommandType.StoredProcedure, spName);
868 }
869 }
870
871 #endregion ExecuteReader
872
873 ExecuteScalar#region ExecuteScalar
874
875 /**//// <summary>
876 /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in
877 /// the connection string.
878 /// </summary>
879 /// <remarks>
880 /// e.g.:
881 /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
882 /// </remarks>
883 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
884 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
885 /// <param name="commandText">the stored procedure name or T-SQL command</param>
886 /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
887 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
888 {
889 //pass through the call providing null for the set of SqlParameters
890 return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
891 }
892
893 /**//// <summary>
894 /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
895 /// using the provided parameters.
896 /// </summary>
897 /// <remarks>
898 /// e.g.:
899 /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
900 /// </remarks>
901 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
902 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
903 /// <param name="commandText">the stored procedure name or T-SQL command</param>
904 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
905 /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
906 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
907 {
908 //create & open a SqlConnection, and dispose of it after we are done.
909 using (SqlConnection cn = new SqlConnection(connectionString))
910 {
911 cn.Open();
912
913 //call the overload that takes a connection in place of the connection string
914 return ExecuteScalar(cn, commandType, commandText, commandParameters);
915 }
916 }
917
918 /**//// <summary>
919 /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in
920 /// the conneciton string using the provided parameter values. This method will discover the parameters for the
921 /// stored procedure, and assign the values based on parameter order.
922 /// </summary>
923 /// <remarks>
924 /// This method provides no access to output parameters or the stored procedure's return value parameter.
925 ///
926 /// e.g.:
927 /// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
928 /// </remarks>
929 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
930 /// <param name="spName">the name of the stored prcedure</param>
931 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
932 /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
933 public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
934 {
935 //if we got parameter values, we need to figure out where they go
936 if ((parameterValues != null) && (parameterValues.Length > 0))
937 {
938 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
939 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
940
941 //assign the provided values to these parameters based on parameter order
942 AssignParameterValues(commandParameters, parameterValues);
943
944 //call the overload that takes an array of SqlParameters
945 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
946 }
947 //otherwise we can just call the SP without params
948 else
949 {
950 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
951 }
952 }
953
954 /**//// <summary>
955 /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.
956 /// </summary>
957 /// <remarks>
958 /// e.g.:
959 /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
960 /// </remarks>
961 /// <param name="connection">a valid SqlConnection</param>
962 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
963 /// <param name="commandText">the stored procedure name or T-SQL command</param>
964 /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
965 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
966 {
967 //pass through the call using a null transaction value
968 return ExecuteScalar(connection, null, commandType, commandText);
969 }
970
971 /**//// <summary>
972 /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
973 /// using the provided parameters.
974 /// </summary>
975 /// <remarks>
976 /// e.g.:
977 /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
978 /// </remarks>
979 /// <param name="connection">a valid SqlConnection</param>
980 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
981 /// <param name="commandText">the stored procedure name or T-SQL command</param>
982 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
983 /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
984 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
985 {
986 //pass through the call using a null transaction value
987 return ExecuteScalar(connection, null, commandType, commandText, commandParameters);
988 }
989
990 /**//// <summary>
991 /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
992 /// using the provided parameter values. This method will discover the parameters for the
993 /// stored procedure, and assign the values based on parameter order.
994 /// </summary>
995 /// <remarks>
996 /// This method provides no access to output parameters or the stored procedure's return value parameter.
997 ///
998 /// e.g.:
999 /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
1000 /// </remarks>
1001 /// <param name="connection">a valid SqlConnection</param>
1002 /// <param name="spName">the name of the stored prcedure</param>
1003 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
1004 /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
1005 public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
1006 {
1007 //pass through the call using a null transaction value
1008 return ExecuteScalar(connection, null, spName, parameterValues);
1009 }
1010
1011 //these three method overloads currently take both connection and transaction. In post-beta2 builds, only
1012 //transaction will need to be passed in, and the .Connection property will be available from that transaction
1013
1014 /**//// <summary>
1015 /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection
1016 /// and SqlTransaction.
1017 /// </summary>
1018 /// <remarks>
1019 /// e.g.:
1020 /// int orderCount = (int)ExecuteScalar(conn, trans, CommandType.StoredProcedure, "GetOrderCount");
1021 /// </remarks>
1022 /// <param name="connection">a valid SqlConnection</param>
1023 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
1024 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
1025 /// <param name="commandText">the stored procedure name or T-SQL command</param>
1026 /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
1027 public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)
1028 {
1029 //pass through the call providing null for the set of SqlParameters
1030 return ExecuteScalar(connection, transaction, commandType, commandText, (SqlParameter[])null);
1031 }
1032
1033 /**//// <summary>
1034 /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection and SqlTransaction
1035 /// using the provided parameters.
1036 /// </summary>
1037 /// <remarks>
1038 /// e.g.:
1039 /// int orderCount = (int)ExecuteScalar(conn, trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
1040 /// </remarks>
1041 /// <param name="connection">a valid SqlConnection</param>
1042 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
1043 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
1044 /// <param name="commandText">the stored procedure name or T-SQL command</param>
1045 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
1046 /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
1047 public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1048 {
1049 //create a command and prepare it for execution
1050 SqlCommand cmd = new SqlCommand();
1051 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
1052
1053 //execute the command & return the results
1054 return cmd.ExecuteScalar();
1055
1056 }
1057
1058 /**//// <summary>
1059 /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
1060 /// and SqlTransaction using the provided parameter values. This method will discover the parameters for the
1061 /// stored procedure, and assign the values based on parameter order.
1062 /// </summary>
1063 /// <remarks>
1064 /// This method provides no access to output parameters or the stored procedure's return value parameter.
1065 ///
1066 /// e.g.:
1067 /// int orderCount = (int)ExecuteScalar(conn, trans, "GetOrderCount", 24, 36);
1068 /// </remarks>
1069 /// <param name="connection">a valid SqlConnection</param>
1070 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
1071 /// <param name="spName">the name of the stored prcedure</param>
1072 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
1073 /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
1074 public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
1075 {
1076 //if we got parameter values, we need to figure out where they go
1077 if ((parameterValues != null) && (parameterValues.Length > 0))
1078 {
1079 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
1080 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
1081
1082 //assign the provided values to these parameters based on parameter order
1083 AssignParameterValues(commandParameters, parameterValues);
1084
1085 //call the overload that takes an array of SqlParameters
1086 return ExecuteScalar(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);
1087 }
1088 //otherwise we can just call the SP without params
1089 else
1090 {
1091 return ExecuteScalar(connection, transaction, CommandType.StoredProcedure, spName);
1092 }
1093 }
1094
1095 #endregion ExecuteScalar
1096
1097 ExecuteXmlReader#region ExecuteXmlReader
1098
1099 /**//// <summary>
1100 /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
1101 /// </summary>
1102 /// <remarks>
1103 /// e.g.:
1104 /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
1105 /// </remarks>
1106 /// <param name="connection">a valid SqlConnection</param>
1107 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
1108 /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
1109 /// <returns>an XmlReader containing the resultset generated by the command</returns>
1110 public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
1111 {
1112 //pass through the call using a null transaction value
1113 return ExecuteXmlReader(connection, null, commandType, commandText);
1114 }
1115
1116 /**//// <summary>
1117 /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
1118 /// using the provided parameters.
1119 /// </summary>
1120 /// <remarks>
1121 /// e.g.:
1122 /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
1123 /// </remarks>
1124 /// <param name="connection">a valid SqlConnection</param>
1125 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
1126 /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
1127 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
1128 /// <returns>an XmlReader containing the resultset generated by the command</returns>
1129 public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1130 {
1131 //pass through the call using a null transaction value
1132 return ExecuteXmlReader(connection, null, commandType, commandText, commandParameters);
1133 }
1134
1135 /**//// <summary>
1136 /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
1137 /// using the provided parameter values. This method will discover the parameters for the
1138 /// stored procedure, and assign the values based on parameter order.
1139 /// </summary>
1140 /// <remarks>
1141 /// This method provides no access to output parameters or the stored procedure's return value parameter.
1142 ///
1143 /// e.g.:
1144 /// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
1145 /// </remarks>
1146 /// <param name="connection">a valid SqlConnection</param>
1147 /// <param name="spName">the name of the stored prcedure using "FOR XML AUTO"</param>
1148 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
1149 /// <returns>an XmlReader containing the resultset generated by the command</returns>
1150 public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
1151 {
1152 //pass through the call using a null transaction value
1153 return ExecuteXmlReader(connection, null, spName, parameterValues);
1154 }
1155
1156 //these three method overloads currently take both connection and transaction. In post-beta2 builds, only
1157 //transaction will need to be passed in, and the .Connection property will be available from that transaction
1158
1159 /**//// <summary>
1160 /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection
1161 /// and SqlTransaction.
1162 /// </summary>
1163 /// <remarks>
1164 /// e.g.:
1165 /// XmlReader r = ExecuteXmlReader(conn, trans, CommandType.StoredProcedure, "GetOrders");
1166 /// </remarks>
1167 /// <param name="connection">a valid SqlConnection</param>
1168 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
1169 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
1170 /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
1171 /// <returns>an XmlReader containing the resultset generated by the command</returns>
1172 public static XmlReader ExecuteXmlReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)
1173 {
1174 //pass through the call providing null for the set of SqlParameters
1175 return ExecuteXmlReader(connection, transaction, commandType, commandText, (SqlParameter[])null);
1176 }
1177
1178 /**//// <summary>
1179 /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection and SqlTransaction
1180 /// using the provided parameters.
1181 /// </summary>
1182 /// <remarks>
1183 /// e.g.:
1184 /// XmlReader r = ExecuteXmlReader(conn, trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
1185 /// </remarks>
1186 /// <param name="connection">a valid SqlConnection</param>
1187 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
1188 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
1189 /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
1190 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
1191 /// <returns>an XmlReader containing the resultset generated by the command</returns>
1192 public static XmlReader ExecuteXmlReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1193 {
1194 //create a command and prepare it for execution
1195 SqlCommand cmd = new SqlCommand();
1196 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
1197
1198 //create the DataAdapter & DataSet
1199 return cmd.ExecuteXmlReader();
1200 }
1201
1202 /**//// <summary>
1203 /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
1204 /// and SqlTransaction using the provided parameter values. This method will discover the parameters for the
1205 /// stored procedure, and assign the values based on parameter order.
1206 /// </summary>
1207 /// <remarks>
1208 /// This method provides no access to output parameters or the stored procedure's return value parameter.
1209 ///
1210 /// e.g.:
1211 /// XmlReader r = ExecuteXmlReader(conn, trans, "GetOrders", 24, 36);
1212 /// </remarks>
1213 /// <param name="connection">a valid SqlConnection</param>
1214 /// <param name="transaction">a valid SqlTransaction associated with the connection</param>
1215 /// <param name="spName">the name of the stored prcedure</param>
1216 /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
1217 /// <returns>a dataset containing the resultset generated by the command</returns>
1218 public static XmlReader ExecuteXmlReader(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)
1219 {
1220 //if we got parameter values, we need to figure out where they go
1221 if ((parameterValues != null) && (parameterValues.Length > 0))
1222 {
1223 //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)
1224 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
1225
1226 //assign the provided values to these parameters based on parameter order
1227 AssignParameterValues(commandParameters, parameterValues);
1228
1229 //call the overload that takes an array of SqlParameters
1230 return ExecuteXmlReader(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);
1231 }
1232 //otherwise we can just call the SP without params
1233 else
1234 {
1235 return ExecuteXmlReader(connection, transaction, CommandType.StoredProcedure, spName);
1236 }
1237 }
1238
1239
1240 #endregion ExecuteXmlReader
1241 }
1242
1243 /**//// <summary>
1244 /// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
1245 /// ability to discover parameters for stored procedures at run-time.
1246 /// </summary>
1247 public sealed class SqlHelperParameterCache
1248 {
1249 private methods, variables, and constructors#region private methods, variables, and constructors
1250
1251 //Since this class provides only static methods, make the default constructor private to prevent
1252 //instances from being created with "new SqlHelperParameterCache()".
1253 private SqlHelperParameterCache() {}
1254
1255 //these hashtables are used to map the sp_procedure_params_rowset resultset to the SqlCommand property enum values
1256 private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
1257 private static Hashtable paramTypes = Hashtable.Synchronized(new Hashtable());
1258 private static Hashtable paramDirections = Hashtable.Synchronized(new Hashtable());
1259
1260 /**//// <summary>
1261 /// resolve at run-time the appropriate set of SqlParameters for a stored procedure
1262 /// </summary>
1263 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
1264 /// <param name="spName">the name of the stored prcedure</param>
1265 /// <param name="includeReturnValueParameter">weather or not to onclude ther return value parameter</param>
1266 /// <returns></returns>
1267 private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
1268 {
1269 DataTable paramDescriptions = new DataTable("paramDescriptions");
1270 using (SqlConnection cn = new SqlConnection(connectionString))
1271 {
1272 cn.Open();
1273 SqlCommand cmd = new SqlCommand("sp_procedure_params_rowset",cn);
1274 cmd.CommandType = CommandType.StoredProcedure;
1275 cmd.Parameters.Add("@procedure_name", spName);
1276
1277 SqlDataAdapter da = new SqlDataAdapter(cmd);
1278 da.Fill(paramDescriptions);
1279 }
1280
1281 SqlParameter[] discoveredParameters;
1282
1283 if (paramDescriptions.Rows.Count <= 0)
1284 {
1285 //sp not found - throw exception
1286 throw(new ArgumentException("Stored procedure '" + spName + "' not found", "spName"));
1287 }
1288
1289 int startRow;
1290 if (includeReturnValueParameter)
1291 {
1292 discoveredParameters = new SqlParameter[paramDescriptions.Rows.Count];
1293 startRow = 0;
1294 }
1295 else
1296 {
1297 discoveredParameters = new SqlParameter[paramDescriptions.Rows.Count-1];
1298 startRow = 1;
1299 }
1300
1301 for (int i = 0, j = discoveredParameters.Length; i < j; i++)
1302 {
1303 DataRow paramRow = paramDescriptions.Rows[i + startRow];
1304 discoveredParameters[i] = new SqlParameter();
1305 discoveredParameters[i].ParameterName = (string)paramRow["PARAMETER_NAME"];
1306 discoveredParameters[i].SqlDbType = (SqlDbType)paramTypes[(string)paramRow["TYPE_NAME"]];
1307 discoveredParameters[i].Direction = (ParameterDirection)paramDirections[(short)paramRow["PARAMETER_TYPE"]];
1308 discoveredParameters[i].Size = paramRow["CHARACTER_OCTET_LENGTH"]==DBNull.Value ? 0 : (int)paramRow["CHARACTER_OCTET_LENGTH"];
1309 discoveredParameters[i].Precision = paramRow["NUMERIC_PRECISION"]==DBNull.Value ? (byte)0 : (byte)(short)paramRow["NUMERIC_PRECISION"];
1310 discoveredParameters[i].Scale = paramRow["NUMERIC_SCALE"]==DBNull.Value ? (byte)0 : (byte)(short)paramRow["NUMERIC_SCALE"];
1311 }
1312
1313 return discoveredParameters;
1314 }
1315
1316 static SqlHelperParameterCache()
1317 {
1318 //populate the mapping hashtables
1319 paramTypes.Add("bigint",SqlDbType.BigInt);
1320 paramTypes.Add("binary",SqlDbType.Binary);
1321 paramTypes.Add("bit",SqlDbType.Bit);
1322 paramTypes.Add("char",SqlDbType.Char);
1323 paramTypes.Add("datetime",SqlDbType.DateTime);
1324 paramTypes.Add("decimal",SqlDbType.Decimal);
1325 paramTypes.Add("float",SqlDbType.Float);
1326 paramTypes.Add("image",SqlDbType.Image);
1327 paramTypes.Add("int",SqlDbType.Int);
1328 paramTypes.Add("money",SqlDbType.Money);
1329 paramTypes.Add("nchar",SqlDbType.NChar);
1330 paramTypes.Add("ntext",SqlDbType.NText);
1331 paramTypes.Add("numeric",SqlDbType.Decimal);
1332 paramTypes.Add("nvarchar",SqlDbType.NVarChar);
1333 paramTypes.Add("real",SqlDbType.Real);
1334 paramTypes.Add("smalldatetime",SqlDbType.SmallDateTime);
1335 paramTypes.Add("smallint",SqlDbType.SmallInt);
1336 paramTypes.Add("smallmoney",SqlDbType.SmallMoney);
1337 paramTypes.Add("sql_variant",SqlDbType.Variant);
1338 paramTypes.Add("text",SqlDbType.Text);
1339 paramTypes.Add("timestamp",SqlDbType.Timestamp);
1340 paramTypes.Add("tinyint",SqlDbType.TinyInt);
1341 paramTypes.Add("uniqueidentifier",SqlDbType.UniqueIdentifier);
1342 paramTypes.Add("varbinary",SqlDbType.VarBinary);
1343 paramTypes.Add("varchar",SqlDbType.VarChar);
1344
1345 paramDirections.Add((short)1,ParameterDirection.Input);
1346 paramDirections.Add((short)2,ParameterDirection.InputOutput);
1347 paramDirections.Add((short)4,ParameterDirection.ReturnValue);
1348
1349 }
1350 //deep copy of cached SqlParameter array
1351 private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
1352 {
1353 SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
1354
1355 for (int i = 0, j = originalParameters.Length; i < j; i++)
1356 {
1357 clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
1358 }
1359
1360 return clonedParameters;
1361 }
1362
1363 #endregion private methods, variables, and constructors
1364
1365 caching functions#region caching functions
1366
1367 /**//// <summary>
1368 /// add parameter array to the cache
1369 /// </summary>
1370 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
1371 /// <param name="commandText">the stored procedure name or T-SQL command</param>
1372 /// <param name="commandParameters">an array of SqlParamters to be cached</param>
1373 public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
1374 {
1375 string hashKey = connectionString + ":" + commandText;
1376
1377 paramCache[hashKey] = commandParameters;
1378 }
1379
1380 /**//// <summary>
1381 /// retrieve a parameter array from the cache
1382 /// </summary>
1383 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
1384 /// <param name="commandText">the stored procedure name or T-SQL command</param>
1385 /// <returns>an array of SqlParamters</returns>
1386 public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
1387 {
1388 string hashKey = connectionString + ":" + commandText;
1389
1390 SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];
1391
1392 if (cachedParameters == null)
1393 {
1394 return null;
1395 }
1396 else
1397 {
1398 return CloneParameters(cachedParameters);
1399 }
1400 }
1401
1402 #endregion caching functions
1403
1404 Parameter Discovery Functions#region Parameter Discovery Functions
1405
1406 /**//// <summary>
1407 /// Retrieves the set of SqlParameters appropriate for the stored procedure
1408 /// </summary>
1409 /// <remarks>
1410 /// This method will query the database for this information, and then store it in a cache for future requests.
1411 /// </remarks>
1412 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
1413 /// <param name="spName">the name of the stored prcedure</param>
1414 /// <returns>an array of SqlParameters</returns>
1415 public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
1416 {
1417 return GetSpParameterSet(connectionString, spName, false);
1418 }
1419
1420 /**//// <summary>
1421 /// Retrieves the set of SqlParameters appropriate for the stored procedure
1422 /// </summary>
1423 /// <remarks>
1424 /// This method will query the database for this information, and then store it in a cache for future requests.
1425 /// </remarks>
1426 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
1427 /// <param name="spName">the name of the stored prcedure</param>
1428 /// <param name="includeReturnValueParameter">a bool value indicating weather the return value parameter should be included in the results</param>
1429 /// <returns>an array of SqlParameters</returns>
1430 public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
1431 {
1432 string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter":"");
1433
1434 SqlParameter[] cachedParameters;
1435
1436 cachedParameters = (SqlParameter[])paramCache[hashKey];
1437
1438 if (cachedParameters == null)
1439 {
1440 cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));
1441 }
1442
1443 return CloneParameters(cachedParameters);
1444 }
1445
1446 #endregion Parameter Discovery Functions
1447
1448 }
1449
}
1450
To be continue...
SQLHelper — 当前使用最广泛的SQL SERVER操作类，转自MS MAAB
1
//===============================================================================2
// Microsoft Data Access Application Block for .NET3
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp4
//5
// SQLHelper.cs6
//7
// This file contains a preliminary implementations of the SqlHelper and 8
// SqlHelperParameterCache classes.9
//10
// For more information see the Data Access Application Block Implementation Overview. 11
// 12
//===============================================================================13
// Copyright (C) 2000-2001 Microsoft Corporation14
// All rights reserved.15
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY16
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT17
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR18
// FITNESS FOR A PARTICULAR PURPOSE.19
//==============================================================================20
21
using System;22
using System.Data;23
using System.Xml;24
using System.Data.SqlClient;25
using System.Collections;26
27
28
namespace Microsoft.ApplicationBlocks.Data29
{30
/**//// <summary>31
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for 32
/// common uses of SqlClient.33
/// </summary>34
public sealed class SqlHelper35
{36
private utility methods & constructors#region private utility methods & constructors37
38
//Since this class provides only static methods, make the default constructor private to prevent 39
//instances from being created with "new SqlHelper()".40
private SqlHelper() {}41
42
43
44
/**//// <summary>45
/// This method is used to attach array's of SqlParameters to a SqlCommand.46
/// 47
/// This method will assign a value of DbNull to any parameter with a direction of48
/// InputOutput and a value of null. 49
/// 50
/// This behavior will prevent default values from being used, but51
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)52
/// where the user provided no input value.53
/// </summary>54
/// <param name="command">The command to which the parameters will be added</param>55
/// <param name="commandParameters">an array of SqlParameters tho be added to command</param>56
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)57
{58
foreach (SqlParameter p in commandParameters)59
{60
//check for derived output value with no value assigned61
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))62
{63
p.Value = DBNull.Value;64
}65
66
command.Parameters.Add(p);67
}68
}69
70
/**//// <summary>71
/// This method assigns an array of values to an array of SqlParameters.72
/// </summary>73
/// <param name="commandParameters">array of SqlParameters to be assigned values</param>74
/// <param name="parameterValues">array of objects holding the values to be assigned</param>75
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)76
{77
if ((commandParameters == null) || (parameterValues == null)) 78
{79
//do nothing if we get no data80
return;81
}82
83
// we must have the same number of values as we pave parameters to put them in84
if (commandParameters.Length != parameterValues.Length)85
{86
throw new ArgumentException("Parameter count does not match Parameter Value count.");87
}88
89
//iterate through the SqlParameters, assigning the values from the corresponding position in the 90
//value array91
for (int i = 0, j = commandParameters.Length; i < j; i++)92
{93
commandParameters[i].Value = parameterValues[i];94
}95
}96
97
98
/**//// <summary>99
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 100
/// to the provided command.101
/// </summary>102
/// <param name="command">the SqlCommand to be prepared</param>103
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>104
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>105
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>106
/// <param name="commandText">the stored procedure name or T-SQL command</param>107
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>108
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)109
{110
//if the provided connection is not open, we will open it111
if (connection.State != ConnectionState.Open)112
{113
connection.Open();114
}115
116
//associate the connectoin with the command117
command.Connection = connection;118
119
//set the command text (stored procedure name or SQL statement)120
command.CommandText = commandText;121
122
//if we were provided a transaction, assign it.123
if (transaction != null)124
{125
command.Transaction = transaction;126
}127
128
//set the command type129
command.CommandType = commandType;130
131
//attach the command parameters if they are provided132
if (commandParameters != null)133
{134
AttachParameters(command, commandParameters);135
}136
137
return;138
}139
140
141
#endregion private utility methods & constructors142
143
ExecuteNonQuery#region ExecuteNonQuery144
145
/**//// <summary>146
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in 147
/// the connection string. 148
/// </summary>149
/// <remarks>150
/// e.g.: 151
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");152
/// </remarks>153
/// <param name="connectionString">a valid connection string for a SqlConnection</param>154
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>155
/// <param name="commandText">the stored procedure name or T-SQL command</param>156
/// <returns>an int representing the number of rows affected by the command</returns>157
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)158
{159
//pass through the call providing null for the set of SqlParameters160
return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);161
}162
163
/**//// <summary>164
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 165
/// using the provided parameters.166
/// </summary>167
/// <remarks>168
/// e.g.: 169
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));170
/// </remarks>171
/// <param name="connectionString">a valid connection string for a SqlConnection</param>172
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>173
/// <param name="commandText">the stored procedure name or T-SQL command</param>174
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>175
/// <returns>an int representing the number of rows affected by the command</returns>176
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)177
{178
//create & open a SqlConnection, and dispose of it after we are done.179
using (SqlConnection cn = new SqlConnection(connectionString))180
{181
cn.Open();182
183
//call the overload that takes a connection in place of the connection string184
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);185
}186
}187
188
/**//// <summary>189
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in 190
/// the conneciton string using the provided parameter values. This method will discover the parameters for the 191
/// stored procedure, and assign the values based on parameter order.192
/// </summary>193
/// <remarks>194
/// This method provides no access to output parameters or the stored procedure's return value parameter.195
/// 196
/// e.g.: 197
/// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);198
/// </remarks>199
/// <param name="connectionString">a valid connection string for a SqlConnection</param>200
/// <param name="spName">the name of the stored prcedure</param>201
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>202
/// <returns>an int representing the number of rows affected by the command</returns>203
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)204
{205
//if we got parameter values, we need to figure out where they go206
if ((parameterValues != null) && (parameterValues.Length > 0)) 207
{208
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)209
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);210
211
//assign the provided values to these parameters based on parameter order212
AssignParameterValues(commandParameters, parameterValues);213
214
//call the overload that takes an array of SqlParameters215
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);216
}217
//otherwise we can just call the SP without params218
else 219
{220
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);221
}222
}223
224
/**//// <summary>225
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. 226
/// </summary>227
/// <remarks>228
/// e.g.: 229
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");230
/// </remarks>231
/// <param name="connection">a valid SqlConnection</param>232
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>233
/// <param name="commandText">the stored procedure name or T-SQL command</param>234
/// <returns>an int representing the number of rows affected by the command</returns>235
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)236
{237
//pass through the call using a null transaction value238
return ExecuteNonQuery(connection, null, commandType, commandText);239
}240
241
/**//// <summary>242
/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 243
/// using the provided parameters.244
/// </summary>245
/// <remarks>246
/// e.g.: 247
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));248
/// </remarks>249
/// <param name="connection">a valid SqlConnection</param>250
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>251
/// <param name="commandText">the stored procedure name or T-SQL command</param>252
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>253
/// <returns>an int representing the number of rows affected by the command</returns>254
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)255
{ 256
//pass through the call using a null transaction value257
return ExecuteNonQuery(connection, null, commandType, commandText, commandParameters);258
}259
260
/**//// <summary>261
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection 262
/// using the provided parameter values. This method will discover the parameters for the 263
/// stored procedure, and assign the values based on parameter order.264
/// </summary>265
/// <remarks>266
/// This method provides no access to output parameters or the stored procedure's return value parameter.267
/// 268
/// e.g.: 269
/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);270
/// </remarks>271
/// <param name="connection">a valid SqlConnection</param>272
/// <param name="spName">the name of the stored prcedure</param>273
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>274
/// <returns>an int representing the number of rows affected by the command</returns>275
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)276
{277
//pass through the call using a null transaction value278
return ExecuteNonQuery(connection, null, spName, parameterValues);279
}280
281
//these three method overloads currently take both connection and transaction. In post-beta2 builds, only 282
//transaction will need to be passed in, and the .Connection property will be available from that transaction283
284
/**//// <summary>285
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection286
/// and SqlTransaction. 287
/// </summary>288
/// <remarks>289
/// e.g.: 290
/// int result = ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "PublishOrders");291
/// </remarks>292
/// <param name="connection">a valid SqlConnection</param>293
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>294
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>295
/// <param name="commandText">the stored procedure name or T-SQL command</param>296
/// <returns>an int representing the number of rows affected by the command</returns>297
public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)298
{299
//pass through the call providing null for the set of SqlParameters300
return ExecuteNonQuery(connection, transaction, commandType, commandText, (SqlParameter[])null);301
}302
303
/**//// <summary>304
/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection and SqlTransaction305
/// using the provided parameters.306
/// </summary>307
/// <remarks>308
/// e.g.: 309
/// int result = ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));310
/// </remarks>311
/// <param name="connection">a valid SqlConnection</param>312
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>313
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>314
/// <param name="commandText">the stored procedure name or T-SQL command</param>315
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>316
/// <returns>an int representing the number of rows affected by the command</returns>317
public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)318
{319
//create a command and prepare it for execution320
SqlCommand cmd = new SqlCommand();321
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);322
323
//finally, execute the command.324
return cmd.ExecuteNonQuery();325
}326
327
/**//// <summary>328
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection 329
/// and SqlTransaction using the provided parameter values. This method will discover the parameters for the 330
/// stored procedure, and assign the values based on parameter order.331
/// </summary>332
/// <remarks>333
/// This method provides no access to output parameters or the stored procedure's return value parameter.334
/// 335
/// e.g.: 336
/// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);337
/// </remarks>338
/// <param name="connection">a valid SqlConnection</param>339
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>340
/// <param name="spName">the name of the stored prcedure</param>341
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>342
/// <returns>an int representing the number of rows affected by the command</returns>343
public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)344
{345
//if we got parameter values, we need to figure out where they go346
if ((parameterValues != null) && (parameterValues.Length > 0)) 347
{348
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)349
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);350
351
//assign the provided values to these parameters based on parameter order352
AssignParameterValues(commandParameters, parameterValues);353
354
//call the overload that takes an array of SqlParameters355
return ExecuteNonQuery(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);356
}357
//otherwise we can just call the SP without params358
else 359
{360
return ExecuteNonQuery(connection, transaction, CommandType.StoredProcedure, spName);361
}362
}363
364
365
#endregion ExecuteNonQuery366
367
ExecuteDataSet#region ExecuteDataSet368
369
/**//// <summary>370
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 371
/// the connection string. 372
/// </summary>373
/// <remarks>374
/// e.g.: 375
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");376
/// </remarks>377
/// <param name="connectionString">a valid connection string for a SqlConnection</param>378
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>379
/// <param name="commandText">the stored procedure name or T-SQL command</param>380
/// <returns>a dataset containing the resultset generated by the command</returns>381
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)382
{383
//pass through the call providing null for the set of SqlParameters384
return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);385
}386
387
/**//// <summary>388
/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 389
/// using the provided parameters.390
/// </summary>391
/// <remarks>392
/// e.g.: 393
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));394
/// </remarks>395
/// <param name="connectionString">a valid connection string for a SqlConnection</param>396
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>397
/// <param name="commandText">the stored procedure name or T-SQL command</param>398
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>399
/// <returns>a dataset containing the resultset generated by the command</returns>400
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)401
{402
//create & open a SqlConnection, and dispose of it after we are done.403
using (SqlConnection cn = new SqlConnection(connectionString))404
{405
cn.Open();406
407
//call the overload that takes a connection in place of the connection string408
return ExecuteDataset(cn, commandType, commandText, commandParameters);409
}410
}411
412
/**//// <summary>413
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 414
/// the conneciton string using the provided parameter values. This method will discover the parameters for the 415
/// stored procedure, and assign the values based on parameter order.416
/// </summary>417
/// <remarks>418
/// This method provides no access to output parameters or the stored procedure's return value parameter.419
/// 420
/// e.g.: 421
/// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);422
/// </remarks>423
/// <param name="connectionString">a valid connection string for a SqlConnection</param>424
/// <param name="spName">the name of the stored prcedure</param>425
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>426
/// <returns>a dataset containing the resultset generated by the command</returns>427
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)428
{429
//if we got parameter values, we need to figure out where they go430
if ((parameterValues != null) && (parameterValues.Length > 0)) 431
{432
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)433
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);434
435
//assign the provided values to these parameters based on parameter order436
AssignParameterValues(commandParameters, parameterValues);437
438
//call the overload that takes an array of SqlParameters439
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);440
}441
//otherwise we can just call the SP without params442
else 443
{444
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);445
}446
}447
448
/**//// <summary>449
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 450
/// </summary>451
/// <remarks>452
/// e.g.: 453
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");454
/// </remarks>455
/// <param name="connection">a valid SqlConnection</param>456
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>457
/// <param name="commandText">the stored procedure name or T-SQL command</param>458
/// <returns>a dataset containing the resultset generated by the command</returns>459
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)460
{461
//pass through the call using a null transaction value462
return ExecuteDataset(connection, null, commandType, commandText);463
}464
465
466
/**//// <summary>467
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 468
/// using the provided parameters.469
/// </summary>470
/// <remarks>471
/// e.g.: 472
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));473
/// </remarks>474
/// <param name="connection">a valid SqlConnection</param>475
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>476
/// <param name="commandText">the stored procedure name or T-SQL command</param>477
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>478
/// <returns>a dataset containing the resultset generated by the command</returns>479
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)480
{481
//pass through the call using a null transaction value482
return ExecuteDataset(connection, null, commandType, commandText, commandParameters);483
}484
485
486
/**//// <summary>487
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 488
/// using the provided parameter values. This method will discover the parameters for the 489
/// stored procedure, and assign the values based on parameter order.490
/// </summary>491
/// <remarks>492
/// This method provides no access to output parameters or the stored procedure's return value parameter.493
/// 494
/// e.g.: 495
/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);496
/// </remarks>497
/// <param name="connection">a valid SqlConnection</param>498
/// <param name="spName">the name of the stored prcedure</param>499
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>500
/// <returns>a dataset containing the resultset generated by the command</returns>501
public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)502
{503
//pass through the call using a null transaction value504
return ExecuteDataset(connection, null, spName, parameterValues);505
}506
507
//these three method overloads currently take both connection and transaction. In post-beta2 builds, only 508
//transaction will need to be passed in, and the .Connection property will be available from that transaction509
510
/**//// <summary>511
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection512
/// and SqlTransaction. 513
/// </summary>514
/// <remarks>515
/// e.g.: 516
/// DataSet ds = ExecuteDataset(conn, trans, CommandType.StoredProcedure, "GetOrders");517
/// </remarks>518
/// <param name="connection">a valid SqlConnection</param>519
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>520
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>521
/// <param name="commandText">the stored procedure name or T-SQL command</param>522
/// <returns>a dataset containing the resultset generated by the command</returns>523
public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)524
{525
//pass through the call providing null for the set of SqlParameters526
return ExecuteDataset(connection, transaction, commandType, commandText, (SqlParameter[])null);527
}528
529
/**//// <summary>530
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection and SqlTransaction531
/// using the provided parameters.532
/// </summary>533
/// <remarks>534
/// e.g.: 535
/// DataSet ds = ExecuteDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));536
/// </remarks>537
/// <param name="connection">a valid SqlConnection</param>538
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>539
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>540
/// <param name="commandText">the stored procedure name or T-SQL command</param>541
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>542
/// <returns>a dataset containing the resultset generated by the command</returns>543
public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)544
{545
//create a command and prepare it for execution546
SqlCommand cmd = new SqlCommand();547
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);548
549
//create the DataAdapter & DataSet550
SqlDataAdapter da = new SqlDataAdapter(cmd);551
DataSet ds = new DataSet();552
553
//fill the DataSet using default values for DataTable names, etc.554
da.Fill(ds);555
556
//return the dataset557
return ds;558
}559
560
/**//// <summary>561
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 562
/// and SqlTransaction using the provided parameter values. This method will discover the parameters for the 563
/// stored procedure, and assign the values based on parameter order.564
/// </summary>565
/// <remarks>566
/// This method provides no access to output parameters or the stored procedure's return value parameter.567
/// 568
/// e.g.: 569
/// DataSet ds = ExecuteDataset(conn, trans, "GetOrders", 24, 36);570
/// </remarks>571
/// <param name="connection">a valid SqlConnection</param>572
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>573
/// <param name="spName">the name of the stored prcedure</param>574
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>575
/// <returns>a dataset containing the resultset generated by the command</returns>576
public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)577
{578
//if we got parameter values, we need to figure out where they go579
if ((parameterValues != null) && (parameterValues.Length > 0)) 580
{581
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)582
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);583
584
//assign the provided values to these parameters based on parameter order585
AssignParameterValues(commandParameters, parameterValues);586
587
//call the overload that takes an array of SqlParameters588
return ExecuteDataset(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);589
}590
//otherwise we can just call the SP without params591
else 592
{593
return ExecuteDataset(connection, transaction, CommandType.StoredProcedure, spName);594
}595
}596
597
#endregion ExecuteDataSet598
599
ExecuteReader#region ExecuteReader600
601
/**//// <summary>602
/// this enum is used to indicate weather the connection was provided by the caller, or created by SqlHelper, so that603
/// we can set the appropriate CommandBehavior when calling ExecuteReader()604
/// </summary>605
private enum SqlConnectionOwnership 606
{607
/**//// <summary>Connection is owned and managed by SqlHelper</summary>608
Internal, 609
/**//// <summary>Connection is owned and managed by the caller</summary>610
External611
}612
613
/**//// <summary>614
/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.615
/// </summary>616
/// <remarks>617
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.618
/// 619
/// If the caller provided the connection, we want to leave it to them to manage.620
/// </remarks>621
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>622
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>623
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>624
/// <param name="commandText">the stored procedure name or T-SQL command</param>625
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>626
/// <param name="connectionOwnership">indicates weather the connection parameter was provided by the caller, or created by SqlHelper</param>627
/// <returns>SqlDataReader containing the results of the command</returns>628
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)629
{ 630
//create a command and prepare it for execution631
SqlCommand cmd = new SqlCommand();632
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);633
634
//create a reader635
SqlDataReader dr;636
637
// call ExecuteReader with the appropriate CommandBehavior638
if (connectionOwnership == SqlConnectionOwnership.External)639
{640
dr = cmd.ExecuteReader();641
}642
else643
{644
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);645
}646
647
return dr;648
}649
650
651
/**//// <summary>652
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 653
/// the connection string. 654
/// </summary>655
/// <remarks>656
/// e.g.: 657
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");658
/// </remarks>659
/// <param name="connectionString">a valid connection string for a SqlConnection</param>660
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>661
/// <param name="commandText">the stored procedure name or T-SQL command</param>662
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>663
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)664
{665
//pass through the call providing null for the set of SqlParameters666
return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);667
}668
669
670
/**//// <summary>671
/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 672
/// using the provided parameters.673
/// </summary>674
/// <remarks>675
/// e.g.: 676
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));677
/// </remarks>678
/// <param name="connectionString">a valid connection string for a SqlConnection</param>679
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>680
/// <param name="commandText">the stored procedure name or T-SQL command</param>681
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>682
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>683
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)684
{685
//create & open a SqlConnection686
SqlConnection cn = new SqlConnection(connectionString);687
cn.Open();688
689
try690
{691
//call the private overload that takes an internally owned connection in place of the connection string692
return ExecuteReader(cn, null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal);693
}694
catch695
{696
//if we fail to return the SqlDatReader, we neeed to close the connection ourselves697
cn.Close();698
throw;699
}700
}701
702
/**//// <summary>703
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 704
/// the conneciton string using the provided parameter values. This method will discover the parameters for the 705
/// stored procedure, and assign the values based on parameter order.706
/// </summary>707
/// <remarks>708
/// This method provides no access to output parameters or the stored procedure's return value parameter.709
/// 710
/// e.g.: 711
/// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);712
/// </remarks>713
/// <param name="connectionString">a valid connection string for a SqlConnection</param>714
/// <param name="spName">the name of the stored prcedure</param>715
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>716
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>717
public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)718
{719
//if we got parameter values, we need to figure out where they go720
if ((parameterValues != null) && (parameterValues.Length > 0)) 721
{722
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)723
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);724
725
//assign the provided values to these parameters based on parameter order726
AssignParameterValues(commandParameters, parameterValues);727
728
//call the overload that takes an array of SqlParameters729
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);730
}731
//otherwise we can just call the SP without params732
else 733
{734
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);735
}736
}737
738
/**//// <summary>739
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 740
/// </summary>741
/// <remarks>742
/// e.g.: 743
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");744
/// </remarks>745
/// <param name="connection">a valid SqlConnection</param>746
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>747
/// <param name="commandText">the stored procedure name or T-SQL command</param>748
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>749
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)750
{751
//pass through the call using a null transaction value752
return ExecuteReader(connection, null, commandType, commandText);753
}754
755
/**//// <summary>756
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 757
/// using the provided parameters.758
/// </summary>759
/// <remarks>760
/// e.g.: 761
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));762
/// </remarks>763
/// <param name="connection">a valid SqlConnection</param>764
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>765
/// <param name="commandText">the stored procedure name or T-SQL command</param>766
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>767
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>768
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)769
{770
//pass through the call using a null transaction value771
return ExecuteReader(connection, null, commandType, commandText, commandParameters);772
}773
774
/**//// <summary>775
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 776
/// using the provided parameter values. This method will discover the parameters for the 777
/// stored procedure, and assign the values based on parameter order.778
/// </summary>779
/// <remarks>780
/// This method provides no access to output parameters or the stored procedure's return value parameter.781
/// 782
/// e.g.: 783
/// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);784
/// </remarks>785
/// <param name="connection">a valid SqlConnection</param>786
/// <param name="spName">the name of the stored prcedure</param>787
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>788
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>789
public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)790
{791
//pass through the call using a null transaction value792
return ExecuteReader(connection, null, spName, parameterValues);793
}794
795
//these three method overloads currently take both connection and transaction. In post-beta2 builds, only 796
//transaction will need to be passed in, and the .Connection property will be available from that transaction797
798
/**//// <summary>799
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection800
/// and SqlTransaction. 801
/// </summary>802
/// <remarks>803
/// e.g.: 804
/// SqlDataReader dr = ExecuteReader(conn, trans, CommandType.StoredProcedure, "GetOrders");805
/// </remarks>806
/// <param name="connection">a valid SqlConnection</param>807
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>808
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>809
/// <param name="commandText">the stored procedure name or T-SQL command</param>810
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>811
public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)812
{813
//pass through the call providing null for the set of SqlParameters814
return ExecuteReader(connection, transaction, commandType, commandText, (SqlParameter[])null);815
}816
817
/**//// <summary>818
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection and SqlTransaction819
/// using the provided parameters.820
/// </summary>821
/// <remarks>822
/// e.g.: 823
/// SqlDataReader dr = ExecuteReader(conn, trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));824
/// </remarks>825
/// <param name="connection">a valid SqlConnection</param>826
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>827
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>828
/// <param name="commandText">the stored procedure name or T-SQL command</param>829
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>830
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>831
public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)832
{833
//pass through to private overload, indicating that the connection is owned by the caller834
return ExecuteReader(connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);835
}836
837
/**//// <summary>838
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 839
/// and SqlTransaction using the provided parameter values. This method will discover the parameters for the 840
/// stored procedure, and assign the values based on parameter order.841
/// </summary>842
/// <remarks>843
/// This method provides no access to output parameters or the stored procedure's return value parameter.844
/// 845
/// e.g.: 846
/// SqlDataReader dr = ExecuteReader(conn, trans, "GetOrders", 24, 36);847
/// </remarks>848
/// <param name="connection">a valid SqlConnection</param>849
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>850
/// <param name="spName">the name of the stored prcedure</param>851
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>852
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>853
public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)854
{855
//if we got parameter values, we need to figure out where they go856
if ((parameterValues != null) && (parameterValues.Length > 0)) 857
{858
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);859
860
AssignParameterValues(commandParameters, parameterValues);861
862
return ExecuteReader(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);863
}864
//otherwise we can just call the SP without params865
else 866
{867
return ExecuteReader(connection, transaction, CommandType.StoredProcedure, spName);868
}869
}870
871
#endregion ExecuteReader872
873
ExecuteScalar#region ExecuteScalar874
875
/**//// <summary>876
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in 877
/// the connection string. 878
/// </summary>879
/// <remarks>880
/// e.g.: 881
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");882
/// </remarks>883
/// <param name="connectionString">a valid connection string for a SqlConnection</param>884
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>885
/// <param name="commandText">the stored procedure name or T-SQL command</param>886
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>887
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)888
{889
//pass through the call providing null for the set of SqlParameters890
return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);891
}892
893
/**//// <summary>894
/// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string 895
/// using the provided parameters.896
/// </summary>897
/// <remarks>898
/// e.g.: 899
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));900
/// </remarks>901
/// <param name="connectionString">a valid connection string for a SqlConnection</param>902
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>903
/// <param name="commandText">the stored procedure name or T-SQL command</param>904
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>905
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>906
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)907
{908
//create & open a SqlConnection, and dispose of it after we are done.909
using (SqlConnection cn = new SqlConnection(connectionString))910
{911
cn.Open();912
913
//call the overload that takes a connection in place of the connection string914
return ExecuteScalar(cn, commandType, commandText, commandParameters);915
}916
}917
918
/**//// <summary>919
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in 920
/// the conneciton string using the provided parameter values. This method will discover the parameters for the 921
/// stored procedure, and assign the values based on parameter order.922
/// </summary>923
/// <remarks>924
/// This method provides no access to output parameters or the stored procedure's return value parameter.925
/// 926
/// e.g.: 927
/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);928
/// </remarks>929
/// <param name="connectionString">a valid connection string for a SqlConnection</param>930
/// <param name="spName">the name of the stored prcedure</param>931
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>932
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>933
public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)934
{935
//if we got parameter values, we need to figure out where they go936
if ((parameterValues != null) && (parameterValues.Length > 0)) 937
{938
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)939
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);940
941
//assign the provided values to these parameters based on parameter order942
AssignParameterValues(commandParameters, parameterValues);943
944
//call the overload that takes an array of SqlParameters945
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);946
}947
//otherwise we can just call the SP without params948
else 949
{950
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);951
}952
}953
954
/**//// <summary>955
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. 956
/// </summary>957
/// <remarks>958
/// e.g.: 959
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");960
/// </remarks>961
/// <param name="connection">a valid SqlConnection</param>962
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>963
/// <param name="commandText">the stored procedure name or T-SQL command</param>964
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>965
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)966
{967
//pass through the call using a null transaction value968
return ExecuteScalar(connection, null, commandType, commandText);969
}970
971
/**//// <summary>972
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 973
/// using the provided parameters.974
/// </summary>975
/// <remarks>976
/// e.g.: 977
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));978
/// </remarks>979
/// <param name="connection">a valid SqlConnection</param>980
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>981
/// <param name="commandText">the stored procedure name or T-SQL command</param>982
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>983
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>984
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)985
{986
//pass through the call using a null transaction value987
return ExecuteScalar(connection, null, commandType, commandText, commandParameters);988
}989
990
/**//// <summary>991
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 992
/// using the provided parameter values. This method will discover the parameters for the 993
/// stored procedure, and assign the values based on parameter order.994
/// </summary>995
/// <remarks>996
/// This method provides no access to output parameters or the stored procedure's return value parameter.997
/// 998
/// e.g.: 999
/// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);1000
/// </remarks>1001
/// <param name="connection">a valid SqlConnection</param>1002
/// <param name="spName">the name of the stored prcedure</param>1003
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>1004
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>1005
public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)1006
{1007
//pass through the call using a null transaction value1008
return ExecuteScalar(connection, null, spName, parameterValues);1009
}1010
1011
//these three method overloads currently take both connection and transaction. In post-beta2 builds, only 1012
//transaction will need to be passed in, and the .Connection property will be available from that transaction1013
1014
/**//// <summary>1015
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection1016
/// and SqlTransaction. 1017
/// </summary>1018
/// <remarks>1019
/// e.g.: 1020
/// int orderCount = (int)ExecuteScalar(conn, trans, CommandType.StoredProcedure, "GetOrderCount");1021
/// </remarks>1022
/// <param name="connection">a valid SqlConnection</param>1023
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>1024
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>1025
/// <param name="commandText">the stored procedure name or T-SQL command</param>1026
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>1027
public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)1028
{1029
//pass through the call providing null for the set of SqlParameters1030
return ExecuteScalar(connection, transaction, commandType, commandText, (SqlParameter[])null);1031
}1032
1033
/**//// <summary>1034
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection and SqlTransaction1035
/// using the provided parameters.1036
/// </summary>1037
/// <remarks>1038
/// e.g.: 1039
/// int orderCount = (int)ExecuteScalar(conn, trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));1040
/// </remarks>1041
/// <param name="connection">a valid SqlConnection</param>1042
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>1043
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>1044
/// <param name="commandText">the stored procedure name or T-SQL command</param>1045
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>1046
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>1047
public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)1048
{1049
//create a command and prepare it for execution1050
SqlCommand cmd = new SqlCommand();1051
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);1052
1053
//execute the command & return the results1054
return cmd.ExecuteScalar();1055
1056
}1057
1058
/**//// <summary>1059
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 1060
/// and SqlTransaction using the provided parameter values. This method will discover the parameters for the 1061
/// stored procedure, and assign the values based on parameter order.1062
/// </summary>1063
/// <remarks>1064
/// This method provides no access to output parameters or the stored procedure's return value parameter.1065
/// 1066
/// e.g.: 1067
/// int orderCount = (int)ExecuteScalar(conn, trans, "GetOrderCount", 24, 36);1068
/// </remarks>1069
/// <param name="connection">a valid SqlConnection</param>1070
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>1071
/// <param name="spName">the name of the stored prcedure</param>1072
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>1073
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>1074
public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)1075
{1076
//if we got parameter values, we need to figure out where they go1077
if ((parameterValues != null) && (parameterValues.Length > 0)) 1078
{1079
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)1080
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);1081
1082
//assign the provided values to these parameters based on parameter order1083
AssignParameterValues(commandParameters, parameterValues);1084
1085
//call the overload that takes an array of SqlParameters1086
return ExecuteScalar(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);1087
}1088
//otherwise we can just call the SP without params1089
else 1090
{1091
return ExecuteScalar(connection, transaction, CommandType.StoredProcedure, spName);1092
}1093
}1094
1095
#endregion ExecuteScalar 1096
1097
ExecuteXmlReader#region ExecuteXmlReader1098
1099
/**//// <summary>1100
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 1101
/// </summary>1102
/// <remarks>1103
/// e.g.: 1104
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");1105
/// </remarks>1106
/// <param name="connection">a valid SqlConnection</param>1107
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>1108
/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>1109
/// <returns>an XmlReader containing the resultset generated by the command</returns>1110
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)1111
{1112
//pass through the call using a null transaction value1113
return ExecuteXmlReader(connection, null, commandType, commandText);1114
}1115
1116
/**//// <summary>1117
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 1118
/// using the provided parameters.1119
/// </summary>1120
/// <remarks>1121
/// e.g.: 1122
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));1123
/// </remarks>1124
/// <param name="connection">a valid SqlConnection</param>1125
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>1126
/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>1127
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>1128
/// <returns>an XmlReader containing the resultset generated by the command</returns>1129
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)1130
{1131
//pass through the call using a null transaction value1132
return ExecuteXmlReader(connection, null, commandType, commandText, commandParameters);1133
}1134
1135
/**//// <summary>1136
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 1137
/// using the provided parameter values. This method will discover the parameters for the 1138
/// stored procedure, and assign the values based on parameter order.1139
/// </summary>1140
/// <remarks>1141
/// This method provides no access to output parameters or the stored procedure's return value parameter.1142
/// 1143
/// e.g.: 1144
/// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);1145
/// </remarks>1146
/// <param name="connection">a valid SqlConnection</param>1147
/// <param name="spName">the name of the stored prcedure using "FOR XML AUTO"</param>1148
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>1149
/// <returns>an XmlReader containing the resultset generated by the command</returns>1150
public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)1151
{1152
//pass through the call using a null transaction value1153
return ExecuteXmlReader(connection, null, spName, parameterValues);1154
}1155
1156
//these three method overloads currently take both connection and transaction. In post-beta2 builds, only 1157
//transaction will need to be passed in, and the .Connection property will be available from that transaction1158
1159
/**//// <summary>1160
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection1161
/// and SqlTransaction. 1162
/// </summary>1163
/// <remarks>1164
/// e.g.: 1165
/// XmlReader r = ExecuteXmlReader(conn, trans, CommandType.StoredProcedure, "GetOrders");1166
/// </remarks>1167
/// <param name="connection">a valid SqlConnection</param>1168
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>1169
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>1170
/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>1171
/// <returns>an XmlReader containing the resultset generated by the command</returns>1172
public static XmlReader ExecuteXmlReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)1173
{1174
//pass through the call providing null for the set of SqlParameters1175
return ExecuteXmlReader(connection, transaction, commandType, commandText, (SqlParameter[])null);1176
}1177
1178
/**//// <summary>1179
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection and SqlTransaction1180
/// using the provided parameters.1181
/// </summary>1182
/// <remarks>1183
/// e.g.: 1184
/// XmlReader r = ExecuteXmlReader(conn, trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));1185
/// </remarks>1186
/// <param name="connection">a valid SqlConnection</param>1187
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>1188
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>1189
/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>1190
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>1191
/// <returns>an XmlReader containing the resultset generated by the command</returns>1192
public static XmlReader ExecuteXmlReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)1193
{1194
//create a command and prepare it for execution1195
SqlCommand cmd = new SqlCommand();1196
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);1197
1198
//create the DataAdapter & DataSet1199
return cmd.ExecuteXmlReader();1200
}1201
1202
/**//// <summary>1203
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 1204
/// and SqlTransaction using the provided parameter values. This method will discover the parameters for the 1205
/// stored procedure, and assign the values based on parameter order.1206
/// </summary>1207
/// <remarks>1208
/// This method provides no access to output parameters or the stored procedure's return value parameter.1209
/// 1210
/// e.g.: 1211
/// XmlReader r = ExecuteXmlReader(conn, trans, "GetOrders", 24, 36);1212
/// </remarks>1213
/// <param name="connection">a valid SqlConnection</param>1214
/// <param name="transaction">a valid SqlTransaction associated with the connection</param>1215
/// <param name="spName">the name of the stored prcedure</param>1216
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>1217
/// <returns>a dataset containing the resultset generated by the command</returns>1218
public static XmlReader ExecuteXmlReader(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues)1219
{1220
//if we got parameter values, we need to figure out where they go1221
if ((parameterValues != null) && (parameterValues.Length > 0)) 1222
{1223
//pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache)1224
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);1225
1226
//assign the provided values to these parameters based on parameter order1227
AssignParameterValues(commandParameters, parameterValues);1228
1229
//call the overload that takes an array of SqlParameters1230
return ExecuteXmlReader(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);1231
}1232
//otherwise we can just call the SP without params1233
else 1234
{1235
return ExecuteXmlReader(connection, transaction, CommandType.StoredProcedure, spName);1236
}1237
}1238
1239
1240
#endregion ExecuteXmlReader1241
}1242
1243
/**//// <summary>1244
/// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the1245
/// ability to discover parameters for stored procedures at run-time.1246
/// </summary>1247
public sealed class SqlHelperParameterCache1248
{1249
private methods, variables, and constructors#region private methods, variables, and constructors1250
1251
//Since this class provides only static methods, make the default constructor private to prevent 1252
//instances from being created with "new SqlHelperParameterCache()".1253
private SqlHelperParameterCache() {}1254
1255
//these hashtables are used to map the sp_procedure_params_rowset resultset to the SqlCommand property enum values1256
private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());1257
private static Hashtable paramTypes = Hashtable.Synchronized(new Hashtable());1258
private static Hashtable paramDirections = Hashtable.Synchronized(new Hashtable());1259
1260
/**//// <summary>1261
/// resolve at run-time the appropriate set of SqlParameters for a stored procedure1262
/// </summary>1263
/// <param name="connectionString">a valid connection string for a SqlConnection</param>1264
/// <param name="spName">the name of the stored prcedure</param>1265
/// <param name="includeReturnValueParameter">weather or not to onclude ther return value parameter</param>1266
/// <returns></returns>1267
private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)1268
{1269
DataTable paramDescriptions = new DataTable("paramDescriptions");1270
using (SqlConnection cn = new SqlConnection(connectionString))1271
{1272
cn.Open();1273
SqlCommand cmd = new SqlCommand("sp_procedure_params_rowset",cn);1274
cmd.CommandType = CommandType.StoredProcedure;1275
cmd.Parameters.Add("@procedure_name", spName);1276
1277
SqlDataAdapter da = new SqlDataAdapter(cmd);1278
da.Fill(paramDescriptions);1279
}1280
1281
SqlParameter[] discoveredParameters;1282
1283
if (paramDescriptions.Rows.Count <= 0) 1284
{1285
//sp not found - throw exception1286
throw(new ArgumentException("Stored procedure '" + spName + "' not found", "spName"));1287
}1288
1289
int startRow;1290
if (includeReturnValueParameter) 1291
{1292
discoveredParameters = new SqlParameter[paramDescriptions.Rows.Count];1293
startRow = 0;1294
}1295
else1296
{1297
discoveredParameters = new SqlParameter[paramDescriptions.Rows.Count-1];1298
startRow = 1;1299
}1300
1301
for (int i = 0, j = discoveredParameters.Length; i < j; i++)1302
{1303
DataRow paramRow = paramDescriptions.Rows[i + startRow];1304
discoveredParameters[i] = new SqlParameter();1305
discoveredParameters[i].ParameterName = (string)paramRow["PARAMETER_NAME"];1306
discoveredParameters[i].SqlDbType = (SqlDbType)paramTypes[(string)paramRow["TYPE_NAME"]];1307
discoveredParameters[i].Direction = (ParameterDirection)paramDirections[(short)paramRow["PARAMETER_TYPE"]]; 1308
discoveredParameters[i].Size = paramRow["CHARACTER_OCTET_LENGTH"]==DBNull.Value ? 0 : (int)paramRow["CHARACTER_OCTET_LENGTH"];1309
discoveredParameters[i].Precision = paramRow["NUMERIC_PRECISION"]==DBNull.Value ? (byte)0 : (byte)(short)paramRow["NUMERIC_PRECISION"];1310
discoveredParameters[i].Scale = paramRow["NUMERIC_SCALE"]==DBNull.Value ? (byte)0 : (byte)(short)paramRow["NUMERIC_SCALE"];1311
}1312
1313
return discoveredParameters;1314
}1315
1316
static SqlHelperParameterCache()1317
{1318
//populate the mapping hashtables1319
paramTypes.Add("bigint",SqlDbType.BigInt);1320
paramTypes.Add("binary",SqlDbType.Binary);1321
paramTypes.Add("bit",SqlDbType.Bit);1322
paramTypes.Add("char",SqlDbType.Char);1323
paramTypes.Add("datetime",SqlDbType.DateTime);1324
paramTypes.Add("decimal",SqlDbType.Decimal);1325
paramTypes.Add("float",SqlDbType.Float);1326
paramTypes.Add("image",SqlDbType.Image);1327
paramTypes.Add("int",SqlDbType.Int);1328
paramTypes.Add("money",SqlDbType.Money);1329
paramTypes.Add("nchar",SqlDbType.NChar);1330
paramTypes.Add("ntext",SqlDbType.NText);1331
paramTypes.Add("numeric",SqlDbType.Decimal);1332
paramTypes.Add("nvarchar",SqlDbType.NVarChar);1333
paramTypes.Add("real",SqlDbType.Real);1334
paramTypes.Add("smalldatetime",SqlDbType.SmallDateTime);1335
paramTypes.Add("smallint",SqlDbType.SmallInt);1336
paramTypes.Add("smallmoney",SqlDbType.SmallMoney);1337
paramTypes.Add("sql_variant",SqlDbType.Variant);1338
paramTypes.Add("text",SqlDbType.Text);1339
paramTypes.Add("timestamp",SqlDbType.Timestamp);1340
paramTypes.Add("tinyint",SqlDbType.TinyInt);1341
paramTypes.Add("uniqueidentifier",SqlDbType.UniqueIdentifier);1342
paramTypes.Add("varbinary",SqlDbType.VarBinary);1343
paramTypes.Add("varchar",SqlDbType.VarChar);1344
1345
paramDirections.Add((short)1,ParameterDirection.Input);1346
paramDirections.Add((short)2,ParameterDirection.InputOutput);1347
paramDirections.Add((short)4,ParameterDirection.ReturnValue);1348
1349
}1350
//deep copy of cached SqlParameter array1351
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)1352
{1353
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];1354
1355
for (int i = 0, j = originalParameters.Length; i < j; i++)1356
{1357
clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();1358
}1359
1360
return clonedParameters;1361
}1362
1363
#endregion private methods, variables, and constructors1364
1365
caching functions#region caching functions1366
1367
/**//// <summary>1368
/// add parameter array to the cache1369
/// </summary>1370
/// <param name="connectionString">a valid connection string for a SqlConnection</param>1371
/// <param name="commandText">the stored procedure name or T-SQL command</param>1372
/// <param name="commandParameters">an array of SqlParamters to be cached</param>1373
public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)1374
{1375
string hashKey = connectionString + ":" + commandText;1376
1377
paramCache[hashKey] = commandParameters;1378
}1379
1380
/**//// <summary>1381
/// retrieve a parameter array from the cache1382
/// </summary>1383
/// <param name="connectionString">a valid connection string for a SqlConnection</param>1384
/// <param name="commandText">the stored procedure name or T-SQL command</param>1385
/// <returns>an array of SqlParamters</returns>1386
public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)1387
{1388
string hashKey = connectionString + ":" + commandText;1389
1390
SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];1391
1392
if (cachedParameters == null)1393
{ 1394
return null;1395
}1396
else1397
{1398
return CloneParameters(cachedParameters);1399
}1400
}1401
1402
#endregion caching functions1403
1404
Parameter Discovery Functions#region Parameter Discovery Functions1405
1406
/**//// <summary>1407
/// Retrieves the set of SqlParameters appropriate for the stored procedure1408
/// </summary>1409
/// <remarks>1410
/// This method will query the database for this information, and then store it in a cache for future requests.1411
/// </remarks>1412
/// <param name="connectionString">a valid connection string for a SqlConnection</param>1413
/// <param name="spName">the name of the stored prcedure</param>1414
/// <returns>an array of SqlParameters</returns>1415
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)1416
{1417
return GetSpParameterSet(connectionString, spName, false);1418
}1419
1420
/**//// <summary>1421
/// Retrieves the set of SqlParameters appropriate for the stored procedure1422
/// </summary>1423
/// <remarks>1424
/// This method will query the database for this information, and then store it in a cache for future requests.1425
/// </remarks>1426
/// <param name="connectionString">a valid connection string for a SqlConnection</param>1427
/// <param name="spName">the name of the stored prcedure</param>1428
/// <param name="includeReturnValueParameter">a bool value indicating weather the return value parameter should be included in the results</param>1429
/// <returns>an array of SqlParameters</returns>1430
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)1431
{1432
string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter":"");1433
1434
SqlParameter[] cachedParameters;1435
1436
cachedParameters = (SqlParameter[])paramCache[hashKey];1437
1438
if (cachedParameters == null)1439
{ 1440
cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));1441
}1442
1443
return CloneParameters(cachedParameters);1444
}1445
1446
#endregion Parameter Discovery Functions1447
1448
}1449
}1450
To be continue...
