sample demo 1

C#

using System;
					
public class Program
{
	public static void Main()
	{
		
		Console.WriteLine("Hello World");
		
		
		define delegate with string as parameter for example:
        public delegate void SetParameterValueDelegate(string value);
		
		
		Second, you need to decide who is the owner of the communication, in other words, which form will send the notification?! In our first scenario the main form (parent) will be the owner of the communication. But in the second scenario the dialog form will be the owner.

Once you decide the owner, you'll need to declare an instance of your delegate type inside the owner class for example:

public SetParameterValueDelegate SetParameterValueCallback;

The other forms will be subscribers for the callback (delegate), only if you wish to send notification to them.
		
	
	in child
	public void SetParamValueCallbackFn(string param)
{

txtParam.Text = param;

}
		
		 <add key="DSNNet" value="Server=mmeddev1\mssqlserver1; Database=DM_Travelers; User Id=sa; Password=info123!" />
			 
		
		
	}
}


 public DataSet GetCoverageReport(string sqlQuery)
        {
            return ConnectionProvider.GetDataSet(ConnectionProvider.GetCommand(sqlQuery, CommandType.Text));
        }

//
namespace DataAccessLayer
{
    public class ConnectionProvider
    {
        private static SqlConnection _myConnection;
        private static SqlCommand _myCommand;

        /// <summary>
        /// initilize an open and available SqlConnection object if not created 
        /// </summary>
        /// <returns>an object of SqlConnection</returns>
        public static SqlConnection GetConnection()
        {
            if (_myConnection == null)
            {
                _myConnection = new SqlConnection { ConnectionString = ConfigurationManager.AppSettings["DSNNet"] };
                _myConnection.Open();
            }

            return _myConnection;
        }

        /// <summary>
        /// initilize an object of SqlCommand, if not available based on parameters, type of Command and text of Command
        /// </summary>
        /// <param name="commandText">Text for the SqlCommand Object</param>
        /// <param name="cmdType">Type of SqlCommand wheather StoredProcedure or Text</param>
        /// <returns>an object of SqlCommand</returns>
        public static SqlCommand GetCommand(string commandText, CommandType cmdType)
        {
            _myCommand = new SqlCommand
            {
                Connection = GetConnection(),
                CommandText = commandText,
                CommandTimeout = 0,
                CommandType = cmdType
            };

            return _myCommand;
        }

        /// <summary>
        /// initilize an object of SqlCommand, if not available based on parameters
        /// type of Command and text of Command and no of parameters.
        /// </summary>
        /// <param name="commandText">Text for the SqlCommand Object</param>
        /// <param name="cmdType">Type of SqlCommand wheather StoredProcedure or Text</param>
        /// /// <param name="Params">Collection of parameter for SqlCommand</param>
        /// <returns>an object of SqlCommand</returns>
        public static SqlCommand GetCommand(string commandText, CommandType cmdType, SqlParameter Params)
        {
            _myCommand = new SqlCommand { Connection = GetConnection() };
            _myCommand.Parameters.Add(Params);
            _myCommand.CommandText = commandText;
            _myCommand.CommandTimeout = 0;
            _myCommand.CommandType = cmdType;

            return _myCommand;
        }

        /// <summary>
        /// initilize an object of SqlCommand, if not available based on parameters
        /// type of Command and text of Command and no of parameters.
        /// </summary>
        /// <param name="commandText">Text for the SqlCommand Object</param>
        /// <param name="cmdType">Type of SqlCommand wheather StoredProcedure or Text</param>
        /// /// <param name="Params">Collection of parameter for SqlCommand</param>
        /// <returns>an object of SqlCommand</returns>
        public static SqlCommand GetCommand(string commandText, CommandType cmdType, SqlParameter[] Params)
        {
            _myCommand = new SqlCommand { Connection = GetConnection() };
            foreach (SqlParameter pmt in Params)
            {
                _myCommand.Parameters.Add(pmt);
            }
            _myCommand.CommandText = commandText;
            _myCommand.CommandTimeout = 0;
            _myCommand.CommandType = cmdType;
            return _myCommand;
        }

        /// <summary>
        /// create and fill an object of DataTable and return based on passed SqlCommand Object.
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <returns>an object of DataTable</returns>
        public static DataTable GetDataTable(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            adap.Fill(dt);
            return dt;
        }

        /// <summary>
        /// create and fill an object of DataSet and return based on passed SqlCommand Object.
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <returns>an object of Dataset</returns>
        public static DataSet GetDataSet(SqlCommand cmd)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            adap.Fill(ds);
            return ds;
        }

        /// <summary>
        /// create, fill and return an object of DataSet with data fetched from database based
        /// on object of SqlCommand
        /// </summary>
        /// <param name="cmds">Collection of SqlCommand Objects</param>
        /// <returns>an object of DataSet</returns>
        public static DataSet GetDataSet(SqlCommand[] cmds)
        {
            DataSet ds = new DataSet();
            foreach (SqlCommand cmd in cmds)
            {
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adap.Fill(dt);
                ds.Tables.Add(dt);
                adap.Dispose();
            }
            return ds;
        }

        public object ExecuteScalar(SqlCommand cmd)
        {
            return cmd.ExecuteScalar();
        }
    }
	
	
	------------------------------------
		
		ALTER procedure [dbo].[USP_GetMenuByRole]  
	@RoleID  int,
	@AppName varchar(50)
As

Set NoCount On   
  
Select MenuXML
from Roles  R
Where RoleId = @RoleID
	And AppId = (Select AppId From ApplicationInfo Where [AppName] = @AppName)

Set NoCount Off
		
		
		ALTER Procedure [dbo].[USP_DeleteUser]
	@UserName	Varchar(128),  
	@isSuccessful	int out
As

Set NoCount On

Delete Users
Where UserName = @UserName

If @@Error <> 0
	Set @IsSuccessful = 0
Else
	Set @IsSuccessful = 1

