欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-21 15:17:34
数据库操作类真的没有必要自己去写,因为成熟的类库真的非常完善了,拿来直接用就好,省时省力。 本文就为大家介绍微软官方的程序petshop4.0中的sqlhelper类,先...

数据库操作类真的没有必要自己去写,因为成熟的类库真的非常完善了,拿来直接用就好,省时省力。

本文就为大家介绍微软官方的程序petshop4.0中的sqlhelper类,先来做一下简单的介绍,petshop是一个范例,微软用它来展示.net企业系统开发的能力。

那sqlhelper中封装了哪些方法呢?

里面的函数一堆,常用的就那几个,无非就是增删改查嘛,来看下几种常用的函数:

1.executenonquery 执行增删改
2.executereader 执行查询
3.executescalar 返回首行首列

使用方法介绍

web.config配置

<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

  }