調(diào)用時如下:

        /// <param name="newtreeview">樹型控件名稱</param>
        /// <param name="treeviewname">一層的功能名稱</param>
        /// <param name="tablename">數(shù)據(jù)庫中的表名</param>
        /// <param name="parentnamefield">父節(jié)點的字段名</param>
        /// <param name="currentnamefield">節(jié)點的字段名</param>
        /// <param name="currentdatafield">節(jié)點的數(shù)據(jù)</param>
  

    newtreeinfobll.inittreedata(this.treeview1,"系統(tǒng)業(yè)務(wù)表","t_s_systemtableindex","pid000","name00","id0000");

有什么不懂的或不明白的地方請大家給我發(fā)email,謝謝,希望大家一起進(jìn)步



---------------------------------

treeinfo.cs  = 數(shù)據(jù)控制層

using system;
using system.windows.forms;
using system.data;
using system.data.sqlclient;

namespace dal
{
 /// <summary>
 /// treeinfo
 /// 樹型初使化數(shù)據(jù)庫的所有信息
 /// 喬高峰     2005-04-26
 /// 功能:實現(xiàn)所有樹型的初使化
 /// </summary>
 public class treeinfo
 {
//  private string parm_tablename;
  /// <summary>
  /// 取某表的所有數(shù)據(jù)(缺少表名)
  /// </summary>
  private  string sql_select_tree = "select * from ";
  /// <summary>
  /// 保存結(jié)果的數(shù)據(jù)集
  /// scf
  /// </summary>
  private dataset newdataset;
  /// <summary>
  /// 為過濾方便的視圖
  /// </summary>
  private dataview newdataview;    
        /// <summary>
        /// 無參數(shù)的構(gòu)造函數(shù)
        /// </summary>
  public treeinfo()
  {
  }
        /// <summary>
        /// 初使化樹型
        /// 喬高峰     2005-04-28
        /// </summary>
        /// <param name="newtreeview">樹型控件名稱</param>
        /// <param name="treeviewname">一層的功能名稱</param>
        /// <param name="tablename">數(shù)據(jù)庫中的表名</param>
        /// <param name="parentnamefield">父節(jié)點的字段名</param>
        /// <param name="currentnamefield">節(jié)點的字段名</param>
        /// <param name="currentdatafield">節(jié)點的數(shù)據(jù)</param>
  public void inittreedata(treeview newtreeview,string treeviewname,string tablename,string parentnamefield,string currentnamefield,string currentdatafield)
  {
   
   //增加第一層節(jié)點的名稱,為該樹的功能名稱
   treenode newtreeviewname = new treenode();
   //設(shè)置該節(jié)點的顯示文本
            newtreeviewname.text = treeviewname;
   ////樹型的圖標(biāo)
   //newtreeviewname.imageindex = ;
   ////選擇時的圖標(biāo)
   //newtreeviewname.selectedimageindex = ;
            newtreeview.nodes.add(newtreeviewname);
      //增加第二層數(shù)據(jù)庫里最高層的數(shù)據(jù)
   //從數(shù)據(jù)庫中取數(shù)據(jù)
   try
   {
   this.newdataset = new dataset();
   this.sql_select_tree = this.sql_select_tree + tablename;

    sqlhelper.filldataset(sqlhelper.conn_string,commandtype.text,this.sql_select_tree,this.newdataset,new string[] {tablename});
    this.newdataview = new dataview();
    this.newdataview.table = this.newdataset.tables[tablename];
   }
   catch(exception ee)
   {
       messagebox.show(ee.message);
   }
   createtreenodes(newtreeviewname,parentnamefield,"0",currentnamefield,currentdatafield);
  }
  /// <summary>
  /// 用遞歸的方法,生成樹型
  /// 喬高峰     2005-04-28
  /// </summary>
  /// <param name="newtreeviewname">一層的節(jié)點索引</param>
  /// <param name="parentnamefield">父節(jié)點的字段名</param>
  /// <param name="parentnamevalue">父節(jié)點的字段值</param>
  /// <param name="currentnamefield">節(jié)點的字段名</param>
  /// <param name="currentdatafield">節(jié)點的數(shù)據(jù)</param>
  public void createtreenodes(treenode newtreeviewname,string parentnamefield,string parentnamevalue,string currentnamefield,string currentdatafield) 
  {
   try
   {
    //規(guī)定父節(jié)點為0的為第一層節(jié)點
    this.newdataview.rowfilter = parentnamefield + " = '" + parentnamevalue+"'";  
    //判斷是否有記錄
    if (this.newdataview.count != 0)
    {

     //messagebox.show(this.newdataview.count.tostring()+ "運行");
     //遞歸運算
     foreach(datarowview newdatarowview in this.newdataview)
     {
      //在循環(huán)外有一條這個語句
      //這條語句是為了找回遞歸時動態(tài)失去的數(shù)據(jù)
      this.newdataview.rowfilter = parentnamefield + " = '" + parentnamevalue+"'";  
      //新增一個節(jié)點
      treenode newtreenode = new treenode();
      //設(shè)置該節(jié)點的顯示文本
      newtreenode.text = newdatarowview[currentnamefield].tostring().trim();
      //保存該節(jié)點的數(shù)據(jù) id
      newtreenode.tag = newdatarowview[currentdatafield].tostring().trim();
      //              //樹型的圖標(biāo)
      //                newtreenode.imageindex = ;
      //     //選擇時的圖標(biāo)
      //                newtreenode.selectedimageindex = ;
      //增回節(jié)點
      newtreeviewname.nodes.add(newtreenode);
   
      //遞歸運算
      createtreenodes(newtreenode,parentnamefield,newdatarowview[currentdatafield].tostring().trim(),currentnamefield,currentdatafield);
     }
    }


   }
   catch(exception ee)
   {
    messagebox.show(ee.message);
   }
  }

 }

-----------------------------------數(shù)據(jù)訪問層----------------------------

//===============================================================================
//數(shù)據(jù)訪問層中最基本的應(yīng)用程序塊(來自微軟microsoft application blocks for .net)
//提供所有有關(guān)操作sql server操作數(shù)據(jù)庫的功能
//喬高峰    2005-04-27
//===============================================================================
using system;
using system.data;
using system.xml;
using system.data.sqlclient;
using system.collections;
using system.configuration;
using system.windows.forms;

namespace dal
{
 /// <summary>
 /// the sqlhelper class is intended to encapsulate high performance, scalable best practices for
 /// common uses of sqlclient
 /// </summary>
 public sealed class sqlhelper
 {
  //聯(lián)接字符串
  public static readonly string conn_string =  configurationsettings.appsettings["sqlconnstring"] ;
  #region private utility methods & constructors

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  #endregion private utility methods & constructors

  #region executenonquery

  /// <summary>
  /// execute a sqlcommand (that returns no resultset and takes no parameters) against the database specified in
  /// the connection string
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  int result = executenonquery(connstring, commandtype.storedprocedure, "publishorders");
  /// </remarks>
  /// <param name="connectionstring">a valid connection string for a sqlconnection</param>
  /// <param name="commandtype">the commandtype (stored procedure, 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

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

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

 }
}