Set NoCount Off
		
		---------------------------------------------
		
		DBParameter[] param = new DBParameter[2];
            param[0] = new DBParameter("@UserName", UserName);
            param[1] = new DBParameter("@IsSuccessful", DbType.Int16, ParameterDirection.Output);

            //call proc to delete from DB
            DBExecutor.ExecuteNonQuery("USP_DeleteUser", DBNames.FRONT_END, LoggedInUserClientId, param);
	
	
	
	 public static class DBExecutor
    {
        public static int DefaultClientID
        {
            get
            {
                return DALConstants.DEFAULTCLIENT;
            }
        }

        #region Execute Dataset Methods
        /// <summary>
        /// Returns a DataSet Object containing Datarows matching to those in the data source. 
        /// </summary>
        /// <param name="command">DataAccessLayer.DBCommand object containing the information about the command to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <returns>DataSet containing the resultant rows.</returns>
        public static DataSet ExecuteDataset(DBCommand command, int ClientID)
        {

            return ExecuteDataset(command, ClientID, false);

        }

        /// <summary>
        /// Returns a DataSet Object containing Datarows matching to those in the data source. 
        /// </summary>
        /// <param name="command">DataAccessLayer.DBCommand object containing the information about the command to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="UseTransaction">True if want to execute the command under transaction, else False.</param>
        /// <returns>DataSet containing the resultant rows.</returns>
        public static DataSet ExecuteDataset(DBCommand command, int ClientID, bool UseTransaction)
        {

            return ExecuteCommmand(command, ClientID, UseTransaction, ExecuteMethodType.DataSet) as System.Data.DataSet;

        }

        /// <summary>
        /// Returns a DataSet Object containing Datarows matching to those in the data source. 
        /// </summary>
        /// <param name="commands">Array of DataAccessLayer.DBCommand containing the information about the commands to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <returns>DataSet containing the resultant rows.</returns>
        public static DataSet ExecuteDataset(DBCommand[] commands, int ClientID)
        {

            return ExecuteDataset(commands, ClientID, false);

        }

        /// <summary>
        /// Returns a DataSet Object containing Datarows matching to those in the data source. 
        /// </summary>
        /// <param name="commands">Array of DataAccessLayer.DBCommand containing the information about the commands to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="UseTransaction">True if want to execute the command under transaction, else False.</param>
        /// <returns>DataSet containing the resultant rows.</returns>
        public static DataSet ExecuteDataset(DBCommand[] commands, int ClientID, bool UseTransaction)
        {

            return ExecuteCommmand(commands, ClientID, UseTransaction, ExecuteMethodType.DataSet) as System.Data.DataSet;

        }

        /// <summary>
        /// Returns a DataSet Object containing Datarows matching to those in the data source. 
        /// </summary>
        /// <param name="CommandString">Command text to be executed.</param>
        /// <param name="ConnectionStringKey">Connection String Key of the database for querying.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="parameters">Parameters to be passed to the procedure.</param>
        /// <returns>DataSet containing the resultant rows.</returns>
        public static DataSet ExecuteDataset(string CommandString, string ConnectionStringKey, int ClientID, params DBParameter[] parameters)
        {
            DBCommand command = new DBCommand();
            command.CommandText = CommandString;

            command.ConnectionStringKey = ConnectionStringKey;
            command.AddParameterRange(parameters);

            return ExecuteCommmand(command, ClientID, null, ExecuteMethodType.DataSet) as DataSet;

        }


        #endregion


        #region Execute Reader Methods
        /// <summary>
        /// Sends the CommandText to the Connection and builds a SqlDataReader. 
        /// </summary>
        /// <param name="command">DataAccessLayer.DBCommand object containing the information about the command to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <returns>System.Data.Common.DbDataReader object for the provided Command.</returns>
        public static System.Data.Common.DbDataReader ExecuteReader(DBCommand command, int ClientID)
        {

            return ExecuteCommmand(command, ClientID, false, ExecuteMethodType.Reader) as System.Data.Common.DbDataReader;

        }

        /// <summary>
        /// Sends the CommandText to the Connection and builds a SqlDataReader. 
        /// </summary>
        /// <param name="commands">Array of DataAccessLayer.DBCommand containing the information about the commands to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <returns>System.Data.Common.DbDataReader object for the provided Command.</returns>
        public static System.Data.Common.DbDataReader ExecuteReader(DBCommand[] commands, int ClientID)
        {

            return ExecuteCommmand(commands, ClientID, false, ExecuteMethodType.Reader) as System.Data.Common.DbDataReader;

        }

        /// <summary>
        /// Sends the CommandText to the Connection and builds a SqlDataReader. 
        /// </summary>
        /// <param name="CommandString">Command text to be executed.</param>
        /// <param name="ConnectionStringKey">Connection String Key of the database for querying.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="parameters">Parameters to be passed to the procedure.</param>
        /// <returns>System.Data.Common.DbDataReader object for the provided Command.</returns>
        public static System.Data.Common.DbDataReader ExecuteReader(string CommandString, string ConnectionStringKey, int ClientID, params DBParameter[] parameters)
        {

            DBCommand command = new DBCommand();
            command.CommandText = CommandString;

            command.ConnectionStringKey = ConnectionStringKey;
            command.AddParameterRange(parameters);

            return ExecuteCommmand(command, ClientID, null, ExecuteMethodType.Reader) as System.Data.Common.DbDataReader;

        }


        #endregion


        #region Execute NonQuery Methods
        /// <summary>
        /// Executes a Procedure against the connection and returns the number of rows affected. 
        /// </summary>
        /// <param name="command">DataAccessLayer.DBCommand object containing the information about the command to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <returns>Number of rows effected.</returns>
        public static int ExecuteNonQuery(DBCommand command, int ClientID)
        {

            return ExecuteNonQuery(command, ClientID, false);

        }

        /// <summary>
        /// Executes a Procedure against the connection and returns the number of rows affected. 
        /// </summary>
        /// <param name="command">DataAccessLayer.DBCommand object containing the information about the command to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="UseTransaction">True if want to execute the command under transaction, else False.</param>
        /// <returns>Number of rows effected.</returns>
        public static int ExecuteNonQuery(DBCommand command, int ClientID, bool UseTransaction)
        {

            return (int)ExecuteCommmand(command, ClientID, UseTransaction, ExecuteMethodType.NonQuery);

        }

        /// <summary>
        /// Executes a Procedure against the connection and returns the number of rows affected. 
        /// </summary>
        /// <param name="commands">Array of DataAccessLayer.DBCommand containing the information about the commands to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <returns>Number of rows effected.</returns>
        public static int ExecuteNonQuery(DBCommand[] commands, int ClientID)
        {

            return ExecuteNonQuery(commands, ClientID, false);

        }

        /// <summary>
        /// Executes a Procedure against the connection and returns the number of rows affected. 
        /// </summary>
        /// <param name="commands">Array of DataAccessLayer.DBCommand containing the information about the commands to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="UseTransaction">True if want to execute the command under transaction, else False.</param>
        /// <returns>Number of rows effected.</returns>
        public static int ExecuteNonQuery(DBCommand[] commands, int ClientID, bool UseTransaction)
        {

            return (int)ExecuteCommmand(commands, ClientID, UseTransaction, ExecuteMethodType.NonQuery); ;

        }

        /// <summary>
        /// Executes a Procedure against the connection and returns the number of rows affected. 
        /// </summary>
        /// <param name="CommandString">Command text to be executed.</param>
        /// <param name="ConnectionStringKey">Connection String Key of the database for querying.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="parameters">Parameters to be passed to the procedure.</param>
        /// <returns>Number of rows effected.</returns>
        public static int ExecuteNonQuery(string CommandString, string ConnectionStringKey, int ClientID, params DBParameter[] parameters)
        {

            DBCommand command = new DBCommand();
            command.CommandText = CommandString;

            command.ConnectionStringKey = ConnectionStringKey;
            command.AddParameterRange(parameters);

            return (int)ExecuteCommmand(command, ClientID, null, ExecuteMethodType.NonQuery);

        }


        #endregion


        #region Execute Scalar Methods
        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned 
        /// by the query. Additional columns or rows are ignored. 
        /// </summary>
        /// <param name="command">DataAccessLayer.DBCommand object containing the information about the command to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        public static object ExecuteScalar(DBCommand command, int ClientID)
        {

            return ExecuteScalar(command, ClientID, false);

        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned 
        /// by the query. Additional columns or rows are ignored. 
        /// </summary>
        /// <param name="command">DataAccessLayer.DBCommand object containing the information about the command to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="UseTransaction">True if want to execute the command under transaction, else False.</param>
        /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        public static object ExecuteScalar(DBCommand command, int ClientID, bool UseTransaction)
        {

            return ExecuteCommmand(command, ClientID, UseTransaction, ExecuteMethodType.Scalar);

        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned 
        /// by the query. Additional columns or rows are ignored. 
        /// </summary>
        /// <param name="commands">Array of DataAccessLayer.DBCommand containing the information about the commands to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        public static object ExecuteScalar(DBCommand[] commands, int ClientID)
        {

            return ExecuteScalar(commands, ClientID, false);

        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned 
        /// by the query. Additional columns or rows are ignored. 
        /// </summary>
        /// <param name="commands">Array of DataAccessLayer.DBCommand containing the information about the commands to be executed.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="UseTransaction">True if want to execute the command under transaction, else False.</param>
        /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        public static object ExecuteScalar(DBCommand[] commands, int ClientID, bool UseTransaction)
        {

            return ExecuteCommmand(commands, ClientID, UseTransaction, ExecuteMethodType.Scalar); ;

        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned 
        /// by the query. Additional columns or rows are ignored. 
        /// </summary>
        /// <param name="CommandString">Command text to be executed.</param>
        /// <param name="ConnectionStringKey">Connection String Key of the database for querying.</param>
        /// <param name="ClientID">Client ID of the user.</param>
        /// <param name="parameters">Parameters to be passed to the procedure.</param>
        /// <returns></returns>
        public static object ExecuteScalar(string CommandString, string ConnectionStringKey, int ClientID, params DBParameter[] parameters)
        {

            DBCommand command = new DBCommand();
            command.CommandText = CommandString;

            command.ConnectionStringKey = ConnectionStringKey;
            command.AddParameterRange(parameters);

            return ExecuteCommmand(command, ClientID, null, ExecuteMethodType.Scalar);

        }


        #endregion


        #region Main Command Execution Code

        private static object ExecuteCommmand(DBCommand command, int ClientID, bool UseTransaction, ExecuteMethodType methodType)
        {
            System.Data.Common.DbTransaction Trans = null;
            TransactionManager tranMgr = null;

            if (UseTransaction)
            {
                tranMgr = new TransactionManager();
                Trans = tranMgr.BeginTransaction(command.ConnectionStringKey, ClientID);
            }

            try
            {
                object retData = ExecuteCommmand(command, ClientID, Trans, methodType);

                if (UseTransaction) { tranMgr.Commit(); }

                return retData;
            }
            catch (Exception ex)
            {
                if (UseTransaction) { tranMgr.RollBack(); }

                throw ex;
            }
        }

        private static object ExecuteCommmand(DBCommand[] commands, int ClientID, bool UseTransaction, ExecuteMethodType methodType)
        {
            System.Data.Common.DbTransaction Trans = null;
            TransactionManager tranMgr = null;

            if (UseTransaction)
            {
                tranMgr = new TransactionManager(commands.Length);
            }

            try
            {
                object retData = null;
                foreach (DBCommand aCommand in commands)
                {
                    Trans = (UseTransaction) ? tranMgr.BeginTransaction(aCommand.ConnectionStringKey, ClientID) : null;

                    retData = ExecuteCommmand(aCommand, ClientID, Trans, methodType);
                }

                if (UseTransaction)
                {
                    tranMgr.Commit();
                }

                return retData;
            }
            catch (Exception ex)
            {
                if (UseTransaction)
                {
                    tranMgr.RollBack();
                }
                throw ex;
            }

        }

        private static object ExecuteCommmand(DBCommand command, int ClientID, System.Data.Common.DbTransaction Trans, ExecuteMethodType methodType)
        {
            ConnectionStringInfo ConStr = ConnectionStringManager.GetConnectionString(command.ConnectionStringKey, ClientID);
            System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(ConStr.ProviderName);
            DBProfiler profiler = new DBProfiler();

            System.Data.Common.DbConnection con;

            System.Data.Common.DbCommand cmd = factory.CreateCommand();

            cmd.CommandText = command.CommandText;
            cmd.CommandType = command.CommandType;

            if (!string.IsNullOrEmpty(command.TimeOutKey))
                cmd.CommandTimeout = CommandTimeOutManager.GetTimeOut(command.TimeOutKey);
            else
            {
                int cmdTimeout = CommandTimeOutManager.GetDefaultTimeOut();
                if (cmdTimeout != -1)
                    cmd.CommandTimeout = cmdTimeout;
            }

            foreach (DBParameter aParam in command.Parameters)
            {
                System.Data.Common.DbParameter dbParam = cmd.CreateParameter();

                dbParam.Direction = aParam.Direction;

                if (aParam.Size > 0)
                    dbParam.Size = aParam.Size;

                if (aParam.IsParameterTypeSet)
                {
                    dbParam.DbType = aParam.Type;
                }

                if (aParam.Value != null)
                    dbParam.Value = aParam.Value;

                int pos = cmd.Parameters.Add(dbParam);

                if (!string.IsNullOrEmpty(aParam.Name))
                    cmd.Parameters[pos].ParameterName = aParam.Name;
                else
                    cmd.Parameters[pos].ParameterName = "";
            }

            if (ConStr.ProviderName == "System.Data.OracleClient")
                UpdateParameterListForOracle(ConStr, cmd);

            if (Trans == null)
            {
                con = factory.CreateConnection();
                con.ConnectionString = ConStr.ConnectionString;
                con.Open();
            }
            else
            {
                con = Trans.Connection;
                cmd.Transaction = Trans;
            }

            try
            {
                cmd.Connection = con;

                object retData = null;

                profiler.StartProfiler(command, ConStr);

                retData = ExecuteMethod(cmd, methodType, factory);

                profiler.EndProfiler(true);

                // Copying values of out or ref parameters back to the Input Parameter Array.
                int p = 0;
                for (int i = 0; i < cmd.Parameters.Count; i++)
                {
                    ////if (!(cmd.Parameters[i] is System.Data.OracleClient.OracleParameter && ((System.Data.OracleClient.OracleParameter)cmd.Parameters[i]).OracleType == System.Data.OracleClient.OracleType.Cursor))
                    ////{
                    ////    command.Parameters[p++].Value = cmd.Parameters[i].Value;
                    ////}
                }

                return retData;
            }
            finally
            {
                if (Trans == null && methodType != ExecuteMethodType.Reader)
                    con.Close();
            }
        }

        private static void UpdateParameterListForOracle(ConnectionStringInfo ConStr,
            System.Data.Common.DbCommand command)
        {
            ////System.Data.IDataParameterCollection paramList = getParameterList(ConStr, command.CommandText);
            ////int i = 0;
            ////foreach (System.Data.IDbDataParameter iParam in paramList)
            ////{
            ////    System.Data.OracleClient.OracleParameter oraParama = iParam as System.Data.OracleClient.OracleParameter;

            ////    if (oraParama is System.Data.OracleClient.OracleParameter && oraParama.Direction == ParameterDirection.Output
            ////            && oraParama.OracleType == System.Data.OracleClient.OracleType.Cursor)
            ////    {
            ////        System.Data.OracleClient.OracleParameter dbParam = new System.Data.OracleClient.OracleParameter();

            ////        dbParam.Direction = oraParama.Direction;

            ////        dbParam.Size = oraParama.Size;

            ////        dbParam.OracleType = System.Data.OracleClient.OracleType.Cursor;

            ////        dbParam.ParameterName = oraParama.ParameterName;

            ////        command.Parameters.Add(dbParam);
            ////    }
            ////    if (command.Parameters[i].ParameterName == null || command.Parameters[i].ParameterName.Length == 0)
            ////    {
            ////        command.Parameters[i].ParameterName = oraParama.ParameterName;
            ////    }
            ////    i++;
            ////}
        }

        private static object ExecuteMethod(System.Data.Common.DbCommand cmd, ExecuteMethodType methodType, System.Data.Common.DbProviderFactory factory)
        {
            object retCommandResult = null;
            switch (methodType)
            {
                case ExecuteMethodType.DataSet:
                    DataSet retDataSet = new DataSet();
                    System.Data.Common.DbDataAdapter adap = factory.CreateDataAdapter();
                    adap.SelectCommand = cmd;
                    adap.Fill(retDataSet);
                    retCommandResult = retDataSet;
                    break;
                case ExecuteMethodType.Scalar:
                    retCommandResult = cmd.ExecuteScalar();
                    break;
                case ExecuteMethodType.NonQuery:
                    retCommandResult = cmd.ExecuteNonQuery();
                    break;
                case ExecuteMethodType.Reader:
                    retCommandResult = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    break;
            }

            return retCommandResult;
        }

        #endregion


        #region Methods for Logger component
        /// <summary>
        /// Reserved for Logger Component. This funtion will execute a command against the database whose information is stored in config file.
        /// </summary>
        /// <param name="command">DBCommand object, containing the information of the procedure to be executed.</param>
        internal static void LogMessage(DBCommand command)
        {
            ConnectionStringInfo ConStr = ConnectionStringManager.GetAdminConnectionString();
            System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(ConStr.ProviderName);

            System.Data.Common.DbConnection con;
            System.Data.Common.DbCommand cmd = factory.CreateCommand();

            con = factory.CreateConnection();
            con.ConnectionString = ConStr.ConnectionString;
            con.Open();

            try
            {
                cmd.CommandText = command.CommandText;
                cmd.CommandType = command.CommandType;
                cmd.Connection = con;

                if (!string.IsNullOrEmpty(command.TimeOutKey))
                    cmd.CommandTimeout = CommandTimeOutManager.GetTimeOut(command.TimeOutKey);
                else
                {
                    int cmdTimeout = CommandTimeOutManager.GetDefaultTimeOut();
                    if (cmdTimeout != -1)
                        cmd.CommandTimeout = cmdTimeout;
                }

                foreach (DBParameter aParam in command.Parameters)
                {
                    System.Data.Common.DbParameter dbParam = cmd.CreateParameter();

                    dbParam.Direction = aParam.Direction;

                    if (aParam.Size > 0)
                        dbParam.Size = aParam.Size;

                    if (aParam.IsParameterTypeSet)
                        dbParam.DbType = aParam.Type;

                    if (aParam.Value != null)
                        dbParam.Value = aParam.Value;

                    int pos = cmd.Parameters.Add(dbParam);

                    if (!string.IsNullOrEmpty(aParam.Name))
                        cmd.Parameters[pos].ParameterName = aParam.Name;
                    else
                        cmd.Parameters[pos].ParameterName = "";
                }

                ExecuteMethod(cmd, ExecuteMethodType.NonQuery, factory);

                // Copying values of out or ref parameters back to the Input Parameter Array.
                for (int i = 0; i < cmd.Parameters.Count; i++)
                {
                    command.Parameters[i].Value = cmd.Parameters[i].Value;
                }

            }
            finally
            {
                con.Close();
            }
        }
        #endregion


        private enum ExecuteMethodType
        {
            DataSet,
            Reader,
            Scalar,
            NonQuery
        }

        /// <summary>
        /// Retrieves parameter information from the stored procedure specified in the DBCommand and 
        /// populates the Parameters collection of the specified DBCommand object.
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="ConnectionStringKey"></param>
        /// <param name="ClientID"></param>
        public static void DeriveParameters(DBCommand command, int ClientID)
        {
            ConnectionStringInfo ConStr = ConnectionStringManager.GetConnectionString(command.ConnectionStringKey, ClientID);

            if (ConStr.ProviderName != "System.Data.SqlClient" && ConStr.ProviderName != "System.Data.OleDb" &&
                ConStr.ProviderName != "System.Data.OracleClient")
            {
                throw new Exception("This function does not supports " + ConStr.ProviderName + " provider.");
            }

            System.Data.IDataParameterCollection ParamList = getParameterList(ConStr, command.CommandText);

            command.Parameters.Clear();

            foreach (System.Data.IDbDataParameter iParam in ParamList)
            {
                DBParameter dbParam = new DBParameter(iParam.ParameterName, iParam.DbType, iParam.Direction);
                dbParam.Size = iParam.Size;

                command.Parameters.Add(dbParam);
            }

        }

        private static System.Data.IDataParameterCollection getParameterList(ConnectionStringInfo ConStr, string ProcName)
        {
            System.Data.IDbCommand icmd;
            if (ConStr.ProviderName == "System.Data.SqlClient")
            {
                System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(ConStr.ConnectionString);
                con.Open();
                try
                {
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(ProcName, con);

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);

                    icmd = cmd;
                }
                finally
                {
                    con.Close();
                }
            }
            //else if (ConStr.ProviderName == "System.Data.OracleClient")
            //{
            //    //System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(ConStr.ConnectionString);
            //    //con.Open();
            //    //try
            //    //{
            //    //    System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand(ProcName, con);

            //    //    cmd.CommandType = System.Data.CommandType.StoredProcedure;

            //    //    System.Data.OracleClient.OracleCommandBuilder.DeriveParameters(cmd);

            //    //    icmd = cmd;
            //    //}
            //    //finally
            //    //{
            //    //    con.Close();
            //    //}
            //}
            else
            {
                System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(ConStr.ConnectionString);
                con.Open();

                try
                {
                    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(ProcName, con);

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    System.Data.OleDb.OleDbCommandBuilder.DeriveParameters(cmd);
                    icmd = cmd;
                }
                finally
                {
                    con.Close();
                }
            }

            return icmd.Parameters;
        }
    }
	
	
	
	-----------------------
		
		using System;
					
public class Program
{
	public static void Main()
	{
		Console.WriteLine("Hello World");
	}
}


using System;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Linq;

using System.Data.OleDb;
using System.Data.SqlClient;
using Common;
using System.Xml;
using System.Transactions;

// The SqlHelper class is intended to encapsulate high performance, scalable best practices for 
// common uses of SqlClient.

// ===============================================================================
// Release history
// VERSION	DESCRIPTION
//   2.0	Added support for FillDataset, UpdateDataset and "Param" helper methods
//
// ===============================================================================

namespace DataAccess
{
    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()
        {
        } // New

        // This method is used to attach array of IDbDataParameters 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.
        // Parameters:
        // -command - The command to which the parameters will be added
        // -commandParameters - an array of IDbDataParameters to be added to command
        private static void AttachParameters(SqlCommand command, IDbDataParameter[] commandParameters)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }
            if (commandParameters != null)
            {
                foreach (IDbDataParameter 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);
                    }
                }
            }
        } // AttachParameters

        // This method assigns dataRow column values to an array of IDbDataParameters.
        // Parameters:
        // -commandParameters: Array of IDbDataParameters to be assigned values
        // -dataRow: the dataRow used to hold the stored procedure' s parameter values
        private static void AssignParameterValues(IDbDataParameter[] commandParameters, DataRow dataRow)
        {

            if (commandParameters == null || dataRow == null)
            {
                // Do nothing if we get no data    
                return;
            }

            // Set the parameters values
            int i = 0;
            foreach (IDbDataParameter 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++;
            }
        }

        // This method assigns an array of values to an array of IDbDataParameters.
        // Parameters:
        // -commandParameters - array of IDbDataParameters to be assigned values
        // -array of objects holding the values to be assigned
        private static void AssignParameterValues(ref IDbDataParameter[] commandParameters, object[] parameterValues)
        {

            int i = 0;
            int j = 0;

            if ((commandParameters == null) && (parameterValues == null) || (commandParameters.Length == 0 && parameterValues.Length == 0) || (parameterValues.Length == 1 && parameterValues[0] == 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 && commandParameters.Length > parameterValues.Length)
            {
                // This means the missing params are optional 
                //ReDim Preserve commandParameters(parameterValues.Length - 1)                
                Array.Resize(ref commandParameters, parameterValues.Length);
            }
            else if (commandParameters.Length < parameterValues.Length)
            {
                // More values than we have params for
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }

            // Value array
            // j = commandParameters.Length - 1
            j = parameterValues.Length - 1;
            for (i = 0; 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];
                }
            }
        } // AssignParameterValues

        // This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        // to the provided command.
        // Parameters:
        // -command - the SqlCommand to be prepared
        // -connection - a valid SqlConnection, on which to execute this command
        // -transaction - a valid SqlTransaction, or ' null' 
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command
        // -commandParameters - an array of IDbDataParameters to be associated with the command or ' null' if no parameters are required
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters, ref 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)
            {
                connection.Open();
                mustCloseConnection = true;
            }
            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;
        } // PrepareCommand

        #endregion

        #region ExecuteNonQuery

        // Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in 
        // the connection string. 
        // e.g.:  
        //  Dim result As Integer =  ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders")
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command
        // Returns: An int representing the number of rows affected by the command
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteNonQuery(connectionString, commandType, commandText, null);
        } // ExecuteNonQuery

        // Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
        // using the provided parameters.
        // e.g.:  
        // Dim result As Integer = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command
        // -commandParameters - an array of SqlParamters used to execute the command
        // Returns: An int representing the number of rows affected by the command
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }
            // Create & open a SqlConnection, and dispose of it after we are done
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
            //INSTANT C# NOTE: Inserted the following 'return' since all code paths must return a value in C#:
            return 0;
        } // ExecuteNonQuery

        // 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 discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        //  Dim result As Integer = ExecuteNonQuery(connString, "PublishOrders", 24, 36)
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection
        // -spName - the name of the stored procedure
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure
        // Returns: An int representing the number of rows affected by the command
        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");
            }

            IDbDataParameter[] commandParameters = null;

            // 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)

                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

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

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

        // Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. 
        // e.g.:  
        // Dim result As Integer = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders")
        // Parameters:
        // -connection - a valid SqlConnection
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command 
        // Returns: An int representing the number of rows affected by the command
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteNonQuery(connection, commandType, commandText, null);

        } // ExecuteNonQuery

        // Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
        // using the provided parameters.
        // e.g.:  
        //  Dim result As Integer = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -connection - a valid SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: An int representing the number of rows affected by the command 
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {

            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            int retval = 0;
            bool mustCloseConnection = false;

            PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters, ref mustCloseConnection);

            cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;

            // Finally, execute the command
            retval = cmd.ExecuteNonQuery();

            // Detach the IDbDataParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            if (mustCloseConnection)
            {
                connection.Close();
            }

            return retval;
        } // ExecuteNonQuery

        // Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection 
        // using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        //  Dim result As integer = ExecuteNonQuery(conn, "PublishOrders", 24, 36)
        // Parameters:
        // -connection - a valid SqlConnection
        // -spName - the name of the stored procedure 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
        // Returns: An int representing the number of rows affected by the command 
        public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }
            IDbDataParameter[] commandParameters = null;

            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

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

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

        } // ExecuteNonQuery

        // Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.
        // e.g.:  
        //  Dim result As Integer = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders")
        // Parameters:
        // -transaction - a valid SqlTransaction associated with the connection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // Returns: An int representing the number of rows affected by the command 
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteNonQuery(transaction, commandType, commandText, null);
        } // ExecuteNonQuery

        // Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
        // using the provided parameters.
        // e.g.:  
        // Dim result As Integer = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -transaction - a valid SqlTransaction 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: An int representing the number of rows affected by the command 
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {

            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            int retval = 0;
            bool mustCloseConnection = false;

            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, ref mustCloseConnection);

            cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;
            // Finally, execute the command
            retval = cmd.ExecuteNonQuery();

            // Detach the IDbDataParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            return retval;
        } // ExecuteNonQuery

        // Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlTransaction 
        // using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim result As Integer = SqlHelper.ExecuteNonQuery(trans, "PublishOrders", 24, 36)
        // Parameters:
        // -transaction - a valid SqlTransaction 
        // -spName - the name of the stored procedure 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
        // Returns: An int representing the number of rows affected by the command 
        public static int ExecuteNonQuery(SqlTransaction transaction, string spName, 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, please provide an open transaction.", "transaction");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;

            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

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

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

        #endregion

        #region ExecuteDataset

        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
        // the connection string. 
        // e.g.:  
        // Dim ds As DataSet = SqlHelper.ExecuteDataset("", commandType.StoredProcedure, "GetOrders")
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command
        // Returns: A dataset containing the resultset generated by the command
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteDataset(connectionString, commandType, commandText, null);
        } // ExecuteDataset

        // Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
        // using the provided parameters.
        // e.g.:  
        // Dim ds As Dataset = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command
        // -commandParameters - an array of SqlParamters used to execute the command
        // Returns: A dataset containing the resultset generated by the command
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {

            if (connectionString == null || connectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            // Create & open a SqlConnection, and dispose of it after we are done
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);

                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                return ExecuteDataset(connection, commandType, commandText, commandParameters);
            }
            finally
            {
                try
                {
                    if (connection != null)
                    {
                        connection.Dispose();
                    }
                }
                catch (Exception ex)
                {
                    string x = "";
                }


            }
            //INSTANT C# NOTE: Inserted the following 'return' since all code paths must return a value in C#:
            return null;
        } // ExecuteDataset

        // 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 discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim ds As Dataset= ExecuteDataset(connString, "GetOrders", 24, 36)
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection
        // -spName - the name of the stored procedure
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure
        // Returns: A dataset containing the resultset generated by the command
        public static DataSet ExecuteDataset(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");
            }
            IDbDataParameter[] commandParameters = null;

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

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

                if (commandParameters.Length > parameterValues.Length) // This enables us to use optional SQL parameters
                {
                    Array.Resize(ref commandParameters, parameterValues.Length);
                }

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

        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
        // e.g.:  
        // Dim ds As Dataset = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders")
        // Parameters:
        // -connection - a valid SqlConnection
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command
        // Returns: A dataset containing the resultset generated by the command
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
        {

            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteDataset(connection, commandType, commandText, null);
        } // ExecuteDataset

        // Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        // using the provided parameters.
        // e.g.:  
        // Dim ds As Dataset = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -connection - a valid SqlConnection
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command
        // -commandParameters - an array of SqlParamters used to execute the command
        // Returns: A dataset containing the resultset generated by the command
        public static DataSet ExecuteDataset(IDbConnection connection, CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter dataAdatpter = null;
            bool mustCloseConnection = false;

            PrepareCommand(cmd, (SqlConnection)connection, null, commandType, commandText, commandParameters, ref mustCloseConnection);

            try
            {

                cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout; // 24 hour timeout
                // Create the DataAdapter & DataSet
                dataAdatpter = new SqlDataAdapter(cmd);

                // Fill the DataSet using default values for DataTable names, etc
                dataAdatpter.Fill(ds);

                // Detach the IDbDataParameters from the command object, so they can be used again
                cmd.Parameters.Clear();
            }
            finally
            {
                if (dataAdatpter != null)
                {
                    dataAdatpter.Dispose();
                }
            }
            if (mustCloseConnection)
            {
                connection.Close();
            }

            // Return the dataset
            return ds;
        } // ExecuteDataset

        // Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
        // using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim ds As Dataset = ExecuteDataset(conn, "GetOrders", 24, 36)
        // Parameters:
        // -connection - a valid SqlConnection
        // -spName - the name of the stored procedure
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure
        // Returns: A dataset containing the resultset generated by the command
        public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
        {

            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;

            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

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

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

        } // ExecuteDataset

        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
        // e.g.:  
        // Dim ds As Dataset = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders")
        // Parameters
        // -transaction - a valid SqlTransaction
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command
        // Returns: A dataset containing the resultset generated by the command
        public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteDataset(transaction, commandType, commandText, null);
        } // ExecuteDataset

        // Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
        // using the provided parameters.
        // e.g.:  
        // Dim ds As Dataset = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new IDbDataParameter("@prodid", 24))
        // Parameters
        // -transaction - a valid SqlTransaction 
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command
        // -commandParameters - an array of SqlParamters used to execute the command
        // Returns: A dataset containing the resultset generated by the command
        public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter dataAdatpter = null;
            bool mustCloseConnection = false;

            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, ref mustCloseConnection);

            try
            {

                cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;

                // Create the DataAdapter & DataSet
                dataAdatpter = new SqlDataAdapter(cmd);

                // Fill the DataSet using default values for DataTable names, etc
                dataAdatpter.Fill(ds);

                // Detach the IDbDataParameters from the command object, so they can be used again
                cmd.Parameters.Clear();
            }
            finally
            {
                if (dataAdatpter != null)
                {
                    dataAdatpter.Dispose();
                }
            }

            // Return the dataset
            return ds;

        } // ExecuteDataset

        // Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
        // SqlTransaction using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim ds As Dataset = ExecuteDataset(trans, "GetOrders", 24, 36)
        // Parameters:
        // -transaction - a valid SqlTransaction 
        // -spName - the name of the stored procedure
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure
        // Returns: A dataset containing the resultset generated by the command
        public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, 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, please provide an open transaction.", "transaction");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;

            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

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

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

        #endregion

        #region ExecuteReader

        //Public Shared Function ExecuteFastReader(ByVal connectionstring As String, ByVal commandType As CommandType, _
        //                                               ByVal commandText As String, _
        //                                               ByVal commandParameters() As Object) As SqlDataReader
        //    Using connection As New SqlClient.SqlConnection(connectionstring)
        //        connection.Open()

        //        Return ExecuteFastReader(connection, commandType, commandText, commandParameters)
        //    End Using
        //End Function

        public static SqlDataReader ExecuteFastReader(System.Data.SqlClient.SqlConnection connection, CommandType commandType, string commandText, object[] commandParameters, bool shouldCloseConnection)
        {

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand(commandText, connection);

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

            IDbDataParameter[] SQLcommandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, commandText);

            // Enables optional params
            for (int i = 0; i <= SQLcommandParameters.GetUpperBound(0); i++)
            {
                if (i <= commandParameters.Length - 1)
                {
                    SQLcommandParameters[i].Value = commandParameters[i];
                    cmd.Parameters.Add(SQLcommandParameters[i]);
                }
            }

            cmd.CommandTimeout = 86400;

            // Call ExecuteReader with the appropriate CommandBehavior
            // Create a reader
            SqlDataReader dataReader = cmd.ExecuteReader((shouldCloseConnection ? CommandBehavior.CloseConnection : CommandBehavior.Default));
            cmd.Parameters.Clear();

            return dataReader;

        } // ExecuteReader


        // 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()
        private enum SqlConnectionOwnership : int
        {
            // Connection is owned and managed by SqlHelper
            Internal,
            // Connection is owned and managed by the caller
            External
        } // SqlConnectionOwnership

        // Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
        // 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.
        // Parameters:
        // -connection - a valid SqlConnection, on which to execute this command 
        // -transaction - a valid SqlTransaction, or ' null' 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of IDbDataParameters to be associated with the command or ' null' if no parameters are required 
        // -connectionOwnership - indicates whether the connection parameter was provided by the caller, or created by SqlHelper 
        // Returns: SqlDataReader containing the results of the command 
        private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters, SqlConnectionOwnership connectionOwnership, SqlCommand cmd = null)
        {

            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            bool mustCloseConnection = false;
            // Create a command and prepare it for execution
            if (cmd == null) cmd = new SqlCommand();

            cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;

            try
            {
                // Create a reader
                SqlDataReader dataReader = null;

                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, ref mustCloseConnection);

                cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;

                // Call ExecuteReader with the appropriate CommandBehavior
                if (connectionOwnership == SqlConnectionOwnership.External)
                {
                    dataReader = cmd.ExecuteReader();
                }
                else
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }

                // Detach the IDbDataParameters from the command object, so they can be used again
                bool canClear = true;
                foreach (IDbDataParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                    {
                        canClear = false;
                    }
                }

                if (canClear)
                {
                    cmd.Parameters.Clear();
                }

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

        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
        // the connection string. 
        // e.g.:  
        // Dim dr As SqlDataReader = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders")
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // Returns: A SqlDataReader containing the resultset generated by the command 
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteReader(connectionString, commandType, commandText, null);
        } // ExecuteReader

        // Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
        // using the provided parameters.
        // e.g.:  
        // Dim dr As SqlDataReader = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: A SqlDataReader containing the resultset generated by the command 
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }

            // Create & open a SqlConnection
            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, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
            }
            catch
            {
                // If we fail to return the SqlDatReader, we need to close the connection ourselves
                if (connection != null)
                {
                    connection.Dispose();
                }
                throw;
            }
        } // ExecuteReader

        // 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 discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim dr As SqlDataReader = ExecuteReader(connString, "GetOrders", 24, 36)
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection 
        // -spName - the name of the stored procedure 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
        // Returns: A SqlDataReader containing the resultset generated by the command 
        public static SqlDataReader ExecuteReader(string connectionString, string spName, object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;

            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

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

                // Call the overload that takes an array of IDbDataParameters
                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
                // Otherwise we can just call the SP without params
            }
            else
            {
                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
        } // ExecuteReader

        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
        // e.g.:  
        // Dim dr As SqlDataReader = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders")
        // Parameters:
        // -connection - a valid SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // Returns: A SqlDataReader containing the resultset generated by the command 
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
        {

            return ExecuteReader(connection, commandType, commandText, null);

        } // ExecuteReader

        // Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
        // 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.
        // Parameters:
        // -connection - a valid SqlConnection, on which to execute this command 
        // -transaction - a valid SqlTransaction, or ' null' 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // -shouldCloseConnection - true to force connection close
        // Returns: SqlDataReader containing the results of the command 
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, bool shouldCloseConnection)
        {

            return ExecuteReader(connection, null, commandType, commandText, null, (SqlConnectionOwnership)((shouldCloseConnection ? SqlConnectionOwnership.Internal : SqlConnectionOwnership.External)));

        }

        // Passing in a command object that can be cancelled elsewhere
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, bool shouldCloseConnection, IDbDataParameter[] commandParameters, SqlCommand cmd = null)
        {

            return ExecuteReader(connection, null, commandType, commandText, commandParameters, (SqlConnectionOwnership)((shouldCloseConnection ? SqlConnectionOwnership.Internal : SqlConnectionOwnership.External)), cmd);

        }


        // Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
        // 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.
        // Parameters:
        // -connection - a valid SqlConnection, on which to execute this command 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // -shouldCloseConnection - true to force connection close
        // -commandParameters - an array of IDbDataParameters to be associated with the command or ' null' if no parameters are required 
        // Returns: SqlDataReader containing the results of the command 
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, bool shouldCloseConnection, IDbDataParameter[] commandParameters)
        {

            return ExecuteReader(connection, null, commandType, commandText, commandParameters, (SqlConnectionOwnership)((shouldCloseConnection ? SqlConnectionOwnership.Internal : SqlConnectionOwnership.External)));

        }

        // Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        // using the provided parameters.
        // e.g.:  
        // Dim dr As SqlDataReader = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -connection - a valid SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: A SqlDataReader containing the resultset generated by the command 
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
        {
            // Pass through the call to private overload using a null transaction value
            return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);

        } // ExecuteReader

        // Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
        // using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim dr As SqlDataReader = ExecuteReader(conn, "GetOrders", 24, 36)
        // Parameters:
        // -connection - a valid SqlConnection 
        // -spName - the name of the stored procedure 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
        // Returns: A SqlDataReader containing the resultset generated by the command 
        public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, object[] parameterValues)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;
            // If we receive parameter values, we need to figure out where they go
            if (parameterValues != null && parameterValues.Length > 0)
            {
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                AssignParameterValues(ref commandParameters, parameterValues);

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

        } // ExecuteReader

        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
        // e.g.:  
        // Dim dr As SqlDataReader = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders")
        // Parameters:
        // -transaction - a valid SqlTransaction  
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // Returns: A SqlDataReader containing the resultset generated by the command 
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteReader(transaction, commandType, commandText, null);
        } // ExecuteReader

        // Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
        // using the provided parameters.
        // e.g.:  
        // Dim dr As SqlDataReader = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -transaction - a valid SqlTransaction 
        // -commandType - the CommandType (stored procedure, text, etc.)
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: A SqlDataReader containing the resultset generated by the command 
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }
            // Pass through to private overload, indicating that the connection is owned by the caller
            return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
        } // ExecuteReader

        // Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
        // using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim dr As SqlDataReader = ExecuteReader(trans, "GetOrders", 24, 36)
        // Parameters:
        // -transaction - a valid SqlTransaction 
        // -spName - the name of the stored procedure 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure
        // Returns: A SqlDataReader containing the resultset generated by the command
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, object[] parameterValues)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;

            // If we receive parameter values, we need to figure out where they go
            if (parameterValues != null && parameterValues.Length > 0)
            {
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                AssignParameterValues(ref commandParameters, parameterValues);

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

        #endregion

        #region ExecuteScalar

        // Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in 
        // the connection string. 
        // e.g.:  
        // Dim orderCount As Integer = CInt(ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"))
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // Returns: An object containing the value in the 1x1 resultset generated by the command
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteScalar(connectionString, commandType, commandText, null);
        } // ExecuteScalar

        // Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string 
        // using the provided parameters.
        // e.g.:  
        // Dim orderCount As Integer = Cint(ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new IDbDataParameter("@prodid", 24)))
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: An object containing the value in the 1x1 resultset generated by the command 
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }
            // Create & open a SqlConnection, and dispose of it after we are done.
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                return ExecuteScalar(connection, commandType, commandText, commandParameters);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
            //INSTANT C# NOTE: Inserted the following 'return' since all code paths must return a value in C#:
            return null;
        } // ExecuteScalar

        // 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 discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim orderCount As Integer = CInt(ExecuteScalar(connString, "GetOrderCount", 24, 36))
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection 
        // -spName - the name of the stored procedure 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
        // Returns: An object containing the value in the 1x1 resultset generated by the command 
        public static object ExecuteScalar(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");
            }

            IDbDataParameter[] commandParameters = null;

            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

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

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

        // Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. 
        // e.g.:  
        // Dim orderCount As Integer = CInt(ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"))
        // Parameters:
        // -connection - a valid SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // Returns: An object containing the value in the 1x1 resultset generated by the command 
        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteScalar(connection, commandType, commandText, null);
        } // ExecuteScalar

        // Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
        // using the provided parameters.
        // e.g.:  
        // Dim orderCount As Integer = CInt(ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new IDbDataParameter("@prodid", 24)))
        // Parameters:
        // -connection - a valid SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: An object containing the value in the 1x1 resultset generated by the command 
        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {

            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            object retval = null;
            bool mustCloseConnection = false;

            PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters, ref mustCloseConnection);

            cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;

            // Execute the command & return the results
            retval = cmd.ExecuteScalar();

            // Detach the IDbDataParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            if (mustCloseConnection)
            {
                connection.Close();
            }

            return retval;

        } // ExecuteScalar

        // Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
        // using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim orderCount As Integer = CInt(ExecuteScalar(conn, "GetOrderCount", 24, 36))
        // Parameters:
        // -connection - a valid SqlConnection 
        // -spName - the name of the stored procedure 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
        // Returns: An object containing the value in the 1x1 resultset generated by the command 
        public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;

            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

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

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

        } // ExecuteScalar

        // Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.
        // e.g.:  
        // Dim orderCount As Integer  = CInt(ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"))
        // Parameters:
        // -transaction - a valid SqlTransaction 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // Returns: An object containing the value in the 1x1 resultset generated by the command 
        public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteScalar(transaction, commandType, commandText, null);
        } // ExecuteScalar

        // Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
        // using the provided parameters.
        // e.g.:  
        // Dim orderCount As Integer = CInt(ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new IDbDataParameter("@prodid", 24)))
        // Parameters:
        // -transaction - a valid SqlTransaction  
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: An object containing the value in the 1x1 resultset generated by the command 
        public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            object retval = null;
            bool mustCloseConnection = false;

            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, ref mustCloseConnection);

            cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;

            // Execute the command & return the results
            retval = cmd.ExecuteScalar();

            // Detach the IDbDataParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            return retval;
        } // ExecuteScalar

        // Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction 
        // using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim orderCount As Integer = CInt(ExecuteScalar(trans, "GetOrderCount", 24, 36))
        // Parameters:
        // -transaction - a valid SqlTransaction 
        // -spName - the name of the stored procedure 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
        // Returns: An object containing the value in the 1x1 resultset generated by the command 
        public static object ExecuteScalar(SqlTransaction transaction, string spName, 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, please provide an open transaction.", "transaction");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;
            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

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

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

        #endregion

        #region ExecuteXmlReader

        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
        // e.g.:  
        // Dim r As XmlReader = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders")
        // Parameters:
        // -connection - a valid SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command using "FOR XML AUTO" 
        // Returns: An XmlReader containing the resultset generated by the command 
        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteXmlReader(connection, commandType, commandText, null);
        } // ExecuteXmlReader

        // Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        // using the provided parameters.
        // e.g.:  
        // Dim r As XmlReader = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -connection - a valid SqlConnection 
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command using "FOR XML AUTO" 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: An XmlReader containing the resultset generated by the command 
        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            // Pass through the call using a null transaction value
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            try
            {
                XmlReader retval = null;

                PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters, ref mustCloseConnection);

                cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;

                // Create the DataAdapter & DataSet
                retval = cmd.ExecuteXmlReader();

                // Detach the IDbDataParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

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

        } // ExecuteXmlReader

        // Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
        // using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim r As XmlReader = ExecuteXmlReader(conn, "GetOrders", 24, 36)
        // Parameters:
        // -connection - a valid SqlConnection 
        // -spName - the name of the stored procedure using "FOR XML AUTO" 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
        // Returns: An XmlReader containing the resultset generated by the command 
        public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;

            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

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

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


        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction
        // e.g.:  
        // Dim r As XmlReader = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders")
        // Parameters:
        // -transaction - a valid SqlTransaction
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command using "FOR XML AUTO" 
        // Returns: An XmlReader containing the resultset generated by the command 
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of IDbDataParameters
            return ExecuteXmlReader(transaction, commandType, commandText, null);
        } // ExecuteXmlReader

        // Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
        // using the provided parameters.
        // e.g.:  
        // Dim r As XmlReader = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -transaction - a valid SqlTransaction
        // -commandType - the CommandType (stored procedure, text, etc.) 
        // -commandText - the stored procedure name or T-SQL command using "FOR XML AUTO" 
        // -commandParameters - an array of SqlParamters used to execute the command 
        // Returns: An XmlReader containing the resultset generated by the command
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            // Create a command and prepare it for execution
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            SqlCommand cmd = new SqlCommand();

            XmlReader retval = null;
            bool mustCloseConnection = false;

            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, ref mustCloseConnection);


            cmd.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;
            // Create the DataAdapter & DataSet
            retval = cmd.ExecuteXmlReader();

            // Detach the IDbDataParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            return retval;

        } // ExecuteXmlReader

        // Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
        // using the provided parameter values.  This method will discover the parameters for the 
        // stored procedure, and assign the values based on parameter order.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // Dim r As XmlReader = ExecuteXmlReader(trans, "GetOrders", 24, 36)
        // Parameters:
        // -transaction - a valid SqlTransaction
        // -spName - the name of the stored procedure 
        // -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
        // Returns: A dataset containing the resultset generated by the command
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, 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, please provide an open transaction.", "transaction");
            }
            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

            IDbDataParameter[] commandParameters = null;

            // 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)
                commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

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

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

        #endregion

        #region FillDataset
        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
        // the connection string. 
        // e.g.:  
        //   FillDataset (connString, CommandType.StoredProcedure, "GetOrders", ds, new String() {"orders"})
        // Parameters:    
        // -connectionString: A valid connection string for a SqlConnection
        // -commandType: the CommandType (stored procedure, text, etc.)
        // -commandText: the stored procedure name or T-SQL command
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        public static void FillDataset(string connectionString, CommandType commandType, string commandText, 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
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);

                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                FillDataset(connection, commandType, commandText, dataSet, tableNames);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
        }

        // Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
        // using the provided parameters.
        // e.g.:  
        //   FillDataset (connString, CommandType.StoredProcedure, "GetOrders", ds, new String() = {"orders"}, new IDbDataParameter("@prodid", 24))
        // Parameters:    
        // -connectionString: A valid connection string for a SqlConnection
        // -commandType: the CommandType (stored procedure, text, etc.)
        // -commandText: the stored procedure name or T-SQL command
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        // -commandParameters: An array of SqlParamters used to execute the command
        public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params IDbDataParameter[] commandParameters)
        {

            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
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);

                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
        }

        // 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.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        //   FillDataset (connString, CommandType.StoredProcedure, "GetOrders", ds, new String() {"orders"}, 24)
        // Parameters:
        // -connectionString: A valid connection string for a SqlConnection
        // -spName: the name of the stored procedure
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        // -parameterValues: An array of objects to be assigned As the input values of the stored procedure
        public static void FillDataset(string connectionString, string spName, DataSet dataSet, string[] tableNames, 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
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);

                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                FillDataset(connection, spName, dataSet, tableNames, parameterValues);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
        }

        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
        // e.g.:  
        //   FillDataset (conn, CommandType.StoredProcedure, "GetOrders", ds, new String() {"orders"})
        // Parameters:
        // -connection: A valid SqlConnection
        // -commandType: the CommandType (stored procedure, text, etc.)
        // -commandText: the stored procedure name or T-SQL command
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        public static void FillDataset(SqlConnection connection, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
        {

            FillDataset(connection, commandType, commandText, dataSet, tableNames, null);

        }

        // Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        // using the provided parameters.
        // e.g.:  
        //   FillDataset (conn, CommandType.StoredProcedure, "GetOrders", ds, new String() {"orders"}, new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -connection: A valid SqlConnection
        // -commandType: the CommandType (stored procedure, text, etc.)
        // -commandText: the stored procedure name or T-SQL command
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        // -commandParameters: An array of SqlParamters used to execute the command
        public static void FillDataset(SqlConnection connection, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params IDbDataParameter[] commandParameters)
        {

            FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);

        }

        // 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.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        // FillDataset (conn, "GetOrders", ds, new string() {"orders"}, 24, 36)
        // Parameters:
        // -connection: A valid SqlConnection
        // -spName: the name of the stored procedure
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        // -parameterValues: An array of objects to be assigned as the input values of the stored procedure
        public static void FillDataset(SqlConnection connection, string spName, DataSet dataSet, string[] tableNames, 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, 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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

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

                // Call the overload that takes an array of IDbDataParameters
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
            }
            else // Otherwise we can just call the SP without params
            {
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
            }

        }

        // Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
        // e.g.:  
        //   FillDataset (trans, CommandType.StoredProcedure, "GetOrders", ds, new string() {"orders"})
        // Parameters:
        // -transaction: A valid SqlTransaction
        // -commandType: the CommandType (stored procedure, text, etc.)
        // -commandText: the stored procedure name or T-SQL command
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        public static void FillDataset(SqlTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
        {

            FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
        }

        // Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
        // using the provided parameters.
        // e.g.:  
        //   FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string() {"orders"}, new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -transaction: A valid SqlTransaction
        // -commandType: the CommandType (stored procedure, text, etc.)
        // -commandText: the stored procedure name or T-SQL command
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        // -commandParameters: An array of SqlParamters used to execute the command
        public static void FillDataset(SqlTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params IDbDataParameter[] commandParameters)
        {

            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }
            FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);

        }

        // 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.
        // This method provides no access to output parameters or the stored procedure' s return value parameter.
        // e.g.:  
        //   FillDataset(trans, "GetOrders", ds, new String(){"orders"}, 24, 36)
        // Parameters:
        // -transaction: A valid SqlTransaction
        // -spName: the name of the stored procedure
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        // -parameterValues: An array of objects to be assigned as the input values of the stored procedure
        public static void FillDataset(SqlTransaction transaction, string spName, DataSet dataSet, string[] tableNames, 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, please provide an open transaction.", "transaction");
            }
            if (dataSet == null)
            {
                throw new ArgumentNullException("dataSet");
            }
            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

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

                // Call the overload that takes an array of IDbDataParameters
                FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
            }
            else // Otherwise we can just call the SP without params
            {
                FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
            }
        }

        // Private helper method that execute a SqlCommand (that returns a resultset) against the specified SqlTransaction and SqlConnection
        // using the provided parameters.
        // e.g.:  
        //   FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new String() {"orders"}, new IDbDataParameter("@prodid", 24))
        // Parameters:
        // -connection: A valid SqlConnection
        // -transaction: A valid SqlTransaction
        // -commandType: the CommandType (stored procedure, text, etc.)
        // -commandText: the stored procedure name or T-SQL command
        // -dataSet: A dataset wich will contain the resultset generated by the command
        // -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)
        // -commandParameters: An array of SqlParamters used to execute the command
        private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params IDbDataParameter[] 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, connection, transaction, commandType, commandText, commandParameters, ref mustCloseConnection);

            // Create the DataAdapter & DataSet
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

            try
            {
                // Add the table mappings specified by the user
                if (tableNames != null && tableNames.Length > 0)
                {

                    string tableName = "Table";
                    int index = 0;

                    for (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 = tableName + (index + 1).ToString();
                    }
                }


                command.CommandTimeout = SystemAppSettings.DataAccessCommandTimeout;
                // Fill the DataSet using default values for DataTable names, etc
                dataAdapter.Fill(dataSet);

                // Detach the IDbDataParameters from the command object, so they can be used again
                command.Parameters.Clear();
            }
            finally
            {
                if (dataAdapter != null)
                {
                    dataAdapter.Dispose();
                }
            }

            if (mustCloseConnection)
            {
                connection.Close();
            }

        }
        #endregion

        #region UpdateDataset
        // Executes the respective command for each inserted, updated, or deleted row in the DataSet.
        // e.g.:  
        //   UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order")
        // Parameters:
        // -insertCommand: A valid transact-SQL statement or stored procedure to insert new records into the data source
        // -deleteCommand: A valid transact-SQL statement or stored procedure to delete records from the data source
        // -updateCommand: A valid transact-SQL statement or stored procedure used to update records in the data source
        // -dataSet: the DataSet used to update the data source
        // -tableName: the DataTable used to update the data source
        public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
        {

            if (insertCommand == null)
            {
                throw new ArgumentNullException("insertCommand");
            }
            if (deleteCommand == null)
            {
                throw new ArgumentNullException("deleteCommand");
            }
            if (updateCommand == null)
            {
                throw new ArgumentNullException("updateCommand");
            }
            if (dataSet == null)
            {
                throw new ArgumentNullException("dataSet");
            }
            if (tableName == null || tableName.Length == 0)
            {
                throw new ArgumentNullException("tableName");
            }

            // Create a SqlDataAdapter, and dispose of it after we are done
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            try
            {
                // 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();
            }
            finally
            {
                if (dataAdapter != null)
                {
                    dataAdapter.Dispose();
                }
            }
        }
        #endregion

        #region CreateCommand
        // Simplify the creation of a Sql command object by allowing
        // a stored procedure and optional parameters to be provided
        // e.g.:  
        // Dim command As SqlCommand = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName")
        // Parameters:
        // -connection: A valid SqlConnection object
        // -spName: the name of the stored procedure
        // -sourceColumns: An array of string to be assigned as the source columns of the stored procedure parameters
        // Returns:
        // a valid SqlCommand object
        public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
        {

            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            // 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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Assign the provided source columns to these parameters based on parameter order
                int index = 0;
                for (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
        // 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.
        // Parameters:
        // -connectionString: A valid connection string for a SqlConnection
        // -spName: the name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values
        // Returns:
        // an int representing the number of rows affected by the command
        public static int ExecuteNonQueryTypedParams(string connectionString, string spName, DataRow dataRow)
        {
            int tempExecuteNonQueryTypedParams = 0;
            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteNonQueryTypedParams = SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteNonQueryTypedParams = SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
            return tempExecuteNonQueryTypedParams;
        }

        // 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.
        // Parameters:
        // -connection:a valid SqlConnection object
        // -spName: the name of the stored procedure
        // -dataRow:The dataRow used to hold the stored procedure' s parameter values.
        // Returns:
        // an int representing the number of rows affected by the command
        public static int ExecuteNonQueryTypedParams(SqlConnection connection, string spName, DataRow dataRow)
        {
            int tempExecuteNonQueryTypedParams = 0;
            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteNonQueryTypedParams = SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteNonQueryTypedParams = SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
            }
            return tempExecuteNonQueryTypedParams;
        }

        // 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.
        // Parameters:
        // -transaction:a valid SqlTransaction object
        // -spName:the name of the stored procedure
        // -dataRow:The dataRow used to hold the stored procedure' s parameter values.
        // Returns:
        // an int representing the number of rows affected by the command
        public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, string spName, DataRow dataRow)
        {
            int tempExecuteNonQueryTypedParams = 0;

            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open 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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteNonQueryTypedParams = SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {

                tempExecuteNonQueryTypedParams = SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
            }
            return tempExecuteNonQueryTypedParams;
        }
        #endregion

        #region ExecuteDatasetTypedParams
        // 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.
        // Parameters:
        // -connectionString: A valid connection string for a SqlConnection
        // -spName: the name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns:
        // a dataset containing the resultset generated by the command
        public static DataSet ExecuteDatasetTypedParams(string connectionString, string spName, DataRow dataRow)
        {
            DataSet tempExecuteDatasetTypedParams = null;
            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteDatasetTypedParams = SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {

                tempExecuteDatasetTypedParams = SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
            return tempExecuteDatasetTypedParams;
        }

        // 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.
        // Parameters:
        // -connection: A valid SqlConnection object
        // -spName: the name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns:
        // a dataset containing the resultset generated by the command
        public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, string spName, DataRow dataRow)
        {
            DataSet tempExecuteDatasetTypedParams = null;

            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteDatasetTypedParams = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {

                tempExecuteDatasetTypedParams = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
            }
            return tempExecuteDatasetTypedParams;
        }

        // 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.
        // Parameters:
        // -transaction: A valid SqlTransaction object
        // -spName: the name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns:
        // a dataset containing the resultset generated by the command
        public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, string spName, DataRow dataRow)
        {
            DataSet tempExecuteDatasetTypedParams = null;
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open 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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteDatasetTypedParams = SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {

                tempExecuteDatasetTypedParams = SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
            }
            return tempExecuteDatasetTypedParams;
        }
        #endregion

        #region ExecuteReaderTypedParams
        // 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.
        // Parameters:
        // -connectionString: A valid connection string for a SqlConnection
        // -spName: the name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns:
        // a SqlDataReader containing the resultset generated by the command
        public static SqlDataReader ExecuteReaderTypedParams(string connectionString, string spName, DataRow dataRow)
        {
            SqlDataReader tempExecuteReaderTypedParams = null;
            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteReaderTypedParams = SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteReaderTypedParams = SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
            return tempExecuteReaderTypedParams;
        }

        // 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.
        // Parameters:
        // -connection: A valid SqlConnection object
        // -spName: The name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns:
        // a SqlDataReader containing the resultset generated by the command
        public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, string spName, DataRow dataRow)
        {
            SqlDataReader tempExecuteReaderTypedParams = null;
            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteReaderTypedParams = SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteReaderTypedParams = SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
            }
            return tempExecuteReaderTypedParams;
        }

        // 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.
        // Parameters:
        // -transaction: A valid SqlTransaction object
        // -spName" The name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns:
        // a SqlDataReader containing the resultset generated by the command
        public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, string spName, DataRow dataRow)
        {
            SqlDataReader tempExecuteReaderTypedParams = null;
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open 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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteReaderTypedParams = SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteReaderTypedParams = SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
            }
            return tempExecuteReaderTypedParams;
        }
        #endregion

        #region ExecuteScalarTypedParams
        // 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.
        // Parameters:
        // -connectionString: A valid connection string for a SqlConnection
        // -spName: The name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns:
        // An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalarTypedParams(string connectionString, string spName, DataRow dataRow)
        {
            object tempExecuteScalarTypedParams = null;
            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteScalarTypedParams = SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteScalarTypedParams = SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
            return tempExecuteScalarTypedParams;
        }

        // 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.
        // Parameters:
        // -connection: A valid SqlConnection object
        // -spName: the name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns: 
        // an object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalarTypedParams(SqlConnection connection, string spName, DataRow dataRow)
        {
            object tempExecuteScalarTypedParams = null;
            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteScalarTypedParams = SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteScalarTypedParams = SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
            }
            return tempExecuteScalarTypedParams;
        }

        // 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.
        // Parameters:
        // -transaction: A valid SqlTransaction object
        // -spName: the name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns: 
        // an object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalarTypedParams(SqlTransaction transaction, string spName, DataRow dataRow)
        {
            object tempExecuteScalarTypedParams = null;
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open 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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteScalarTypedParams = SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteScalarTypedParams = SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
            }
            return tempExecuteScalarTypedParams;
        }
        #endregion

        #region ExecuteXmlReaderTypedParams
        // 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.
        // Parameters:
        // -connection: A valid SqlConnection object
        // -spName: the name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns: 
        // an XmlReader containing the resultset generated by the command
        public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, string spName, DataRow dataRow)
        {
            XmlReader tempExecuteXmlReaderTypedParams = null;
            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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteXmlReaderTypedParams = SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteXmlReaderTypedParams = SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
            }
            return tempExecuteXmlReaderTypedParams;
        }

        // 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.
        // Parameters:
        // -transaction: A valid SqlTransaction object
        // -spName: the name of the stored procedure
        // -dataRow: The dataRow used to hold the stored procedure' s parameter values.
        // Returns: 
        // an XmlReader containing the resultset generated by the command
        public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, string spName, DataRow dataRow)
        {
            XmlReader tempExecuteXmlReaderTypedParams = null;
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && (transaction.Connection == null))
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open 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)
                IDbDataParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

                tempExecuteXmlReaderTypedParams = SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                tempExecuteXmlReaderTypedParams = SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
            }
            return tempExecuteXmlReaderTypedParams;
        }
        #endregion
    } // SqlHelper

    // SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
    // ability to discover parameters for stored procedures at run-time.
    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()
        {
        } // New

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

        // resolve at run time the appropriate set of IDbDataParameters for a stored procedure
        // Parameters:
        // - connectionString - a valid connection string for a SqlConnection
        // - spName - the name of the stored procedure
        // - includeReturnValueParameter - whether or not to include their return value parameter>
        // Returns: IDbDataParameter()
        private static IDbDataParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter, params object[] parameterValues)
        {

            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;
            IDbDataParameter[] discoveredParameters = null;
            connection.Open();
            SqlCommandBuilder.DeriveParameters(cmd);
            connection.Close();
            if (!includeReturnValueParameter)
            {
                cmd.Parameters.RemoveAt(0);
            }

            discoveredParameters = new IDbDataParameter[cmd.Parameters.Count];
            cmd.Parameters.CopyTo(discoveredParameters, 0);

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

            return discoveredParameters;

        } // DiscoverSpParameterSet

        // Deep copy of cached IDbDataParameter array
        private static IDbDataParameter[] CloneParameters(IDbDataParameter[] originalParameters)
        {

            int i = 0;
            int j = originalParameters.Length - 1;
            IDbDataParameter[] clonedParameters = new IDbDataParameter[j + 1];

            for (i = 0; i <= j; i++)
            {
                clonedParameters[i] = (IDbDataParameter)(((ICloneable)(originalParameters[i])).Clone());
            }

            return clonedParameters;
        } // CloneParameters

        #endregion

        #region caching functions

        // add parameter array to the cache
        // Parameters
        // -connectionString - a valid connection string for a SqlConnection 
        // -commandText - the stored procedure name or T-SQL command 
        // -commandParameters - an array of SqlParamters to be cached 
        public static void CacheParameterSet(string connectionString, string commandText, params IDbDataParameter[] 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;
        } // CacheParameterSet

        // retrieve a parameter array from the cache
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection 
        // -commandText - the stored procedure name or T-SQL command 
        // Returns: An array of SqlParamters 
        public static IDbDataParameter[] 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;
            IDbDataParameter[] cachedParameters = (IDbDataParameter[])(paramCache[hashKey]);

            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        } // GetCachedParameterSet

        #endregion

        #region Parameter Discovery Functions
        // Retrieves the set of IDbDataParameters appropriate for the stored procedure.
        // This method will query the database for this information, and then store it in a cache for future requests.
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection 
        // -spName - the name of the stored procedure 
        // Returns: An array of IDbDataParameters
        public static IDbDataParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        } // GetSpParameterSet

        // Retrieves the set of IDbDataParameters appropriate for the stored procedure.
        // This method will query the database for this information, and then store it in a cache for future requests.
        // Parameters:
        // -connectionString - a valid connection string for a SqlConnection
        // -spName - the name of the stored procedure 
        // -includeReturnValueParameter - a bool value indicating whether the return value parameter should be included in the results 
        // Returns: An array of IDbDataParameters 
        public static IDbDataParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            IDbDataParameter[] tempGetSpParameterSet = null;
            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                if (connectionString == null || connectionString.Length == 0)
                {
                    throw new ArgumentNullException("connectionString");
                }
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(connectionString);
                    tempGetSpParameterSet = GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Dispose();
                    }
                }
            }
            return tempGetSpParameterSet;
        } // GetSpParameterSet

        // Retrieves the set of IDbDataParameters appropriate for the stored procedure.
        // This method will query the database for this information, and then store it in a cache for future requests.
        // Parameters:
        // -connection - a valid SqlConnection object
        // -spName - the name of the stored procedure 
        // -includeReturnValueParameter - a bool value indicating whether the return value parameter should be included in the results 
        // Returns: An array of IDbDataParameters 
        public static IDbDataParameter[] GetSpParameterSet(SqlConnection connection, string spName)
        {

            return GetSpParameterSet(connection, spName, false);
        } // GetSpParameterSet

        // Retrieves the set of IDbDataParameters appropriate for the stored procedure.
        // This method will query the database for this information, and then store it in a cache for future requests.
        // Parameters:
        // -connection - a valid SqlConnection object
        // -spName - the name of the stored procedure 
        // -includeReturnValueParameter - a bool value indicating whether the return value parameter should be included in the results 
        // Returns: An array of IDbDataParameters 
        public static IDbDataParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            IDbDataParameter[] tempGetSpParameterSet = null;
            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                if (connection == null)
                {
                    throw new ArgumentNullException("connection");
                }
                SqlConnection clonedConnection = null;
                try
                {
                    clonedConnection = (SqlConnection)((((ICloneable)connection).Clone()));
                    tempGetSpParameterSet = GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
                }
                finally
                {
                    if (clonedConnection != null)
                    {
                        clonedConnection.Dispose();
                    }
                }
            }
            return tempGetSpParameterSet;
        } // GetSpParameterSet

        // Retrieves the set of IDbDataParameters appropriate for the stored procedure.
        // This method will query the database for this information, and then store it in a cache for future requests.
        // Parameters:
        // -connection - a valid SqlConnection object
        // -spName - the name of the stored procedure 
        // -includeReturnValueParameter - a bool value indicating whether the return value parameter should be included in the results 
        // Returns: An array of IDbDataParameters 
        private static IDbDataParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {

            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            IDbDataParameter[] cachedParameters = null;
            string hashKey = null;

            if (spName == null || spName.Length == 0)
            {
                throw new ArgumentNullException("spName");
            }

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

            cachedParameters = (IDbDataParameter[])(paramCache[hashKey]);

            if (cachedParameters == null)
            {
                IDbDataParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }

            return CloneParameters(cachedParameters);

        } // GetSpParameterSet
        #endregion

    } // SqlHelperParameterCache
} //end of root namespace