微软官方SqlHelper类 数据库辅助操作类 原创

<connectionstrings>
	<add name="connectionstring" connectionstring="server=127.0.0.1;uid=sa;pwd=ok;database=petshop;max pool size =512; min pool size=0; connection lifetime = 300;packet size=1000;" providername="system.data.sqlclient" />
</connectionstrings>

sql = "update student set name = @name where id = @id";
sqlhelper.executenonquery(commandtype.text, sql, new sqlparameter[]{
 new sqlparameter("@name", name),
 new sqlparameter("@id", id)
});

/// <summary>
  /// the sqlhelper class is intended to encapsulate high performance, 
  /// scalable best practices for common uses of sqlclient.
  /// </summary>
  public abstract class sqlhelper
  {

    //数据库连接字符串
    public static readonly string connectionstring = configurationmanager.connectionstrings["sqlconnstring"].connectionstring;

    #region 私有函数和方法

    /// <summary>
    /// this method is used to attach array of sqlparameters to a sqlcommand.
    /// 
    /// this method will assign a value of dbnull to any parameter with a direction of
    /// inputoutput and a value of null. 
    /// 
    /// this behavior will prevent default values from being used, but
    /// this will be the less common case than an intended pure output parameter (derived as inputoutput)
    /// where the user provided no input value.
    /// </summary>
    /// <param name="command">the command to which the parameters will be added</param>
    /// <param name="commandparameters">an array of sqlparameters to be added to command</param>
    private static void attachparameters(sqlcommand command, sqlparameter[] commandparameters)
    {
      if (command == null) throw new argumentnullexception("command");
      if (commandparameters != null)
      {
        foreach (sqlparameter p in commandparameters)
        {
          if (p != null)
          {
            // check for derived output value with no value assigned
            if ((p.direction == parameterdirection.inputoutput ||
              p.direction == parameterdirection.input) &&
              (p.value == null))
            {
              p.value = dbnull.value;
            }
            command.parameters.add(p);
          }
        }
      }
    }

    /// <summary>
    /// this method assigns datarow column values to an array of sqlparameters
    /// </summary>
    /// <param name="commandparameters">array of sqlparameters to be assigned values</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values</param>
    private static void assignparametervalues(sqlparameter[] commandparameters, datarow datarow)
    {
      if ((commandparameters == null) || (datarow == null))
      {
        // do nothing if we get no data
        return;
      }

      int i = 0;
      // set the parameters values
      foreach (sqlparameter commandparameter in commandparameters)
      {
        // check the parameter name
        if (commandparameter.parametername == null ||
          commandparameter.parametername.length <= 1)
          throw new exception(
            string.format(
              "please provide a valid parameter name on the parameter #{0}, the parametername property has the following value: '{1}'.",
              i, commandparameter.parametername));
        if (datarow.table.columns.indexof(commandparameter.parametername.substring(1)) != -1)
          commandparameter.value = datarow[commandparameter.parametername.substring(1)];
        i++;
      }
    }

    /// <summary>
    /// this method assigns an array of values to an array of sqlparameters
    /// </summary>
    /// <param name="commandparameters">array of sqlparameters to be assigned values</param>
    /// <param name="parametervalues">array of objects holding the values to be assigned</param>
    private static void assignparametervalues(sqlparameter[] commandparameters, object[] parametervalues)
    {
      if ((commandparameters == null) || (parametervalues == null))
      {
        // do nothing if we get no data
        return;
      }

      // we must have the same number of values as we pave parameters to put them in
      if (commandparameters.length != parametervalues.length)
      {
        throw new argumentexception("parameter count does not match parameter value count.");
      }

      // iterate through the sqlparameters, assigning the values from the corresponding position in the 
      // value array
      for (int i = 0, j = commandparameters.length; i < j; i++)
      {
        // if the current array value derives from idbdataparameter, then assign its value property
        if (parametervalues[i] is idbdataparameter)
        {
          idbdataparameter paraminstance = (idbdataparameter)parametervalues[i];
          if (paraminstance.value == null)
          {
            commandparameters[i].value = dbnull.value;
          }
          else
          {
            commandparameters[i].value = paraminstance.value;
          }
        }
        else if (parametervalues[i] == null)
        {
          commandparameters[i].value = dbnull.value;
        }
        else
        {
          commandparameters[i].value = parametervalues[i];
        }
      }
    }

    /// <summary>
    /// this method opens (if necessary) and assigns a connection, transaction, command type and parameters 
    /// to the provided command
    /// </summary>
    /// <param name="command">the sqlcommand to be prepared</param>
    /// <param name="connection">a valid sqlconnection, on which to execute this command</param>
    /// <param name="transaction">a valid sqltransaction, or 'null'</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparameters to be associated with the command or 'null' if no parameters are required</param>
    /// <param name="mustcloseconnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
    private static void preparecommand(sqlcommand command, sqlconnection connection, sqltransaction transaction, commandtype commandtype, string commandtext, sqlparameter[] commandparameters, out bool mustcloseconnection)
    {
      if (command == null) throw new argumentnullexception("command");
      if (commandtext == null || commandtext.length == 0) throw new argumentnullexception("commandtext");

      // if the provided connection is not open, we will open it
      if (connection.state != connectionstate.open)
      {
        mustcloseconnection = true;
        connection.open();
      }
      else
      {
        mustcloseconnection = false;
      }

      // associate the connection with the command
      command.connection = connection;

      // set the command text (stored procedure name or sql statement)
      command.commandtext = commandtext;

      // if we were provided a transaction, assign it
      if (transaction != null)
      {
        if (transaction.connection == null) throw new argumentexception("the transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        command.transaction = transaction;
      }

      // set the command type
      command.commandtype = commandtype;

      // attach the command parameters if they are provided
      if (commandparameters != null)
      {
        attachparameters(command, commandparameters);
      }
      return;
    }

    #endregion private utility methods & constructors

    #region executenonquery

    public static int executenonquery(commandtype cmdtype, string cmdtext)
    {
      return executenonquery(connectionstring, cmdtype, cmdtext);
    }

    public static int executenonquery(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparameters)
    {
      return executenonquery(connectionstring, cmdtype, cmdtext, commandparameters);
    }

    /// <summary>
    /// execute a sqlcommand (that returns no resultset and takes no parameters) against the database specified in 
    /// the connection string
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int result = executenonquery(connstring, commandtype.storedprocedure, "publishorders");
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquery(string connectionstring, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executenonquery(connectionstring, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns no resultset) against the database specified in the connection string 
    /// using the provided parameters
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int result = executenonquery(connstring, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquery(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");

      // create & open a sqlconnection, and dispose of it after we are done
      using (sqlconnection connection = new sqlconnection(connectionstring))
      {
        connection.open();

        // call the overload that takes a connection in place of the connection string
        return executenonquery(connection, commandtype, commandtext, commandparameters);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns no resultset) against the database specified in 
    /// the connection string using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// int result = executenonquery(connstring, "publishorders", 24, 36);
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored prcedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquery(string connectionstring, string spname, params object[] parametervalues)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executenonquery(connectionstring, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executenonquery(connectionstring, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns no resultset and takes no parameters) against the provided sqlconnection. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int result = executenonquery(conn, commandtype.storedprocedure, "publishorders");
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquery(sqlconnection connection, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executenonquery(connection, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns no resultset) against the specified sqlconnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int result = executenonquery(conn, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquery(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (connection == null) throw new argumentnullexception("connection");

      // create a command and prepare it for execution
      sqlcommand cmd = new sqlcommand();
      bool mustcloseconnection = false;
      preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection);

      // finally, execute the command
      int retval = cmd.executenonquery();

      // detach the sqlparameters from the command object, so they can be used again
      cmd.parameters.clear();
      if (mustcloseconnection)
        connection.close();
      return retval;
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns no resultset) against the specified sqlconnection 
    /// using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// int result = executenonquery(conn, "publishorders", 24, 36);
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquery(sqlconnection connection, string spname, params object[] parametervalues)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executenonquery(connection, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executenonquery(connection, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns no resultset and takes no parameters) against the provided sqltransaction. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int result = executenonquery(trans, commandtype.storedprocedure, "publishorders");
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquery(sqltransaction transaction, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executenonquery(transaction, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns no resultset) against the specified sqltransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int result = executenonquery(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquery(sqltransaction transaction, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (transaction == null) throw new argumentnullexception("transaction");
      if (transaction != null && transaction.connection == null) throw new argumentexception("the transaction was rollbacked or commited, please provide an open transaction.", "transaction");

      // create a command and prepare it for execution
      sqlcommand cmd = new sqlcommand();
      bool mustcloseconnection = false;
      preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection);

      // finally, execute the command
      int retval = cmd.executenonquery();

      // detach the sqlparameters from the command object, so they can be used again
      cmd.parameters.clear();
      return retval;
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns no resultset) against the specified 
    /// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// int result = executenonquery(conn, trans, "publishorders", 24, 36);
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquery(sqltransaction transaction, 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");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executenonquery(transaction, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executenonquery(transaction, commandtype.storedprocedure, spname);
      }
    }

    #endregion executenonquery

    #region executedataset

    public static dataset executedataset(commandtype commandtype, string commandtext)
    {
      return executedataset(connectionstring, commandtype, commandtext);
    }

    public static dataset executedataset(commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      return executedataset(connectionstring, commandtype, commandtext, commandparameters);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the database specified in 
    /// the connection string. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// dataset ds = executedataset(connstring, commandtype.storedprocedure, "getorders");
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedataset(string connectionstring, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executedataset(connectionstring, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the database specified in the connection string 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// dataset ds = executedataset(connstring, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedataset(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");

      // create & open a sqlconnection, and dispose of it after we are done
      using (sqlconnection connection = new sqlconnection(connectionstring))
      {
        connection.open();

        // call the overload that takes a connection in place of the connection string
        return executedataset(connection, commandtype, commandtext, commandparameters);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in 
    /// the connection string using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// dataset ds = executedataset(connstring, "getorders", 24, 36);
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedataset(string connectionstring, string spname, params object[] parametervalues)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executedataset(connectionstring, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executedataset(connectionstring, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqlconnection. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// dataset ds = executedataset(conn, commandtype.storedprocedure, "getorders");
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedataset(sqlconnection connection, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executedataset(connection, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// dataset ds = executedataset(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedataset(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (connection == null) throw new argumentnullexception("connection");

      // create a command and prepare it for execution
      sqlcommand cmd = new sqlcommand();
      bool mustcloseconnection = false;
      preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection);

      // create the dataadapter & dataset
      using (sqldataadapter da = new sqldataadapter(cmd))
      {
        dataset ds = new dataset();

        // fill the dataset using default values for datatable names, etc
        da.fill(ds);

        // detach the sqlparameters from the command object, so they can be used again
        cmd.parameters.clear();

        if (mustcloseconnection)
          connection.close();

        // return the dataset
        return ds;
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// dataset ds = executedataset(conn, "getorders", 24, 36);
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedataset(sqlconnection connection, string spname, params object[] parametervalues)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executedataset(connection, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executedataset(connection, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqltransaction. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// dataset ds = executedataset(trans, commandtype.storedprocedure, "getorders");
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedataset(sqltransaction transaction, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executedataset(transaction, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the specified sqltransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// dataset ds = executedataset(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedataset(sqltransaction transaction, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (transaction == null) throw new argumentnullexception("transaction");
      if (transaction != null && transaction.connection == null) throw new argumentexception("the transaction was rollbacked or commited, please provide an open transaction.", "transaction");

      // create a command and prepare it for execution
      sqlcommand cmd = new sqlcommand();
      bool mustcloseconnection = false;
      preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection);

      // create the dataadapter & dataset
      using (sqldataadapter da = new sqldataadapter(cmd))
      {
        dataset ds = new dataset();

        // fill the dataset using default values for datatable names, etc
        da.fill(ds);

        // detach the sqlparameters from the command object, so they can be used again
        cmd.parameters.clear();

        // return the dataset
        return ds;
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified 
    /// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// dataset ds = executedataset(trans, "getorders", 24, 36);
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedataset(sqltransaction transaction, 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");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executedataset(transaction, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executedataset(transaction, commandtype.storedprocedure, spname);
      }
    }

    #endregion executedataset

    #region executereader

    /// <summary>
    /// this enum is used to indicate whether the connection was provided by the caller, or created by sqlhelper, so that
    /// we can set the appropriate commandbehavior when calling executereader()
    /// </summary>
    private enum sqlconnectionownership
    {
      /// <summary>connection is owned and managed by sqlhelper</summary>
      internal,
      /// <summary>connection is owned and managed by the caller</summary>
      external
    }

    public static sqldatareader executereader(commandtype cmdtype, string cmdtext)
    {
      return executereader(connectionstring, cmdtype, cmdtext);
    }

    public static sqldatareader executereader(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparameters)
    {
      return executereader(connectionstring, cmdtype, cmdtext, commandparameters);
    }

    /// <summary>
    /// create and prepare a sqlcommand, and call executereader with the appropriate commandbehavior.
    /// </summary>
    /// <remarks>
    /// if we created and opened the connection, we want the connection to be closed when the datareader is closed.
    /// 
    /// if the caller provided the connection, we want to leave it to them to manage.
    /// </remarks>
    /// <param name="connection">a valid sqlconnection, on which to execute this command</param>
    /// <param name="transaction">a valid sqltransaction, or 'null'</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparameters to be associated with the command or 'null' if no parameters are required</param>
    /// <param name="connectionownership">indicates whether the connection parameter was provided by the caller, or created by sqlhelper</param>
    /// <returns>sqldatareader containing the results of the command</returns>
    private static sqldatareader executereader(sqlconnection connection, sqltransaction transaction, commandtype commandtype, string commandtext, sqlparameter[] commandparameters, sqlconnectionownership connectionownership)
    {
      if (connection == null) throw new argumentnullexception("connection");

      bool mustcloseconnection = false;
      // create a command and prepare it for execution
      sqlcommand cmd = new sqlcommand();
      try
      {
        preparecommand(cmd, connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection);

        // create a reader
        sqldatareader datareader;

        // call executereader with the appropriate commandbehavior
        if (connectionownership == sqlconnectionownership.external)
        {
          datareader = cmd.executereader();
        }
        else
        {
          datareader = cmd.executereader(commandbehavior.closeconnection);
        }

        // detach the sqlparameters from the command object, so they can be used again.
        // hack: there is a problem here, the output parameter values are fletched 
        // when the reader is closed, so if the parameters are detached from the command
        // then the sqlreader can磘 set its values. 
        // when this happen, the parameters can磘 be used again in other command.
        bool canclear = true;
        foreach (sqlparameter commandparameter in cmd.parameters)
        {
          if (commandparameter.direction != parameterdirection.input)
            canclear = false;
        }

        if (canclear)
        {
          cmd.parameters.clear();
        }

        return datareader;
      }
      catch
      {
        if (mustcloseconnection)
          connection.close();
        throw;
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the database specified in 
    /// the connection string. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// sqldatareader dr = executereader(connstring, commandtype.storedprocedure, "getorders");
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereader(string connectionstring, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executereader(connectionstring, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the database specified in the connection string 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// sqldatareader dr = executereader(connstring, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereader(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      sqlconnection connection = null;
      try
      {
        connection = new sqlconnection(connectionstring);
        connection.open();

        // call the private overload that takes an internally owned connection in place of the connection string
        return executereader(connection, null, commandtype, commandtext, commandparameters, sqlconnectionownership.internal);
      }
      catch
      {
        // if we fail to return the sqldatreader, we need to close the connection ourselves
        if (connection != null) connection.close();
        throw;
      }

    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in 
    /// the connection string using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// sqldatareader dr = executereader(connstring, "getorders", 24, 36);
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereader(string connectionstring, string spname, params object[] parametervalues)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);

        assignparametervalues(commandparameters, parametervalues);

        return executereader(connectionstring, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executereader(connectionstring, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqlconnection. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// sqldatareader dr = executereader(conn, commandtype.storedprocedure, "getorders");
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereader(sqlconnection connection, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executereader(connection, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// sqldatareader dr = executereader(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereader(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      // pass through the call to the private overload using a null transaction value and an externally owned connection
      return executereader(connection, (sqltransaction)null, commandtype, commandtext, commandparameters, sqlconnectionownership.external);
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// sqldatareader dr = executereader(conn, "getorders", 24, 36);
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereader(sqlconnection connection, string spname, params object[] parametervalues)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        assignparametervalues(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);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqltransaction. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// sqldatareader dr = executereader(trans, commandtype.storedprocedure, "getorders");
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereader(sqltransaction transaction, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executereader(transaction, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the specified sqltransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    ///  sqldatareader dr = executereader(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereader(sqltransaction transaction, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (transaction == null) throw new argumentnullexception("transaction");
      if (transaction != null && transaction.connection == null) throw new argumentexception("the transaction was rollbacked or commited, 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);
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified
    /// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// sqldatareader dr = executereader(trans, "getorders", 24, 36);
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereader(sqltransaction transaction, 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");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        assignparametervalues(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);
      }
    }

    #endregion executereader

    #region executescalar

    public static object executescalar(commandtype cmdtype, string cmdtext)
    {
      return executescalar(connectionstring, cmdtype, cmdtext);
    }

    public static object executescalar(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparameters)
    {
      return executescalar(connectionstring, cmdtype, cmdtext, commandparameters);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a 1x1 resultset and takes no parameters) against the database specified in 
    /// the connection string. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int ordercount = (int)executescalar(connstring, commandtype.storedprocedure, "getordercount");
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalar(string connectionstring, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executescalar(connectionstring, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a 1x1 resultset) against the database specified in the connection string 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int ordercount = (int)executescalar(connstring, commandtype.storedprocedure, "getordercount", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalar(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      // create & open a sqlconnection, and dispose of it after we are done
      using (sqlconnection connection = new sqlconnection(connectionstring))
      {
        connection.open();

        // call the overload that takes a connection in place of the connection string
        return executescalar(connection, commandtype, commandtext, commandparameters);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the database specified in 
    /// the connection string using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// int ordercount = (int)executescalar(connstring, "getordercount", 24, 36);
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalar(string connectionstring, string spname, params object[] parametervalues)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executescalar(connectionstring, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executescalar(connectionstring, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a 1x1 resultset and takes no parameters) against the provided sqlconnection. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int ordercount = (int)executescalar(conn, commandtype.storedprocedure, "getordercount");
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalar(sqlconnection connection, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executescalar(connection, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a 1x1 resultset) against the specified sqlconnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int ordercount = (int)executescalar(conn, commandtype.storedprocedure, "getordercount", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalar(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (connection == null) throw new argumentnullexception("connection");

      // create a command and prepare it for execution
      sqlcommand cmd = new sqlcommand();

      bool mustcloseconnection = false;
      preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection);

      // execute the command & return the results
      object retval = cmd.executescalar();

      // detach the sqlparameters from the command object, so they can be used again
      cmd.parameters.clear();

      if (mustcloseconnection)
        connection.close();

      return retval;
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the specified sqlconnection 
    /// using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// int ordercount = (int)executescalar(conn, "getordercount", 24, 36);
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalar(sqlconnection connection, string spname, params object[] parametervalues)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executescalar(connection, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executescalar(connection, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a 1x1 resultset and takes no parameters) against the provided sqltransaction. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int ordercount = (int)executescalar(trans, commandtype.storedprocedure, "getordercount");
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalar(sqltransaction transaction, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executescalar(transaction, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a 1x1 resultset) against the specified sqltransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// int ordercount = (int)executescalar(trans, commandtype.storedprocedure, "getordercount", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalar(sqltransaction transaction, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (transaction == null) throw new argumentnullexception("transaction");
      if (transaction != null && transaction.connection == null) throw new argumentexception("the transaction was rollbacked or commited, please provide an open transaction.", "transaction");

      // create a command and prepare it for execution
      sqlcommand cmd = new sqlcommand();
      bool mustcloseconnection = false;
      preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection);

      // execute the command & return the results
      object retval = cmd.executescalar();

      // detach the sqlparameters from the command object, so they can be used again
      cmd.parameters.clear();
      return retval;
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the specified
    /// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// int ordercount = (int)executescalar(trans, "getordercount", 24, 36);
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalar(sqltransaction transaction, 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");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // ppull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executescalar(transaction, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executescalar(transaction, commandtype.storedprocedure, spname);
      }
    }

    #endregion executescalar

    #region executexmlreader

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqlconnection. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// xmlreader r = executexmlreader(conn, commandtype.storedprocedure, "getorders");
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command using "for xml auto"</param>
    /// <returns>an xmlreader containing the resultset generated by the command</returns>
    public static xmlreader executexmlreader(sqlconnection connection, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executexmlreader(connection, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// xmlreader r = executexmlreader(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command using "for xml auto"</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>an xmlreader containing the resultset generated by the command</returns>
    public static xmlreader executexmlreader(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (connection == null) throw new argumentnullexception("connection");

      bool mustcloseconnection = false;
      // create a command and prepare it for execution
      sqlcommand cmd = new sqlcommand();
      try
      {
        preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection);

        // create the dataadapter & dataset
        xmlreader retval = cmd.executexmlreader();

        // detach the sqlparameters from the command object, so they can be used again
        cmd.parameters.clear();

        return retval;
      }
      catch
      {
        if (mustcloseconnection)
          connection.close();
        throw;
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// xmlreader r = executexmlreader(conn, "getorders", 24, 36);
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="spname">the name of the stored procedure using "for xml auto"</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>an xmlreader containing the resultset generated by the command</returns>
    public static xmlreader executexmlreader(sqlconnection connection, string spname, params object[] parametervalues)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executexmlreader(connection, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executexmlreader(connection, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqltransaction. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// xmlreader r = executexmlreader(trans, commandtype.storedprocedure, "getorders");
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command using "for xml auto"</param>
    /// <returns>an xmlreader containing the resultset generated by the command</returns>
    public static xmlreader executexmlreader(sqltransaction transaction, commandtype commandtype, string commandtext)
    {
      // pass through the call providing null for the set of sqlparameters
      return executexmlreader(transaction, commandtype, commandtext, (sqlparameter[])null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the specified sqltransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// xmlreader r = executexmlreader(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command using "for xml auto"</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <returns>an xmlreader containing the resultset generated by the command</returns>
    public static xmlreader executexmlreader(sqltransaction transaction, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
    {
      if (transaction == null) throw new argumentnullexception("transaction");
      if (transaction != null && transaction.connection == null) throw new argumentexception("the transaction was rollbacked or commited, please provide an open transaction.", "transaction");

      // create a command and prepare it for execution
      sqlcommand cmd = new sqlcommand();
      bool mustcloseconnection = false;
      preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection);

      // create the dataadapter & dataset
      xmlreader retval = cmd.executexmlreader();

      // detach the sqlparameters from the command object, so they can be used again
      cmd.parameters.clear();
      return retval;
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified 
    /// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// xmlreader r = executexmlreader(trans, "getorders", 24, 36);
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static xmlreader executexmlreader(sqltransaction transaction, 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");

      // if we receive parameter values, we need to figure out where they go
      if ((parametervalues != null) && (parametervalues.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        return executexmlreader(transaction, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        // otherwise we can just call the sp without params
        return executexmlreader(transaction, commandtype.storedprocedure, spname);
      }
    }

    #endregion executexmlreader

    #region filldataset
    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the database specified in 
    /// the connection string. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// filldataset(connstring, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"});
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)</param>
    public static void filldataset(string connectionstring, 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
      using (sqlconnection 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);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the database specified in the connection string 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// filldataset(connstring, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    public static void filldataset(string connectionstring, commandtype commandtype,
      string commandtext, dataset dataset, string[] tablenames,
      params sqlparameter[] commandparameters)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (dataset == null) throw new argumentnullexception("dataset");
      // create & open a sqlconnection, and dispose of it after we are done
      using (sqlconnection connection = new sqlconnection(connectionstring))
      {
        connection.open();

        // call the overload that takes a connection in place of the connection string
        filldataset(connection, commandtype, commandtext, dataset, tablenames, commandparameters);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in 
    /// the connection string using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// filldataset(connstring, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, 24);
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>  
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    public static void filldataset(string connectionstring, 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
      using (sqlconnection 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);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqlconnection. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// filldataset(conn, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"});
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>  
    public static void filldataset(sqlconnection connection, commandtype commandtype,
      string commandtext, dataset dataset, string[] tablenames)
    {
      filldataset(connection, commandtype, commandtext, dataset, tablenames, null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// filldataset(conn, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    public static void filldataset(sqlconnection connection, commandtype commandtype,
      string commandtext, dataset dataset, string[] tablenames,
      params sqlparameter[] commandparameters)
    {
      filldataset(connection, null, commandtype, commandtext, dataset, tablenames, commandparameters);
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// filldataset(conn, "getorders", ds, new string[] {"orders"}, 24, 36);
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    public static void filldataset(sqlconnection connection, 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)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        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);
      }
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqltransaction. 
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// filldataset(trans, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"});
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    public static void filldataset(sqltransaction transaction, commandtype commandtype,
      string commandtext,
      dataset dataset, string[] tablenames)
    {
      filldataset(transaction, commandtype, commandtext, dataset, tablenames, null);
    }

    /// <summary>
    /// execute a sqlcommand (that returns a resultset) against the specified sqltransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// filldataset(trans, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    public static void filldataset(sqltransaction transaction, commandtype commandtype,
      string commandtext, dataset dataset, string[] tablenames,
      params sqlparameter[] commandparameters)
    {
      filldataset(transaction.connection, transaction, commandtype, commandtext, dataset, tablenames, commandparameters);
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified 
    /// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// this method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.: 
    /// filldataset(trans, "getorders", ds, new string[]{"orders"}, 24, 36);
    /// </remarks>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
    public static void filldataset(sqltransaction transaction, 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)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // assign the provided values to these parameters based on parameter order
        assignparametervalues(commandparameters, parametervalues);

        // call the overload that takes an array of sqlparameters
        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);
      }
    }

    /// <summary>
    /// private helper method that execute a sqlcommand (that returns a resultset) against the specified sqltransaction and sqlconnection
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// filldataset(conn, trans, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, new sqlparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">a valid sqlconnection</param>
    /// <param name="transaction">a valid sqltransaction</param>
    /// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
    /// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
    private static void filldataset(sqlconnection connection, sqltransaction transaction, commandtype commandtype,
      string commandtext, dataset dataset, string[] tablenames,
      params sqlparameter[] commandparameters)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (dataset == null) throw new argumentnullexception("dataset");

      // create a command and prepare it for execution
      sqlcommand command = new sqlcommand();
      bool mustcloseconnection = false;
      preparecommand(command, connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection);

      // create the dataadapter & dataset
      using (sqldataadapter dataadapter = new sqldataadapter(command))
      {

        // add the table mappings specified by the user
        if (tablenames != null && tablenames.length > 0)
        {
          string tablename = "table";
          for (int index = 0; index < tablenames.length; index++)
          {
            if (tablenames[index] == null || tablenames[index].length == 0) throw new argumentexception("the tablenames parameter must contain a list of tables, a value was provided as null or empty string.", "tablenames");
            dataadapter.tablemappings.add(tablename, tablenames[index]);
            tablename += (index + 1).tostring();
          }
        }

        // fill the dataset using default values for datatable names, etc
        dataadapter.fill(dataset);

        // detach the sqlparameters from the command object, so they can be used again
        command.parameters.clear();
      }

      if (mustcloseconnection)
        connection.close();
    }
    #endregion

    #region updatedataset
    /// <summary>
    /// executes the respective command for each inserted, updated, or deleted row in the dataset.
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// updatedataset(conn, insertcommand, deletecommand, updatecommand, dataset, "order");
    /// </remarks>
    /// <param name="insertcommand">a valid transact-sql statement or stored procedure to insert new records into the data source</param>
    /// <param name="deletecommand">a valid transact-sql statement or stored procedure to delete records from the data source</param>
    /// <param name="updatecommand">a valid transact-sql statement or stored procedure used to update records in the data source</param>
    /// <param name="dataset">the dataset used to update the data source</param>
    /// <param name="tablename">the datatable used to update the data source.</param>
    public static void updatedataset(sqlcommand insertcommand, sqlcommand deletecommand, sqlcommand updatecommand, 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 (tablename == null || tablename.length == 0) throw new argumentnullexception("tablename");

      // create a sqldataadapter, and dispose of it after we are done
      using (sqldataadapter dataadapter = new sqldataadapter())
      {
        // set the data adapter commands
        dataadapter.updatecommand = updatecommand;
        dataadapter.insertcommand = insertcommand;
        dataadapter.deletecommand = deletecommand;

        // update the dataset changes in the data source
        dataadapter.update(dataset, tablename);

        // commit all the changes made to the dataset
        dataset.acceptchanges();
      }
    }
    #endregion

    #region createcommand
    /// <summary>
    /// simplify the creation of a sql command object by allowing
    /// a stored procedure and optional parameters to be provided
    /// </summary>
    /// <remarks>
    /// e.g.: 
    /// sqlcommand command = createcommand(conn, "addcustomer", "customerid", "customername");
    /// </remarks>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="sourcecolumns">an array of string to be assigned as the source columns of the stored procedure parameters</param>
    /// <returns>a valid sqlcommand object</returns>
    public static sqlcommand createcommand(sqlconnection connection, string spname, params string[] sourcecolumns)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // create a sqlcommand
      sqlcommand cmd = new sqlcommand(spname, connection);
      cmd.commandtype = commandtype.storedprocedure;

      // if we receive parameter values, we need to figure out where they go
      if ((sourcecolumns != null) && (sourcecolumns.length > 0))
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // assign the provided source columns to these parameters based on parameter order
        for (int index = 0; index < sourcecolumns.length; index++)
          commandparameters[index].sourcecolumn = sourcecolumns[index];

        // attach the discovered parameters to the sqlcommand object
        attachparameters(cmd, commandparameters);
      }

      return cmd;
    }
    #endregion

    #region executenonquerytypedparams
    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns no resultset) against the database specified in 
    /// the connection string using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquerytypedparams(string connectionstring, string spname, datarow datarow)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executenonquery(connectionstring, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executenonquery(connectionstring, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns no resultset) against the specified sqlconnection 
    /// using the datarow column values as the stored procedure's parameters values. 
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquerytypedparams(sqlconnection connection, string spname, datarow datarow)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executenonquery(connection, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executenonquery(connection, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns no resultset) against the specified
    /// sqltransaction using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="transaction">a valid sqltransaction object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int executenonquerytypedparams(sqltransaction transaction, string spname, datarow datarow)
    {
      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");

      // sf the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executenonquery(transaction, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executenonquery(transaction, commandtype.storedprocedure, spname);
      }
    }
    #endregion

    #region executedatasettypedparams
    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in 
    /// the connection string using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedatasettypedparams(string connectionstring, string spname, datarow datarow)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      //if the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executedataset(connectionstring, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executedataset(connectionstring, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the datarow column values as the store procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedatasettypedparams(sqlconnection connection, string spname, datarow datarow)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executedataset(connection, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executedataset(connection, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqltransaction 
    /// using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="transaction">a valid sqltransaction object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>a dataset containing the resultset generated by the command</returns>
    public static dataset executedatasettypedparams(sqltransaction transaction, string spname, datarow datarow)
    {
      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)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executedataset(transaction, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executedataset(transaction, commandtype.storedprocedure, spname);
      }
    }

    #endregion

    #region executereadertypedparams
    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in 
    /// the connection string using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereadertypedparams(string connectionstring, string spname, datarow datarow)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executereader(connectionstring, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executereader(connectionstring, commandtype.storedprocedure, spname);
      }
    }


    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereadertypedparams(sqlconnection connection, string spname, datarow datarow)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executereader(connection, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executereader(connection, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqltransaction 
    /// using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="transaction">a valid sqltransaction object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>a sqldatareader containing the resultset generated by the command</returns>
    public static sqldatareader executereadertypedparams(sqltransaction transaction, string spname, datarow datarow)
    {
      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)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executereader(transaction, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executereader(transaction, commandtype.storedprocedure, spname);
      }
    }
    #endregion

    #region executescalartypedparams
    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the database specified in 
    /// the connection string using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalartypedparams(string connectionstring, string spname, datarow datarow)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executescalar(connectionstring, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executescalar(connectionstring, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the specified sqlconnection 
    /// using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalartypedparams(sqlconnection connection, string spname, datarow datarow)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executescalar(connection, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executescalar(connection, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the specified sqltransaction
    /// using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="transaction">a valid sqltransaction object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    public static object executescalartypedparams(sqltransaction transaction, string spname, datarow datarow)
    {
      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)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executescalar(transaction, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executescalar(transaction, commandtype.storedprocedure, spname);
      }
    }
    #endregion

    #region executexmlreadertypedparams
    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection 
    /// using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>an xmlreader containing the resultset generated by the command</returns>
    public static xmlreader executexmlreadertypedparams(sqlconnection connection, string spname, datarow datarow)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      // if the row has values, the store procedure parameters must be initialized
      if (datarow != null && datarow.itemarray.length > 0)
      {
        // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executexmlreader(connection, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executexmlreader(connection, commandtype.storedprocedure, spname);
      }
    }

    /// <summary>
    /// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqltransaction 
    /// using the datarow column values as the stored procedure's parameters values.
    /// this method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="transaction">a valid sqltransaction object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
    /// <returns>an xmlreader containing the resultset generated by the command</returns>
    public static xmlreader executexmlreadertypedparams(sqltransaction transaction, string spname, datarow datarow)
    {
      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)
        sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);

        // set the parameters values
        assignparametervalues(commandparameters, datarow);

        return sqlhelper.executexmlreader(transaction, commandtype.storedprocedure, spname, commandparameters);
      }
      else
      {
        return sqlhelper.executexmlreader(transaction, commandtype.storedprocedure, spname);
      }
    }
    #endregion

  }

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

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

    private static hashtable paramcache = hashtable.synchronized(new hashtable());

    /// <summary>
    /// resolve at run time the appropriate set of sqlparameters for a stored procedure
    /// </summary>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="includereturnvalueparameter">whether or not to include their return value parameter</param>
    /// <returns>the parameter array discovered.</returns>
    private static sqlparameter[] discoverspparameterset(sqlconnection connection, string spname, bool includereturnvalueparameter)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      sqlcommand cmd = new sqlcommand(spname, connection);
      cmd.commandtype = commandtype.storedprocedure;

      connection.open();
      sqlcommandbuilder.deriveparameters(cmd);
      connection.close();

      if (!includereturnvalueparameter)
      {
        cmd.parameters.removeat(0);
      }

      sqlparameter[] discoveredparameters = new sqlparameter[cmd.parameters.count];

      cmd.parameters.copyto(discoveredparameters, 0);

      // init the parameters with a dbnull value
      foreach (sqlparameter discoveredparameter in discoveredparameters)
      {
        discoveredparameter.value = dbnull.value;
      }
      return discoveredparameters;
    }

    /// <summary>
    /// deep copy of cached sqlparameter array
    /// </summary>
    /// <param name="originalparameters"></param>
    /// <returns></returns>
    private static sqlparameter[] cloneparameters(sqlparameter[] originalparameters)
    {
      sqlparameter[] clonedparameters = new sqlparameter[originalparameters.length];

      for (int i = 0, j = originalparameters.length; i < j; i++)
      {
        clonedparameters[i] = (sqlparameter)((icloneable)originalparameters[i]).clone();
      }

      return clonedparameters;
    }

    #endregion private methods, variables, and constructors

    #region caching functions

    /// <summary>
    /// add parameter array to the cache
    /// </summary>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <param name="commandparameters">an array of sqlparamters to be cached</param>
    public static void cacheparameterset(string connectionstring, string commandtext, params sqlparameter[] commandparameters)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (commandtext == null || commandtext.length == 0) throw new argumentnullexception("commandtext");

      string hashkey = connectionstring + ":" + commandtext;

      paramcache[hashkey] = commandparameters;
    }

    /// <summary>
    /// retrieve a parameter array from the cache
    /// </summary>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="commandtext">the stored procedure name or t-sql command</param>
    /// <returns>an array of sqlparamters</returns>
    public static sqlparameter[] getcachedparameterset(string connectionstring, string commandtext)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (commandtext == null || commandtext.length == 0) throw new argumentnullexception("commandtext");

      string hashkey = connectionstring + ":" + commandtext;

      sqlparameter[] cachedparameters = paramcache[hashkey] as sqlparameter[];
      if (cachedparameters == null)
      {
        return null;
      }
      else
      {
        return cloneparameters(cachedparameters);
      }
    }

    #endregion caching functions

    #region parameter discovery functions

    /// <summary>
    /// retrieves the set of sqlparameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// this method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <returns>an array of sqlparameters</returns>
    public static sqlparameter[] getspparameterset(string connectionstring, string spname)
    {
      return getspparameterset(connectionstring, spname, false);
    }

    /// <summary>
    /// retrieves the set of sqlparameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// this method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="includereturnvalueparameter">a bool value indicating whether the return value parameter should be included in the results</param>
    /// <returns>an array of sqlparameters</returns>
    public static sqlparameter[] getspparameterset(string connectionstring, string spname, bool includereturnvalueparameter)
    {
      if (connectionstring == null || connectionstring.length == 0) throw new argumentnullexception("connectionstring");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      using (sqlconnection connection = new sqlconnection(connectionstring))
      {
        return getspparametersetinternal(connection, spname, includereturnvalueparameter);
      }
    }

    /// <summary>
    /// retrieves the set of sqlparameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// this method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <returns>an array of sqlparameters</returns>
    internal static sqlparameter[] getspparameterset(sqlconnection connection, string spname)
    {
      return getspparameterset(connection, spname, false);
    }

    /// <summary>
    /// retrieves the set of sqlparameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// this method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="includereturnvalueparameter">a bool value indicating whether the return value parameter should be included in the results</param>
    /// <returns>an array of sqlparameters</returns>
    internal static sqlparameter[] getspparameterset(sqlconnection connection, string spname, bool includereturnvalueparameter)
    {
      if (connection == null) throw new argumentnullexception("connection");
      using (sqlconnection clonedconnection = (sqlconnection)((icloneable)connection).clone())
      {
        return getspparametersetinternal(clonedconnection, spname, includereturnvalueparameter);
      }
    }

    /// <summary>
    /// retrieves the set of sqlparameters appropriate for the stored procedure
    /// </summary>
    /// <param name="connection">a valid sqlconnection object</param>
    /// <param name="spname">the name of the stored procedure</param>
    /// <param name="includereturnvalueparameter">a bool value indicating whether the return value parameter should be included in the results</param>
    /// <returns>an array of sqlparameters</returns>
    private static sqlparameter[] getspparametersetinternal(sqlconnection connection, string spname, bool includereturnvalueparameter)
    {
      if (connection == null) throw new argumentnullexception("connection");
      if (spname == null || spname.length == 0) throw new argumentnullexception("spname");

      string hashkey = connection.connectionstring + ":" + spname + (includereturnvalueparameter ? ":include returnvalue parameter" : "");

      sqlparameter[] cachedparameters;

      cachedparameters = paramcache[hashkey] as sqlparameter[];
      if (cachedparameters == null)
      {
        sqlparameter[] spparameters = discoverspparameterset(connection, spname, includereturnvalueparameter);
        paramcache[hashkey] = spparameters;
        cachedparameters = spparameters;
      }

      return cloneparameters(cachedparameters);
    }

    #endregion parameter discovery functions

  }