微软原版SQLHelper类

发布时间：2021-05-21 04:49:47 所属栏目：大数据来源： https://blog.csdn.net/kasama1

导读：代码 Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-- ? 1 // =============================================================================== // Microsoft Data Access Application Block for .NE

代码

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> ? 1 // ===============================================================================
// Microsoft Data Access Application Block for .NET
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//
// sqlHelper.cs
//
// This file contains the implementations of the sqlHelper and sqlHelperParameterCache
// classes.
//
// For more information see the Data Access Application Block Implementation Overview.?
// ===============================================================================
// Release history
// VERSION ? ?DESCRIPTION
// ? 2.0 ? ?Added support for FillDataset,UpdateDataset and "Param" helper methods
//
// ===============================================================================
// Copyright (C) 2000-2001 Microsoft Corporation
// All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND,EITHER EXPRESSED OR IMPLIED,INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
// FITNESS FOR A PARTICULAR PURPOSE.
// ==============================================================================

using System;
using System.Data;
using System.Xml;
using System.Data.sqlClient;
using System.Collections;

namespace Microsoft.ApplicationBlocks.Data
{
? ? /// <summary>
? ? /// The sqlHelper class is intended to encapsulate high performance,scalable best practices for?
? ? /// common uses of sqlClient
? ? /// </summary>
? ? public sealed class sqlHelper
? ? {
? ? ? ? #region private utility methods & constructors

? ? ? ? // Since this class provides only static methods,make the default constructor private to prevent?
? ? ? ? // instances from being created with "new sqlHelper()"
? ? ? ? private sqlHelper() {}

? ? ? ? /// <summary>
? ? ? ? /// This method is used to attach array of sqlParameters to a sqlCommand.
? ? ? ? ///?
? ? ? ? /// This method will assign a value of DbNull to any parameter with a direction of
? ? ? ? /// InputOutput and a value of null. ?
? ? ? ? ///?
? ? ? ? /// This behavior will prevent default values from being used,but
? ? ? ? /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
? ? ? ? /// where the user provided no input value.
? ? ? ? /// </summary>
? ? ? ? /// <param name="command">The command to which the parameters will be added</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParameters to be added to command</param>
? ? ? ? private static void AttachParameters(sqlCommand command,sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( command == null ) throw new ArgumentNullException( "command" );
? ? ? ? ? ? if( commandParameters != null )
? ? ? ? ? ? {
? ? ? ? ? ? ? ? foreach (sqlParameter p in commandParameters)
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? if( p != null )
? ? ? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? ? ? // Check for derived output value with no value assigned
? ? ? ? ? ? ? ? ? ? ? ? if ( ( p.Direction == ParameterDirection.InputOutput ||?
? ? ? ? ? ? ? ? ? ? ? ? ? ? p.Direction == ParameterDirection.Input ) &&?
? ? ? ? ? ? ? ? ? ? ? ? ? ? (p.Value == null))
? ? ? ? ? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? ? ? ? ? p.Value = DBNull.Value;
? ? ? ? ? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? ? ? ? ? command.Parameters.Add(p);
? ? ? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? }
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// This method assigns dataRow column values to an array of sqlParameters
? ? ? ? /// </summary>
? ? ? ? /// <param name="commandParameters">Array of sqlParameters to be assigned values</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
? ? ? ? private static void AssignParameterValues(sqlParameter[] commandParameters,DataRow dataRow)
? ? ? ? {
? ? ? ? ? ? if ((commandParameters == null) || (dataRow == null))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Do nothing if we get no data
? ? ? ? ? ? ? ? return;
? ? ? ? ? ? }

? ? ? ? ? ? int i = 0;
? ? ? ? ? ? // Set the parameters values
? ? ? ? ? ? foreach(sqlParameter commandParameter in commandParameters)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Check the parameter name
? ? ? ? ? ? ? ? if( commandParameter.ParameterName == null ||?
? ? ? ? ? ? ? ? ? ? commandParameter.ParameterName.Length <= 1 )
? ? ? ? ? ? ? ? ? ? throw new Exception(?
? ? ? ? ? ? ? ? ? ? ? ? string.Format(?
? ? ? ? ? ? ? ? ? ? ? ? ? ? "Please provide a valid parameter name on the parameter #{0},the ParameterName property has the following value: '{1}'.",?
? ? ? ? ? ? ? ? ? ? ? ? ? ? i,commandParameter.ParameterName ) );
? ? ? ? ? ? ? ? if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
? ? ? ? ? ? ? ? ? ? commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
? ? ? ? ? ? ? ? i++;
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// This method assigns an array of values to an array of sqlParameters
? ? ? ? /// </summary>
? ? ? ? /// <param name="commandParameters">Array of sqlParameters to be assigned values</param>
? ? ? ? /// <param name="parameterValues">Array of objects holding the values to be assigned</param>
? ? ? ? private static void AssignParameterValues(sqlParameter[] commandParameters,object[] parameterValues)
? ? ? ? {
? ? ? ? ? ? if ((commandParameters == null) || (parameterValues == null))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Do nothing if we get no data
? ? ? ? ? ? ? ? return;
? ? ? ? ? ? }

? ? ? ? ? ? // We must have the same number of values as we pave parameters to put them in
? ? ? ? ? ? if (commandParameters.Length != parameterValues.Length)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? throw new ArgumentException("Parameter count does not match Parameter Value count.");
? ? ? ? ? ? }

? ? ? ? ? ? // Iterate through the sqlParameters,assigning the values from the corresponding position in the?
? ? ? ? ? ? // value array
? ? ? ? ? ? for (int i = 0,j = commandParameters.Length; i < j; i++)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // If the current array value derives from IDbDataParameter,then assign its Value property
? ? ? ? ? ? ? ? if (parameterValues[i] is IDbDataParameter)
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
? ? ? ? ? ? ? ? ? ? if( paramInstance.Value == null )
? ? ? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? ? ? commandParameters[i].Value = DBNull.Value;?
? ? ? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? ? ? else
? ? ? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? ? ? commandParameters[i].Value = paramInstance.Value;
? ? ? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? else if (parameterValues[i] == null)
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? commandParameters[i].Value = DBNull.Value;
? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? else
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? commandParameters[i].Value = parameterValues[i];
? ? ? ? ? ? ? ? }
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// This method opens (if necessary) and assigns a connection,transaction,command type and parameters?
? ? ? ? /// to the provided command
? ? ? ? /// </summary>
? ? ? ? /// <param name="command">The sqlCommand to be prepared</param>
? ? ? ? /// <param name="connection">A valid sqlConnection,on which to execute this command</param>
? ? ? ? /// <param name="transaction">A valid sqlTransaction,or 'null'</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,text,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParameters to be associated with the command or 'null' if no parameters are required</param>
? ? ? ? /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method,otherwose is false.</param>
? ? ? ? private static void PrepareCommand(sqlCommand command,sqlConnection connection,sqlTransaction transaction,CommandType commandType,string commandText,sqlParameter[] commandParameters,out bool mustCloseConnection )
? ? ? ? {
? ? ? ? ? ? if( command == null ) throw new ArgumentNullException( "command" );
? ? ? ? ? ? if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );

? ? ? ? ? ? // If the provided connection is not open,we will open it
? ? ? ? ? ? if (connection.State != ConnectionState.Open)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? mustCloseConnection = true;
? ? ? ? ? ? ? ? connection.Open();
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? mustCloseConnection = false;
? ? ? ? ? ? }

? ? ? ? ? ? // Associate the connection with the command
? ? ? ? ? ? command.Connection = connection;

? ? ? ? ? ? // Set the command text (stored procedure name or sql statement)
? ? ? ? ? ? command.CommandText = commandText;

? ? ? ? ? ? // If we were provided a transaction,assign it
? ? ? ? ? ? if (transaction != null)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited,please provide an open transaction.","transaction" );
? ? ? ? ? ? ? ? command.Transaction = transaction;
? ? ? ? ? ? }

? ? ? ? ? ? // Set the command type
? ? ? ? ? ? command.CommandType = commandType;

? ? ? ? ? ? // Attach the command parameters if they are provided
? ? ? ? ? ? if (commandParameters != null)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? AttachParameters(command,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? return;
? ? ? ? }

? ? ? ? #endregion private utility methods & constructors

? ? ? ? #region ExecuteNonQuery

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns no resultset and takes no parameters) against the database specified in?
? ? ? ? /// the connection string
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int result = ExecuteNonQuery(connString,CommandType.StoredProcedure,"PublishOrders");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQuery(string connectionString,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteNonQuery(connectionString,commandType,commandText,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns no resultset) against the database specified in the connection string?
? ? ? ? /// using the provided parameters
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int result = ExecuteNonQuery(connString,"PublishOrders",new sqlParameter("@prodid",24));
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQuery(string connectionString,params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );

? ? ? ? ? ? // Create & open a sqlConnection,and dispose of it after we are done
? ? ? ? ? ? using (sqlConnection connection = new sqlConnection(connectionString))
? ? ? ? ? ? {
? ? ? ? ? ? ? ? connection.Open();

? ? ? ? ? ? ? ? // Call the overload that takes a connection in place of the connection string
? ? ? ? ? ? ? ? return ExecuteNonQuery(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns no resultset) against the database specified in?
? ? ? ? /// the connection string using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int result = ExecuteNonQuery(connString,24,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored prcedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQuery(string connectionString,string spName,params object[] parameterValues)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? // If we receive parameter values,we need to figure out where they go
? ? ? ? ? ? if ((parameterValues != null) && (parameterValues.Length > 0))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connectionString,spName);

? ? ? ? ? ? ? ? // Assign the provided values to these parameters based on parameter order
? ? ? ? ? ? ? ? AssignParameterValues(commandParameters,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteNonQuery(connectionString,spName,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteNonQuery(connectionString,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns no resultset and takes no parameters) against the provided sqlConnection.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int result = ExecuteNonQuery(conn,"PublishOrders");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQuery(sqlConnection connection,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteNonQuery(connection,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns no resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int result = ExecuteNonQuery(conn,24));
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQuery(sqlConnection connection,params sqlParameter[] commandParameters)
? ? ? ? { ? ?
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );

? ? ? ? ? ? // Create a command and prepare it for execution
? ? ? ? ? ? sqlCommand cmd = new sqlCommand();
? ? ? ? ? ? bool mustCloseConnection = false;
? ? ? ? ? ? PrepareCommand(cmd,connection,(sqlTransaction)null,commandParameters,out mustCloseConnection );
? ? ? ? ? ??
? ? ? ? ? ? // Finally,execute the command
? ? ? ? ? ? int retval = cmd.ExecuteNonQuery();
? ? ? ? ? ??
? ? ? ? ? ? // Detach the sqlParameters from the command object,so they can be used again
? ? ? ? ? ? cmd.Parameters.Clear();
? ? ? ? ? ? if( mustCloseConnection )
? ? ? ? ? ? ? ? connection.Close();
? ? ? ? ? ? return retval;
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns no resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int result = ExecuteNonQuery(conn,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQuery(sqlConnection connection,params object[] parameterValues)
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? // If we receive parameter values,we need to figure out where they go
? ? ? ? ? ? if ((parameterValues != null) && (parameterValues.Length > 0))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connection,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteNonQuery(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteNonQuery(connection,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns no resultset and takes no parameters) against the provided sqlTransaction.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int result = ExecuteNonQuery(trans,"PublishOrders");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQuery(sqlTransaction transaction,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteNonQuery(transaction,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns no resultset) against the specified sqlTransaction
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int result = ExecuteNonQuery(trans,"GetOrders",24));
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQuery(sqlTransaction transaction,params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( transaction == null ) throw new ArgumentNullException( "transaction" );
? ? ? ? ? ? if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited,"transaction" );

? ? ? ? ? ? // Create a command and prepare it for execution
? ? ? ? ? ? sqlCommand cmd = new sqlCommand();
? ? ? ? ? ? bool mustCloseConnection = false;
? ? ? ? ? ? PrepareCommand(cmd,transaction.Connection,out mustCloseConnection );
? ? ? ? ? ? ? ??
? ? ? ? ? ? // Finally,execute the command
? ? ? ? ? ? int retval = cmd.ExecuteNonQuery();
? ? ? ? ? ? ? ??
? ? ? ? ? ? // Detach the sqlParameters from the command object,so they can be used again
? ? ? ? ? ? cmd.Parameters.Clear();
? ? ? ? ? ? return retval;
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns no resultset) against the specified?
? ? ? ? /// sqlTransaction using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),trans,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQuery(sqlTransaction transaction,params object[] parameterValues)
? ? ? ? {
? ? ? ? ? ? if( transaction == null ) throw new ArgumentNullException( "transaction" );
? ? ? ? ? ? if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited,"transaction" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? // If we receive parameter values,we need to figure out where they go
? ? ? ? ? ? if ((parameterValues != null) && (parameterValues.Length > 0))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(transaction.Connection,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteNonQuery(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteNonQuery(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? #endregion ExecuteNonQuery

? ? ? ? #region ExecuteDataset

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the database specified in?
? ? ? ? /// the connection string.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?DataSet ds = ExecuteDataset(connString,"GetOrders");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDataset(string connectionString,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteDataset(connectionString,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the database specified in the connection string?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?DataSet ds = ExecuteDataset(connString,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDataset(string connectionString,and dispose of it after we are done
? ? ? ? ? ? using (sqlConnection connection = new sqlConnection(connectionString))
? ? ? ? ? ? {
? ? ? ? ? ? ? ? connection.Open();

? ? ? ? ? ? ? ? // Call the overload that takes a connection in place of the connection string
? ? ? ? ? ? ? ? return ExecuteDataset(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the database specified in?
? ? ? ? /// the connection string using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?DataSet ds = ExecuteDataset(connString,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDataset(string connectionString,params object[] parameterValues)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
? ? ? ? ? ??
? ? ? ? ? ? // If we receive parameter values,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteDataset(connectionString,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteDataset(connectionString,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provided sqlConnection.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?DataSet ds = ExecuteDataset(conn,"GetOrders");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDataset(sqlConnection connection,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteDataset(connection,(sqlParameter[])null);
? ? ? ? }
? ? ? ??
? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?DataSet ds = ExecuteDataset(conn,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDataset(sqlConnection connection,params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );

? ? ? ? ? ? // Create a command and prepare it for execution
? ? ? ? ? ? sqlCommand cmd = new sqlCommand();
? ? ? ? ? ? bool mustCloseConnection = false;
? ? ? ? ? ? PrepareCommand(cmd,out mustCloseConnection );
? ? ? ? ? ? ? ??
? ? ? ? ? ? // Create the DataAdapter & DataSet
? ? ? ? ? ? using( sqlDataAdapter da = new sqlDataAdapter(cmd) )
? ? ? ? ? ? {
? ? ? ? ? ? ? ? DataSet ds = new DataSet();

? ? ? ? ? ? ? ? // Fill the DataSet using default values for DataTable names,etc
? ? ? ? ? ? ? ? da.Fill(ds);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? // Detach the sqlParameters from the command object,so they can be used again
? ? ? ? ? ? ? ? cmd.Parameters.Clear();

? ? ? ? ? ? ? ? if( mustCloseConnection )
? ? ? ? ? ? ? ? ? ? connection.Close();

? ? ? ? ? ? ? ? // Return the dataset
? ? ? ? ? ? ? ? return ds;
? ? ? ? ? ? } ? ?
? ? ? ? }
? ? ? ??
? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?DataSet ds = ExecuteDataset(conn,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDataset(sqlConnection connection,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteDataset(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteDataset(connection,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provided sqlTransaction.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?DataSet ds = ExecuteDataset(trans,"GetOrders");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDataset(sqlTransaction transaction,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteDataset(transaction,(sqlParameter[])null);
? ? ? ? }
? ? ? ??
? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the specified sqlTransaction
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?DataSet ds = ExecuteDataset(trans,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDataset(sqlTransaction transaction,so they can be used again
? ? ? ? ? ? ? ? cmd.Parameters.Clear();

? ? ? ? ? ? ? ? // Return the dataset
? ? ? ? ? ? ? ? return ds;
? ? ? ? ? ? } ? ?
? ? ? ? }
? ? ? ??
? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified?
? ? ? ? /// sqlTransaction using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?DataSet ds = ExecuteDataset(trans,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDataset(sqlTransaction transaction,"transaction" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
? ? ? ? ? ??
? ? ? ? ? ? // If we receive parameter values,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteDataset(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteDataset(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? #endregion ExecuteDataset
? ? ? ??
? ? ? ? #region ExecuteReader

? ? ? ? /// <summary>
? ? ? ? /// This enum is used to indicate whether the connection was provided by the caller,or created by sqlHelper,so that
? ? ? ? /// we can set the appropriate CommandBehavior when calling ExecuteReader()
? ? ? ? /// </summary>
? ? ? ? private enum sqlConnectionOwnership ? ?
? ? ? ? {
? ? ? ? ? ? /// <summary>Connection is owned and managed by sqlHelper</summary>
? ? ? ? ? ? Internal,?
? ? ? ? ? ? /// <summary>Connection is owned and managed by the caller</summary>
? ? ? ? ? ? External
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Create and prepare a sqlCommand,and call ExecuteReader with the appropriate CommandBehavior.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// If we created and opened the connection,we want the connection to be closed when the DataReader is closed.
? ? ? ? ///?
? ? ? ? /// If the caller provided the connection,we want to leave it to them to manage.
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParameters to be associated with the command or 'null' if no parameters are required</param>
? ? ? ? /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller,or created by sqlHelper</param>
? ? ? ? /// <returns>sqlDataReader containing the results of the command</returns>
? ? ? ? private static sqlDataReader ExecuteReader(sqlConnection connection,sqlConnectionOwnership connectionOwnership)
? ? ? ? { ? ?
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );

? ? ? ? ? ? bool mustCloseConnection = false;
? ? ? ? ? ? // Create a command and prepare it for execution
? ? ? ? ? ? sqlCommand cmd = new sqlCommand();
? ? ? ? ? ? try
? ? ? ? ? ? {
? ? ? ? ? ? ? ? PrepareCommand(cmd,out mustCloseConnection );
? ? ? ? ? ??
? ? ? ? ? ? ? ? // Create a reader
? ? ? ? ? ? ? ? sqlDataReader dataReader;

? ? ? ? ? ? ? ? // Call ExecuteReader with the appropriate CommandBehavior
? ? ? ? ? ? ? ? if (connectionOwnership == sqlConnectionOwnership.External)
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? dataReader = cmd.ExecuteReader();
? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? else
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
? ? ? ? ? ? ? ? }
? ? ? ? ? ??
? ? ? ? ? ? ? ? // Detach the sqlParameters from the command object,so they can be used again.
? ? ? ? ? ? ? ? // HACK: There is a problem here,the output parameter values are fletched?
? ? ? ? ? ? ? ? // when the reader is closed,so if the parameters are detached from the command
? ? ? ? ? ? ? ? // then the sqlReader can磘 set its values.?
? ? ? ? ? ? ? ? // When this happen,the parameters can磘 be used again in other command.
? ? ? ? ? ? ? ? bool canClear = true;
? ? ? ? ? ? ? ? foreach(sqlParameter commandParameter in cmd.Parameters)
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? if (commandParameter.Direction != ParameterDirection.Input)
? ? ? ? ? ? ? ? ? ? ? ? canClear = false;
? ? ? ? ? ? ? ? }
? ? ? ? ? ??
? ? ? ? ? ? ? ? if (canClear)
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? cmd.Parameters.Clear();
? ? ? ? ? ? ? ? }

? ? ? ? ? ? ? ? return dataReader;
? ? ? ? ? ? }
? ? ? ? ? ? catch
? ? ? ? ? ? {
? ? ? ? ? ? ? ? if( mustCloseConnection )
? ? ? ? ? ? ? ? ? ? connection.Close();
? ? ? ? ? ? ? ? throw;
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the database specified in?
? ? ? ? /// the connection string.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?sqlDataReader dr = ExecuteReader(connString,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReader(string connectionString,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteReader(connectionString,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the database specified in the connection string?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?sqlDataReader dr = ExecuteReader(connString,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReader(string connectionString,params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? sqlConnection connection = null;
? ? ? ? ? ? try
? ? ? ? ? ? {
? ? ? ? ? ? ? ? connection = new sqlConnection(connectionString);
? ? ? ? ? ? ? ? connection.Open();

? ? ? ? ? ? ? ? // Call the private overload that takes an internally owned connection in place of the connection string
? ? ? ? ? ? ? ? return ExecuteReader(connection,null,sqlConnectionOwnership.Internal);
? ? ? ? ? ? }
? ? ? ? ? ? catch
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // If we fail to return the sqlDatReader,we need to close the connection ourselves
? ? ? ? ? ? ? ? if( connection != null ) connection.Close();
? ? ? ? ? ? ? ? throw;
? ? ? ? ? ? }
? ? ? ? ? ??
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the database specified in?
? ? ? ? /// the connection string using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?sqlDataReader dr = ExecuteReader(connString,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReader(string connectionString,we need to figure out where they go
? ? ? ? ? ? if ((parameterValues != null) && (parameterValues.Length > 0))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connectionString,spName);

? ? ? ? ? ? ? ? AssignParameterValues(commandParameters,parameterValues);

? ? ? ? ? ? ? ? return ExecuteReader(connectionString,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteReader(connectionString,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provided sqlConnection.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?sqlDataReader dr = ExecuteReader(conn,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReader(sqlConnection connection,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteReader(connection,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?sqlDataReader dr = ExecuteReader(conn,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReader(sqlConnection connection,params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? // Pass through the call to the private overload using a null transaction value and an externally owned connection
? ? ? ? ? ? return ExecuteReader(connection,sqlConnectionOwnership.External);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?sqlDataReader dr = ExecuteReader(conn,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReader(sqlConnection connection,we need to figure out where they go
? ? ? ? ? ? if ((parameterValues != null) && (parameterValues.Length > 0))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connection,parameterValues);

? ? ? ? ? ? ? ? return ExecuteReader(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteReader(connection,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provided sqlTransaction.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?sqlDataReader dr = ExecuteReader(trans,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReader(sqlTransaction transaction,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteReader(transaction,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the specified sqlTransaction
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ? sqlDataReader dr = ExecuteReader(trans,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReader(sqlTransaction transaction,"transaction" );

? ? ? ? ? ? // Pass through to private overload,indicating that the connection is owned by the caller
? ? ? ? ? ? return ExecuteReader(transaction.Connection,sqlConnectionOwnership.External);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified
? ? ? ? /// sqlTransaction using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?sqlDataReader dr = ExecuteReader(trans,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReader(sqlTransaction transaction,we need to figure out where they go
? ? ? ? ? ? if ((parameterValues != null) && (parameterValues.Length > 0))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(transaction.Connection,parameterValues);

? ? ? ? ? ? ? ? return ExecuteReader(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteReader(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? #endregion ExecuteReader

? ? ? ? #region ExecuteScalar
? ? ? ??
? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in?
? ? ? ? /// the connection string.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int orderCount = (int)ExecuteScalar(connString,"GetOrderCount");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalar(string connectionString,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteScalar(connectionString,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a 1x1 resultset) against the database specified in the connection string?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int orderCount = (int)ExecuteScalar(connString,"GetOrderCount",etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalar(string connectionString,params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? // Create & open a sqlConnection,and dispose of it after we are done
? ? ? ? ? ? using (sqlConnection connection = new sqlConnection(connectionString))
? ? ? ? ? ? {
? ? ? ? ? ? ? ? connection.Open();

? ? ? ? ? ? ? ? // Call the overload that takes a connection in place of the connection string
? ? ? ? ? ? ? ? return ExecuteScalar(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a 1x1 resultset) against the database specified in?
? ? ? ? /// the connection string using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int orderCount = (int)ExecuteScalar(connString,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalar(string connectionString,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteScalar(connectionString,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteScalar(connectionString,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided sqlConnection.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int orderCount = (int)ExecuteScalar(conn,"GetOrderCount");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalar(sqlConnection connection,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteScalar(connection,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a 1x1 resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int orderCount = (int)ExecuteScalar(conn,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalar(sqlConnection connection,params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );

? ? ? ? ? ? // Create a command and prepare it for execution
? ? ? ? ? ? sqlCommand cmd = new sqlCommand();

? ? ? ? ? ? bool mustCloseConnection = false;
? ? ? ? ? ? PrepareCommand(cmd,out mustCloseConnection );
? ? ? ? ? ? ? ??
? ? ? ? ? ? // Execute the command & return the results
? ? ? ? ? ? object retval = cmd.ExecuteScalar();
? ? ? ? ? ? ? ??
? ? ? ? ? ? // Detach the sqlParameters from the command object,so they can be used again
? ? ? ? ? ? cmd.Parameters.Clear();

? ? ? ? ? ? if( mustCloseConnection )
? ? ? ? ? ? ? ? connection.Close();

? ? ? ? ? ? return retval;
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a 1x1 resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int orderCount = (int)ExecuteScalar(conn,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalar(sqlConnection connection,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteScalar(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteScalar(connection,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided sqlTransaction.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int orderCount = (int)ExecuteScalar(trans,"GetOrderCount");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalar(sqlTransaction transaction,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteScalar(transaction,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a 1x1 resultset) against the specified sqlTransaction
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int orderCount = (int)ExecuteScalar(trans,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalar(sqlTransaction transaction,so they can be used again
? ? ? ? ? ? cmd.Parameters.Clear();
? ? ? ? ? ? return retval;
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a 1x1 resultset) against the specified
? ? ? ? /// sqlTransaction using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?int orderCount = (int)ExecuteScalar(trans,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalar(sqlTransaction transaction,we need to figure out where they go
? ? ? ? ? ? if ((parameterValues != null) && (parameterValues.Length > 0))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // PPull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(transaction.Connection,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteScalar(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteScalar(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? #endregion ExecuteScalar ? ?

? ? ? ? #region ExecuteXmlReader
? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provided sqlConnection.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?XmlReader r = ExecuteXmlReader(conn,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command using "FOR XML AUTO"</param>
? ? ? ? /// <returns>An XmlReader containing the resultset generated by the command</returns>
? ? ? ? public static XmlReader ExecuteXmlReader(sqlConnection connection,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteXmlReader(connection,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?XmlReader r = ExecuteXmlReader(conn,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command using "FOR XML AUTO"</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>An XmlReader containing the resultset generated by the command</returns>
? ? ? ? public static XmlReader ExecuteXmlReader(sqlConnection connection,params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );

? ? ? ? ? ? bool mustCloseConnection = false;
? ? ? ? ? ? // Create a command and prepare it for execution
? ? ? ? ? ? sqlCommand cmd = new sqlCommand();
? ? ? ? ? ? try
? ? ? ? ? ? {
? ? ? ? ? ? ? ? PrepareCommand(cmd,out mustCloseConnection );
? ? ? ? ? ??
? ? ? ? ? ? ? ? // Create the DataAdapter & DataSet
? ? ? ? ? ? ? ? XmlReader retval = cmd.ExecuteXmlReader();
? ? ? ? ? ??
? ? ? ? ? ? ? ? // Detach the sqlParameters from the command object,so they can be used again
? ? ? ? ? ? ? ? cmd.Parameters.Clear();

? ? ? ? ? ? ? ? return retval;
? ? ? ? ? ? }
? ? ? ? ? ? catch
? ? ? ? ? ? { ? ?
? ? ? ? ? ? ? ? if( mustCloseConnection )
? ? ? ? ? ? ? ? ? ? connection.Close();
? ? ? ? ? ? ? ? throw;
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?XmlReader r = ExecuteXmlReader(conn,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure using "FOR XML AUTO"</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>An XmlReader containing the resultset generated by the command</returns>
? ? ? ? public static XmlReader ExecuteXmlReader(sqlConnection connection,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteXmlReader(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteXmlReader(connection,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provided sqlTransaction.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?XmlReader r = ExecuteXmlReader(trans,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command using "FOR XML AUTO"</param>
? ? ? ? /// <returns>An XmlReader containing the resultset generated by the command</returns>
? ? ? ? public static XmlReader ExecuteXmlReader(sqlTransaction transaction,string commandText)
? ? ? ? {
? ? ? ? ? ? // Pass through the call providing null for the set of sqlParameters
? ? ? ? ? ? return ExecuteXmlReader(transaction,(sqlParameter[])null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the specified sqlTransaction
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?XmlReader r = ExecuteXmlReader(trans,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command using "FOR XML AUTO"</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <returns>An XmlReader containing the resultset generated by the command</returns>
? ? ? ? public static XmlReader ExecuteXmlReader(sqlTransaction transaction,out mustCloseConnection );
? ? ? ? ? ??
? ? ? ? ? ? // Create the DataAdapter & DataSet
? ? ? ? ? ? XmlReader retval = cmd.ExecuteXmlReader();
? ? ? ? ? ??
? ? ? ? ? ? // Detach the sqlParameters from the command object,so they can be used again
? ? ? ? ? ? cmd.Parameters.Clear();
? ? ? ? ? ? return retval; ? ? ? ? ? ?
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified?
? ? ? ? /// sqlTransaction using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?XmlReader r = ExecuteXmlReader(trans,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static XmlReader ExecuteXmlReader(sqlTransaction transaction,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? return ExecuteXmlReader(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? return ExecuteXmlReader(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? #endregion ExecuteXmlReader

? ? ? ? #region FillDataset
? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the database specified in?
? ? ? ? /// the connection string.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(connString,ds,new string[] {"orders"});
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)</param>
? ? ? ? public static void FillDataset(string connectionString,DataSet dataSet,string[] tableNames)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
? ? ? ? ? ??
? ? ? ? ? ? // Create & open a sqlConnection,and dispose of it after we are done
? ? ? ? ? ? using (sqlConnection connection = new sqlConnection(connectionString))
? ? ? ? ? ? {
? ? ? ? ? ? ? ? connection.Open();

? ? ? ? ? ? ? ? // Call the overload that takes a connection in place of the connection string
? ? ? ? ? ? ? ? FillDataset(connection,dataSet,tableNames);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the database specified in the connection string?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(connString,new string[] {"orders"},etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)
? ? ? ? /// </param>
? ? ? ? public static void FillDataset(string connectionString,
? ? ? ? ? ? string commandText,string[] tableNames,
? ? ? ? ? ? params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
? ? ? ? ? ? // Create & open a sqlConnection,tableNames,and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(connString,24);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)
? ? ? ? /// </param> ? ?
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? public static void FillDataset(string connectionString,
? ? ? ? ? ? DataSet dataSet,
? ? ? ? ? ? params object[] parameterValues)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
? ? ? ? ? ? // Create & open a sqlConnection,and dispose of it after we are done
? ? ? ? ? ? using (sqlConnection connection = new sqlConnection(connectionString))
? ? ? ? ? ? {
? ? ? ? ? ? ? ? connection.Open();

? ? ? ? ? ? ? ? // Call the overload that takes a connection in place of the connection string
? ? ? ? ? ? ? ? FillDataset (connection,parameterValues);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provided sqlConnection.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(conn,new string[] {"orders"});
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)
? ? ? ? /// </param> ? ?
? ? ? ? public static void FillDataset(sqlConnection connection,?
? ? ? ? ? ? string commandText,string[] tableNames)
? ? ? ? {
? ? ? ? ? ? FillDataset(connection,null);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(conn,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)
? ? ? ? /// </param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? public static void FillDataset(sqlConnection connection,
? ? ? ? ? ? params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? FillDataset(connection,commandParameters);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(conn,36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)
? ? ? ? /// </param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? public static void FillDataset(sqlConnection connection,?
? ? ? ? ? ? DataSet dataSet,
? ? ? ? ? ? params object[] parameterValues)
? ? ? ? {
? ? ? ? ? ? if ( connection == null ) throw new ArgumentNullException( "connection" );
? ? ? ? ? ? if (dataSet == null ) throw new ArgumentNullException( "dataSet" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? // If we receive parameter values,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? FillDataset(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? FillDataset(connection,tableNames);
? ? ? ? ? ? } ? ?
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provided sqlTransaction.?
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(trans,new string[] {"orders"});
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)
? ? ? ? /// </param>
? ? ? ? public static void FillDataset(sqlTransaction transaction,string[] tableNames)
? ? ? ? {
? ? ? ? ? ? FillDataset (transaction,null); ? ?
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a sqlCommand (that returns a resultset) against the specified sqlTransaction
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(trans,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)
? ? ? ? /// </param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? public static void FillDataset(sqlTransaction transaction,
? ? ? ? ? ? params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? FillDataset(transaction.Connection,commandParameters);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified?
? ? ? ? /// sqlTransaction using the provided parameter values. ?This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method provides no access to output parameters or the stored procedure's return value parameter.
? ? ? ? ///?
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(trans,new string[]{"orders"},36);
? ? ? ? /// </remarks>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)
? ? ? ? /// </param>
? ? ? ? /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
? ? ? ? public static void FillDataset(sqlTransaction transaction,
? ? ? ? ? ? params object[] parameterValues)?
? ? ? ? {
? ? ? ? ? ? if( transaction == null ) throw new ArgumentNullException( "transaction" );
? ? ? ? ? ? if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited,"transaction" );
? ? ? ? ? ? if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? // If we receive parameter values,parameterValues);

? ? ? ? ? ? ? ? // Call the overload that takes an array of sqlParameters
? ? ? ? ? ? ? ? FillDataset(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Otherwise we can just call the SP without params
? ? ? ? ? ? ? ? FillDataset(transaction,tableNames);
? ? ? ? ? ? } ? ?
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Private helper method that execute a sqlCommand (that returns a resultset) against the specified sqlTransaction and sqlConnection
? ? ? ? /// using the provided parameters.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?FillDataset(conn,24));
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection</param>
? ? ? ? /// <param name="transaction">A valid sqlTransaction</param>
? ? ? ? /// <param name="commandType">The CommandType (stored procedure,etc.)</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
? ? ? ? /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
? ? ? ? /// by a user defined name (probably the actual table name)
? ? ? ? /// </param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters used to execute the command</param>
? ? ? ? private static void FillDataset(sqlConnection connection,
? ? ? ? ? ? params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );
? ? ? ? ? ? if( dataSet == null ) throw new ArgumentNullException( "dataSet" );

? ? ? ? ? ? // Create a command and prepare it for execution
? ? ? ? ? ? sqlCommand command = new sqlCommand();
? ? ? ? ? ? bool mustCloseConnection = false;
? ? ? ? ? ? PrepareCommand(command,out mustCloseConnection );
? ? ? ? ? ? ? ??
? ? ? ? ? ? // Create the DataAdapter & DataSet
? ? ? ? ? ? using( sqlDataAdapter dataAdapter = new sqlDataAdapter(command) )
? ? ? ? ? ? {
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? // Add the table mappings specified by the user
? ? ? ? ? ? ? ? if (tableNames != null && tableNames.Length > 0)
? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? string tableName = "Table";
? ? ? ? ? ? ? ? ? ? for (int index=0; index < tableNames.Length; index++)
? ? ? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? ? ? if( tableNames[index] == null || tableNames[index].Length == 0 ) throw new ArgumentException( "The tableNames parameter must contain a list of tables,a value was provided as null or empty string.","tableNames" );
? ? ? ? ? ? ? ? ? ? ? ? dataAdapter.TableMappings.Add(tableName,tableNames[index]);
? ? ? ? ? ? ? ? ? ? ? ? tableName += (index + 1).ToString();
? ? ? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? // Fill the DataSet using default values for DataTable names,etc
? ? ? ? ? ? ? ? dataAdapter.Fill(dataSet);

? ? ? ? ? ? ? ? // Detach the sqlParameters from the command object,so they can be used again
? ? ? ? ? ? ? ? command.Parameters.Clear();
? ? ? ? ? ? }

? ? ? ? ? ? if( mustCloseConnection )
? ? ? ? ? ? ? ? connection.Close();
? ? ? ? }
? ? ? ? #endregion
? ? ? ??
? ? ? ? #region UpdateDataset
? ? ? ? /// <summary>
? ? ? ? /// Executes the respective command for each inserted,updated,or deleted row in the DataSet.
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?UpdateDataset(conn,insertCommand,deleteCommand,updateCommand,"Order");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="insertCommand">A valid transact-sql statement or stored procedure to insert new records into the data source</param>
? ? ? ? /// <param name="deleteCommand">A valid transact-sql statement or stored procedure to delete records from the data source</param>
? ? ? ? /// <param name="updateCommand">A valid transact-sql statement or stored procedure used to update records in the data source</param>
? ? ? ? /// <param name="dataSet">The DataSet used to update the data source</param>
? ? ? ? /// <param name="tableName">The DataTable used to update the data source.</param>
? ? ? ? public static void UpdateDataset(sqlCommand insertCommand,sqlCommand deleteCommand,sqlCommand updateCommand,string tableName)
? ? ? ? {
? ? ? ? ? ? if( insertCommand == null ) throw new ArgumentNullException( "insertCommand" );
? ? ? ? ? ? if( deleteCommand == null ) throw new ArgumentNullException( "deleteCommand" );
? ? ? ? ? ? if( updateCommand == null ) throw new ArgumentNullException( "updateCommand" );
? ? ? ? ? ? if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( "tableName" );?

? ? ? ? ? ? // Create a sqlDataAdapter,and dispose of it after we are done
? ? ? ? ? ? using (sqlDataAdapter dataAdapter = new sqlDataAdapter())
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Set the data adapter commands
? ? ? ? ? ? ? ? dataAdapter.UpdateCommand = updateCommand;
? ? ? ? ? ? ? ? dataAdapter.InsertCommand = insertCommand;
? ? ? ? ? ? ? ? dataAdapter.DeleteCommand = deleteCommand;

? ? ? ? ? ? ? ? // Update the dataset changes in the data source
? ? ? ? ? ? ? ? dataAdapter.Update (dataSet,tableName);?

? ? ? ? ? ? ? ? // Commit all the changes made to the DataSet
? ? ? ? ? ? ? ? dataSet.AcceptChanges();
? ? ? ? ? ? }
? ? ? ? }
? ? ? ? #endregion

? ? ? ? #region CreateCommand
? ? ? ? /// <summary>
? ? ? ? /// Simplify the creation of a sql command object by allowing
? ? ? ? /// a stored procedure and optional parameters to be provided
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// e.g.: ?
? ? ? ? /// ?sqlCommand command = CreateCommand(conn,"AddCustomer","CustomerID","CustomerName");
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="sourceColumns">An array of string to be assigned as the source columns of the stored procedure parameters</param>
? ? ? ? /// <returns>A valid sqlCommand object</returns>
? ? ? ? public static sqlCommand CreateCommand(sqlConnection connection,params string[] sourceColumns)?
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? // Create a sqlCommand
? ? ? ? ? ? sqlCommand cmd = new sqlCommand( spName,connection );
? ? ? ? ? ? cmd.CommandType = CommandType.StoredProcedure;

? ? ? ? ? ? // If we receive parameter values,we need to figure out where they go
? ? ? ? ? ? if ((sourceColumns != null) && (sourceColumns.Length > 0))?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connection,spName);

? ? ? ? ? ? ? ? // Assign the provided source columns to these parameters based on parameter order
? ? ? ? ? ? ? ? for (int index=0; index < sourceColumns.Length; index++)
? ? ? ? ? ? ? ? ? ? commandParameters[index].SourceColumn = sourceColumns[index];

? ? ? ? ? ? ? ? // Attach the discovered parameters to the sqlCommand object
? ? ? ? ? ? ? ? AttachParameters (cmd,commandParameters);
? ? ? ? ? ? }

? ? ? ? ? ? return cmd;
? ? ? ? }
? ? ? ? #endregion

? ? ? ? #region ExecuteNonQueryTypedParams
? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns no resultset) against the database specified in?
? ? ? ? /// the connection string using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on row values.
? ? ? ? /// </summary>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQueryTypedParams(String connectionString,String spName,DataRow dataRow)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
? ? ? ? ? ??
? ? ? ? ? ? // If the row has values,the store procedure parameters must be initialized
? ? ? ? ? ? if (dataRow != null && dataRow.ItemArray.Length > 0)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connectionString,spName);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? // Set the parameters values
? ? ? ? ? ? ? ? AssignParameterValues(commandParameters,dataRow);
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteNonQuery(connectionString,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteNonQuery(connectionString,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns no resultset) against the specified sqlConnection?
? ? ? ? /// using the dataRow column values as the stored procedure's parameters values. ?
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on row values.
? ? ? ? /// </summary>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQueryTypedParams(sqlConnection connection,DataRow dataRow)
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? // If the row has values,the store procedure parameters must be initialized
? ? ? ? ? ? if (dataRow != null && dataRow.ItemArray.Length > 0)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connection,dataRow);
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteNonQuery(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteNonQuery(connection,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns no resultset) against the specified
? ? ? ? /// sqlTransaction using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on row values.
? ? ? ? /// </summary>
? ? ? ? /// <param name="transaction">A valid sqlTransaction object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>An int representing the number of rows affected by the command</returns>
? ? ? ? public static int ExecuteNonQueryTypedParams(sqlTransaction transaction,DataRow dataRow)
? ? ? ? {
? ? ? ? ? ? if( transaction == null ) throw new ArgumentNullException( "transaction" );
? ? ? ? ? ? if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited,"transaction" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? // Sf the row has values,the store procedure parameters must be initialized
? ? ? ? ? ? if (dataRow != null && dataRow.ItemArray.Length > 0)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(transaction.Connection,dataRow);
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteNonQuery(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteNonQuery(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }
? ? ? ? #endregion

? ? ? ? #region ExecuteDatasetTypedParams
? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the database specified in?
? ? ? ? /// the connection string using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on row values.
? ? ? ? /// </summary>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDatasetTypedParams(string connectionString,DataRow dataRow)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? //If the row has values,the store procedure parameters must be initialized
? ? ? ? ? ? if ( dataRow != null && dataRow.ItemArray.Length > 0)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connectionString,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteDataset(connectionString,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteDataset(connectionString,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the dataRow column values as the store procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on row values.
? ? ? ? /// </summary>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDatasetTypedParams(sqlConnection connection,the store procedure parameters must be initialized
? ? ? ? ? ? if( dataRow != null && dataRow.ItemArray.Length > 0)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connection,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteDataset(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteDataset(connection,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified sqlTransaction?
? ? ? ? /// using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on row values.
? ? ? ? /// </summary>
? ? ? ? /// <param name="transaction">A valid sqlTransaction object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>A dataset containing the resultset generated by the command</returns>
? ? ? ? public static DataSet ExecuteDatasetTypedParams(sqlTransaction transaction,"transaction" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? // If the row has values,the store procedure parameters must be initialized
? ? ? ? ? ? if( dataRow != null && dataRow.ItemArray.Length > 0)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(transaction.Connection,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteDataset(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteDataset(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? #endregion

? ? ? ? #region ExecuteReaderTypedParams
? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the database specified in?
? ? ? ? /// the connection string using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReaderTypedParams(String connectionString,the store procedure parameters must be initialized
? ? ? ? ? ? if ( dataRow != null && dataRow.ItemArray.Length > 0 )
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connectionString,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteReader(connectionString,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteReader(connectionString,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? ? ? ? ??
? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReaderTypedParams(sqlConnection connection,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteReader(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteReader(connection,spName);
? ? ? ? ? ? }
? ? ? ? }
? ? ? ??
? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified sqlTransaction?
? ? ? ? /// using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <param name="transaction">A valid sqlTransaction object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>A sqlDataReader containing the resultset generated by the command</returns>
? ? ? ? public static sqlDataReader ExecuteReaderTypedParams(sqlTransaction transaction,the store procedure parameters must be initialized
? ? ? ? ? ? if( dataRow != null && dataRow.ItemArray.Length > 0 )
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(transaction.Connection,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteReader(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteReader(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }
? ? ? ? #endregion

? ? ? ? #region ExecuteScalarTypedParams
? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a 1x1 resultset) against the database specified in?
? ? ? ? /// the connection string using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalarTypedParams(String connectionString,the store procedure parameters must be initialized
? ? ? ? ? ? if( dataRow != null && dataRow.ItemArray.Length > 0)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
? ? ? ? ? ? ? ? sqlParameter[] commandParameters = sqlHelperParameterCache.GetSpParameterSet(connectionString,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteScalar(connectionString,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteScalar(connectionString,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a 1x1 resultset) against the specified sqlConnection?
? ? ? ? /// using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalarTypedParams(sqlConnection connection,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteScalar(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteScalar(connection,spName);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a 1x1 resultset) against the specified sqlTransaction
? ? ? ? /// using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <param name="transaction">A valid sqlTransaction object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
? ? ? ? public static object ExecuteScalarTypedParams(sqlTransaction transaction,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteScalar(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteScalar(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }
? ? ? ? #endregion

? ? ? ? #region ExecuteXmlReaderTypedParams
? ? ? ? /// <summary>
? ? ? ? /// Execute a stored procedure via a sqlCommand (that returns a resultset) against the specified sqlConnection?
? ? ? ? /// using the dataRow column values as the stored procedure's parameters values.
? ? ? ? /// This method will query the database to discover the parameters for the?
? ? ? ? /// stored procedure (the first time each stored procedure is called),and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>An XmlReader containing the resultset generated by the command</returns>
? ? ? ? public static XmlReader ExecuteXmlReaderTypedParams(sqlConnection connection,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteXmlReader(connection,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteXmlReader(connection,and assign the values based on parameter order.
? ? ? ? /// </summary>
? ? ? ? /// <param name="transaction">A valid sqlTransaction object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
? ? ? ? /// <returns>An XmlReader containing the resultset generated by the command</returns>
? ? ? ? public static XmlReader ExecuteXmlReaderTypedParams(sqlTransaction transaction,dataRow);
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? return sqlHelper.ExecuteXmlReader(transaction,commandParameters);
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return sqlHelper.ExecuteXmlReader(transaction,spName);
? ? ? ? ? ? }
? ? ? ? }
? ? ? ? #endregion

? ? }

? ? /// <summary>
? ? /// sqlHelperParameterCache provides functions to leverage a static cache of procedure parameters,and the
? ? /// ability to discover parameters for stored procedures at run-time.
? ? /// </summary>
? ? public sealed class sqlHelperParameterCache
? ? {
? ? ? ? #region private methods,variables,and constructors

? ? ? ? //Since this class provides only static methods,make the default constructor private to prevent?
? ? ? ? //instances from being created with "new sqlHelperParameterCache()"
? ? ? ? private sqlHelperParameterCache() {}

? ? ? ? private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

? ? ? ? /// <summary>
? ? ? ? /// Resolve at run time the appropriate set of sqlParameters for a stored procedure
? ? ? ? /// </summary>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
? ? ? ? /// <returns>The parameter array discovered.</returns>
? ? ? ? private static sqlParameter[] DiscoverSpParameterSet(sqlConnection connection,bool includeReturnValueParameter)
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? sqlCommand cmd = new sqlCommand(spName,connection);
? ? ? ? ? ? cmd.CommandType = CommandType.StoredProcedure;

? ? ? ? ? ? connection.Open();
? ? ? ? ? ? sqlCommandBuilder.DeriveParameters(cmd);
? ? ? ? ? ? connection.Close();

? ? ? ? ? ? if (!includeReturnValueParameter)?
? ? ? ? ? ? {
? ? ? ? ? ? ? ? cmd.Parameters.RemoveAt(0);
? ? ? ? ? ? }
? ? ? ? ? ? ? ??
? ? ? ? ? ? sqlParameter[] discoveredParameters = new sqlParameter[cmd.Parameters.Count];

? ? ? ? ? ? cmd.Parameters.CopyTo(discoveredParameters,0);

? ? ? ? ? ? // Init the parameters with a DBNull value
? ? ? ? ? ? foreach (sqlParameter discoveredParameter in discoveredParameters)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? discoveredParameter.Value = DBNull.Value;
? ? ? ? ? ? }
? ? ? ? ? ? return discoveredParameters;
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Deep copy of cached sqlParameter array
? ? ? ? /// </summary>
? ? ? ? /// <param name="originalParameters"></param>
? ? ? ? /// <returns></returns>
? ? ? ? private static sqlParameter[] CloneParameters(sqlParameter[] originalParameters)
? ? ? ? {
? ? ? ? ? ? sqlParameter[] clonedParameters = new sqlParameter[originalParameters.Length];

? ? ? ? ? ? for (int i = 0,j = originalParameters.Length; i < j; i++)
? ? ? ? ? ? {
? ? ? ? ? ? ? ? clonedParameters[i] = (sqlParameter)((ICloneable)originalParameters[i]).Clone();
? ? ? ? ? ? }

? ? ? ? ? ? return clonedParameters;
? ? ? ? }

? ? ? ? #endregion private methods,and constructors

? ? ? ? #region caching functions

? ? ? ? /// <summary>
? ? ? ? /// Add parameter array to the cache
? ? ? ? /// </summary>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <param name="commandParameters">An array of sqlParamters to be cached</param>
? ? ? ? public static void CacheParameterSet(string connectionString,params sqlParameter[] commandParameters)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );

? ? ? ? ? ? string hashKey = connectionString + ":" + commandText;

? ? ? ? ? ? paramCache[hashKey] = commandParameters;
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Retrieve a parameter array from the cache
? ? ? ? /// </summary>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="commandText">The stored procedure name or T-sql command</param>
? ? ? ? /// <returns>An array of sqlParamters</returns>
? ? ? ? public static sqlParameter[] GetCachedParameterSet(string connectionString,string commandText)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );

? ? ? ? ? ? string hashKey = connectionString + ":" + commandText;

? ? ? ? ? ? sqlParameter[] cachedParameters = paramCache[hashKey] as sqlParameter[];
? ? ? ? ? ? if (cachedParameters == null)
? ? ? ? ? ? { ? ? ? ? ? ?
? ? ? ? ? ? ? ? return null;
? ? ? ? ? ? }
? ? ? ? ? ? else
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return CloneParameters(cachedParameters);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? #endregion caching functions

? ? ? ? #region Parameter Discovery Functions

? ? ? ? /// <summary>
? ? ? ? /// Retrieves the set of sqlParameters appropriate for the stored procedure
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method will query the database for this information,and then store it in a cache for future requests.
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <returns>An array of sqlParameters</returns>
? ? ? ? public static sqlParameter[] GetSpParameterSet(string connectionString,string spName)
? ? ? ? {
? ? ? ? ? ? return GetSpParameterSet(connectionString,false);
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Retrieves the set of sqlParameters appropriate for the stored procedure
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method will query the database for this information,and then store it in a cache for future requests.
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connectionString">A valid connection string for a sqlConnection</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
? ? ? ? /// <returns>An array of sqlParameters</returns>
? ? ? ? public static sqlParameter[] GetSpParameterSet(string connectionString,bool includeReturnValueParameter)
? ? ? ? {
? ? ? ? ? ? if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? using(sqlConnection connection = new sqlConnection(connectionString))
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return GetSpParameterSetInternal(connection,includeReturnValueParameter);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Retrieves the set of sqlParameters appropriate for the stored procedure
? ? ? ? /// </summary>
? ? ? ? /// <remarks>
? ? ? ? /// This method will query the database for this information,and then store it in a cache for future requests.
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <returns>An array of sqlParameters</returns>
? ? ? ? internal static sqlParameter[] GetSpParameterSet(sqlConnection connection,string spName)
? ? ? ? {
? ? ? ? ? ? return GetSpParameterSet(connection,and then store it in a cache for future requests.
? ? ? ? /// </remarks>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
? ? ? ? /// <returns>An array of sqlParameters</returns>
? ? ? ? internal static sqlParameter[] GetSpParameterSet(sqlConnection connection,bool includeReturnValueParameter)
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );
? ? ? ? ? ? using (sqlConnection clonedConnection = (sqlConnection)((ICloneable)connection).Clone())
? ? ? ? ? ? {
? ? ? ? ? ? ? ? return GetSpParameterSetInternal(clonedConnection,includeReturnValueParameter);
? ? ? ? ? ? }
? ? ? ? }

? ? ? ? /// <summary>
? ? ? ? /// Retrieves the set of sqlParameters appropriate for the stored procedure
? ? ? ? /// </summary>
? ? ? ? /// <param name="connection">A valid sqlConnection object</param>
? ? ? ? /// <param name="spName">The name of the stored procedure</param>
? ? ? ? /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
? ? ? ? /// <returns>An array of sqlParameters</returns>
? ? ? ? private static sqlParameter[] GetSpParameterSetInternal(sqlConnection connection,bool includeReturnValueParameter)
? ? ? ? {
? ? ? ? ? ? if( connection == null ) throw new ArgumentNullException( "connection" );
? ? ? ? ? ? if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

? ? ? ? ? ? string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter":"");

? ? ? ? ? ? sqlParameter[] cachedParameters;
? ? ? ? ? ??
? ? ? ? ? ? cachedParameters = paramCache[hashKey] as sqlParameter[];
? ? ? ? ? ? if (cachedParameters == null)
? ? ? ? ? ? { ? ?
? ? ? ? ? ? ? ? sqlParameter[] spParameters = DiscoverSpParameterSet(connection,includeReturnValueParameter);
? ? ? ? ? ? ? ? paramCache[hashKey] = spParameters;
? ? ? ? ? ? ? ? cachedParameters = spParameters;
? ? ? ? ? ? }
? ? ? ? ? ??
? ? ? ? ? ? return CloneParameters(cachedParameters);
? ? ? ? }
? ? ? ??
? ? ? ? #endregion Parameter Discovery Functions

? ? }
}

（编辑：北几岛）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!