Access操作访问操作类

// ===============================================================================
// 功能：提供使用OleDb操作数据库的各种方法
// 说明：根据 Microsoft DataAcess Application Block for .Net 2.0(Sql Server 数据库)修改
// 内部版本： 1.0
// ===============================================================================

using System;
using System.Data;
using System.Configuration;
using System.Xml;
using System.Collections;
using System.Data.OleDb;
using System.Text;
using System.Web;

namespace DotNet.DataAccess.OleDb
{
/// <summary>
/// 文件功能：数据库连接类，取得数据库连接串，我们应用的是站点根目录
/// 作者：
/// 日期：2008-12-24
/// 备注：只需要在web.config中是这样配置的：
///
///       <appSettings>
///       <add key="DataBasePath" value="DataBase\NetIdea.mdb" />
///        </appSettings>
///
/// </summary>
public sealed class Database
{
  #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 Database()"
  private Database() {}

  /// <summary>
  /// 取得Access 数据库的连接字符串
  /// 在程序中调用：GetAccessConnstring("DataBasePath")即可

  public static string GetAccessConnstring(string FileKey)
  {
   System.Text.StringBuilder Connstring=new StringBuilder("");
   Connstring.Append(@"provider=microsoft.jet.oledb.4.0;data source=");
   Connstring.Append(System.Web.HttpContext.Current.Server.MapPath("~")).Append(@"\");
   Connstring.Append(ConfigurationSettings.AppSettings[FileKey].ToString().Trim());
   return Connstring.ToString();
  }

  /// <summary>
  /// 取回连接字符串
  /// </summary>
//  public static string ConnectionString
//  {
//   get {return ConfigurationSettings.AppSettings["ConnectionString"] ;}
//  }

  public static string ConnectionString
  {
   get {return Database.GetAccessConnstring("DataBasePath") ;}
  }

  /// <summary>
  /// This method is used to attach array of OleDbParameters to a OleDbCommand.
  ///
  /// 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 OleDbParameters to be added to command</param>
  private static void AttachParameters(OleDbCommand command, OleDbParameter[] commandParameters)
  {
   if( command == null ) throw new ArgumentNullException( "command" );
   if( commandParameters != null )
   {
    foreach (OleDbParameter 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 OleDbParameters
  /// </summary>
  /// <param name="commandParameters">Array of OleDbParameters to be assigned values</param>
  /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
  private static void AssignParameterValues(OleDbParameter[] commandParameters, DataRow dataRow)
  {
   if ((commandParameters == null) || (dataRow == null))
   {
    // Do nothing if we get no data
    return;
   }

   int i = 0;
   // Set the parameters values
   foreach(OleDbParameter 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 OleDbParameters
  /// </summary>
  /// <param name="commandParameters">Array of OleDbParameters to be assigned values</param>
  /// <param name="parameterValues">Array of objects holding the values to be assigned</param>
  public static void AssignParameterValues(OleDbParameter[] commandParameters,params 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 OleDbParameters, 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 OleDbCommand to be prepared</param>
  /// <param name="connection">A valid OleDbConnection, on which to execute this command</param>
  /// <param name="transaction">A valid OleDbTransaction, 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 OleDbParameters 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(OleDbCommand command, OleDbConnection connection, OleDbTransaction transaction, CommandType commandType, string commandText, OleDbParameter[] 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 OleDbCommand (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 OleDbConnection</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 OleDbParameters
   return ExecuteNonQuery(connectionString, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (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 OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connectionString">A valid connection string for a OleDbConnection</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 OleDbParameter[] commandParameters)
  {
   if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );

   // Create & open a OleDbConnection, and dispose of it after we are done
   using (OleDbConnection connection = new OleDbConnection(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 OleDbCommand (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 OleDbConnection</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)
    OleDbParameter[] commandParameters = ParamsCache.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 OleDbParameters
    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 OleDbCommand (that returns no resultset and takes no parameters) against the provided OleDbConnection.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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(OleDbConnection connection, CommandType commandType, string commandText)
  {
   // Pass through the call providing null for the set of OleDbParameters
   return ExecuteNonQuery(connection, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns no resultset) against the specified OleDbConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );

   // Create a command and prepare it for execution
   OleDbCommand cmd = new OleDbCommand();
   bool mustCloseConnection = false;
   PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

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

   // Detach the OleDbParameters 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 OleDbCommand (that returns no resultset) against the specified OleDbConnection
  /// 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 OleDbConnection</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(OleDbConnection connection, string spName, params object[] parameterValues)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );
   if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

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

    // Call the overload that takes an array of OleDbParameters
    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 OleDbCommand (that returns no resultset and takes no parameters) against the provided OleDbTransaction.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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(OleDbTransaction transaction, CommandType commandType, string commandText)
  {
   // Pass through the call providing null for the set of OleDbParameters
   return ExecuteNonQuery(transaction, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns no resultset) against the specified OleDbTransaction
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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(OleDbTransaction transaction, CommandType commandType, string commandText, params OleDbParameter[] 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
   OleDbCommand cmd = new OleDbCommand();
   bool mustCloseConnection = false;
   PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

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

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

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns no resultset) against the specified
  /// OleDbTransaction 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 OleDbTransaction</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(OleDbTransaction 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" );

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

    // Call the overload that takes an array of OleDbParameters
    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 OleDbCommand (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 OleDbConnection</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 OleDbParameters
   return ExecuteDataset(connectionString, commandType, commandText, (OleDbParameter[])null);
  }

   // Create & open a OleDbConnection, and dispose of it after we are done
   using (OleDbConnection connection = new OleDbConnection(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 OleDbCommand (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 OleDbConnection</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)
    OleDbParameter[] commandParameters = ParamsCache.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 OleDbParameters
    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 OleDbCommand (that returns a resultset and takes no parameters) against the provided OleDbConnection.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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(OleDbConnection connection, CommandType commandType, string commandText)
  {
   // Pass through the call providing null for the set of OleDbParameters
   return ExecuteDataset(connection, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns a resultset) against the specified OleDbConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );

   // Create a command and prepare it for execution
   OleDbCommand cmd = new OleDbCommand();
   bool mustCloseConnection = false;
   PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

   // Create the DataAdapter & DataSet
   using( OleDbDataAdapter da = new OleDbDataAdapter(cmd) )
   {
    DataSet ds = new DataSet();

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

    // Detach the OleDbParameters 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 OleDbCommand (that returns a resultset) against the specified OleDbConnection
  /// 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 OleDbConnection</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(OleDbConnection connection, string spName, params object[] parameterValues)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );
   if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

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

    // Call the overload that takes an array of OleDbParameters
    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 OleDbCommand (that returns a resultset and takes no parameters) against the provided OleDbTransaction.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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(OleDbTransaction transaction, CommandType commandType, string commandText)
  {
   // Pass through the call providing null for the set of OleDbParameters
   return ExecuteDataset(transaction, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns a resultset) against the specified OleDbTransaction
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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(OleDbTransaction transaction, CommandType commandType, string commandText, params OleDbParameter[] 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
   OleDbCommand cmd = new OleDbCommand();
   bool mustCloseConnection = false;
   PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

   // Create the DataAdapter & DataSet
   using( OleDbDataAdapter da = new OleDbDataAdapter(cmd) )
   {
    DataSet ds = new DataSet();

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

    // Detach the OleDbParameters 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 OleDbCommand (that returns a resultset) against the specified
  /// OleDbTransaction 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 OleDbTransaction</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(OleDbTransaction 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)
    OleDbParameter[] commandParameters = ParamsCache.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 OleDbParameters
    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 Database, so that
  /// we can set the appropriate CommandBehavior when calling ExecuteReader()
  /// </summary>
  private enum SqlConnectionOwnership
  {
   /// <summary>Connection is owned and managed by Database</summary>
   Internal,
   /// <summary>Connection is owned and managed by the caller</summary>
   External
  }

  /// <summary>
  /// Create and prepare a OleDbCommand, 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 OleDbConnection, on which to execute this command</param>
  /// <param name="transaction">A valid OleDbTransaction, 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 OleDbParameters 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 Database</param>
  /// <returns>OleDbDataReader containing the results of the command</returns>
  private static OleDbDataReader ExecuteReader(OleDbConnection connection, OleDbTransaction transaction, CommandType commandType, string commandText, OleDbParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );

   bool mustCloseConnection = false;
   // Create a command and prepare it for execution
   OleDbCommand cmd = new OleDbCommand();
   try
   {
    PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

    // Create a reader
    OleDbDataReader dataReader;

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

    // Detach the OleDbParameters 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(OleDbParameter commandParameter in cmd.Parameters)
//    {
//     if (commandParameter.Direction != ParameterDirection.Input)
//      canClear = false;
//    }
//
//    if (canClear)
//    {
//     cmd.Parameters.Clear();
//    }

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

  /// <summary>
  /// Execute a OleDbCommand (that returns a resultset and takes no parameters) against the database specified in
  /// the connection string.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// OleDbDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
  /// </remarks>
  /// <param name="connectionString">A valid connection string for a OleDbConnection</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
  {
   // Pass through the call providing null for the set of OleDbParameters
   return ExecuteReader(connectionString, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns a resultset) against the database specified in the connection string
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// OleDbDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connectionString">A valid connection string for a OleDbConnection</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
  {
   if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
   OleDbConnection connection = null;
   try
   {
    connection = new OleDbConnection(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 OleDbCommand (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.:
  /// OleDbDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
  /// </remarks>
  /// <param name="connectionString">A valid connection string for a OleDbConnection</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader 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))
   {
    OleDbParameter[] commandParameters = ParamsCache.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 OleDbCommand (that returns a resultset and takes no parameters) against the provided OleDbConnection.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// OleDbDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReader(OleDbConnection connection, CommandType commandType, string commandText)
  {
   // Pass through the call providing null for the set of OleDbParameters
   return ExecuteReader(connection, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns a resultset) against the specified OleDbConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// OleDbDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReader(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
  {
   // Pass through the call to the private overload using a null transaction value and an externally owned connection
   return ExecuteReader(connection, (OleDbTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
  }

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a resultset) against the specified OleDbConnection
  /// 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.:
  /// OleDbDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReader(OleDbConnection 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))
   {
    OleDbParameter[] commandParameters = ParamsCache.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 OleDbCommand (that returns a resultset and takes no parameters) against the provided OleDbTransaction.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// OleDbDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReader(OleDbTransaction transaction, CommandType commandType, string commandText)
  {
   // Pass through the call providing null for the set of OleDbParameters
   return ExecuteReader(transaction, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns a resultset) against the specified OleDbTransaction
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  ///   OleDbDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReader(OleDbTransaction transaction, CommandType commandType, string commandText, params OleDbParameter[] 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 OleDbCommand (that returns a resultset) against the specified
  /// OleDbTransaction 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.:
  /// OleDbDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReader(OleDbTransaction 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" );

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 OleDbCommand (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 OleDbConnection</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 OleDbParameters
   return ExecuteScalar(connectionString, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (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 OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connectionString">A valid connection string for a OleDbConnection</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 OleDbParameter[] commandParameters)
  {
   if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
   // Create & open a OleDbConnection, and dispose of it after we are done
   using (OleDbConnection connection = new OleDbConnection(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 OleDbCommand (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 OleDbConnection</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)
    OleDbParameter[] commandParameters = ParamsCache.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 OleDbParameters
    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 OleDbCommand (that returns a 1x1 resultset and takes no parameters) against the provided OleDbConnection.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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(OleDbConnection connection, CommandType commandType, string commandText)
  {
   // Pass through the call providing null for the set of OleDbParameters
   return ExecuteScalar(connection, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns a 1x1 resultset) against the specified OleDbConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );

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

   bool mustCloseConnection = false;
   PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

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

   // Detach the OleDbParameters 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 OleDbCommand (that returns a 1x1 resultset) against the specified OleDbConnection
  /// 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 OleDbConnection</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(OleDbConnection connection, string spName, params object[] parameterValues)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );
   if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

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

    // Call the overload that takes an array of OleDbParameters
    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 OleDbCommand (that returns a 1x1 resultset and takes no parameters) against the provided OleDbTransaction.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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(OleDbTransaction transaction, CommandType commandType, string commandText)
  {
   // Pass through the call providing null for the set of OleDbParameters
   return ExecuteScalar(transaction, commandType, commandText, (OleDbParameter[])null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns a 1x1 resultset) against the specified OleDbTransaction
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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(OleDbTransaction transaction, CommandType commandType, string commandText, params OleDbParameter[] 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
   OleDbCommand cmd = new OleDbCommand();
   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 OleDbParameters from the command object, so they can be used again
   cmd.Parameters.Clear();
   return retval;
  }

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a 1x1 resultset) against the specified
  /// OleDbTransaction 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 OleDbTransaction</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(OleDbTransaction 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)
    OleDbParameter[] commandParameters = ParamsCache.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 OleDbParameters
    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 FillDataset
  /// <summary>
  /// Execute a OleDbCommand (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 OleDbConnection</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 OleDbConnection, and dispose of it after we are done
   using (OleDbConnection connection = new OleDbConnection(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 OleDbCommand (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 OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connectionString">A valid connection string for a OleDbConnection</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 OleDbParameter[] commandParameters)
  {
   if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
   if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
   // Create & open a OleDbConnection, and dispose of it after we are done
   using (OleDbConnection connection = new OleDbConnection(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 OleDbCommand (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 OleDbConnection</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 OleDbConnection, and dispose of it after we are done
   using (OleDbConnection connection = new OleDbConnection(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 OleDbCommand (that returns a resultset and takes no parameters) against the provided OleDbConnection.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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(OleDbConnection connection, CommandType commandType,
   string commandText, DataSet dataSet, string[] tableNames)
  {
   FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns a resultset) against the specified OleDbConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</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(OleDbConnection connection, CommandType commandType,
   string commandText, DataSet dataSet, string[] tableNames,
   params OleDbParameter[] commandParameters)
  {
   FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
  }

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a resultset) against the specified OleDbConnection
  /// 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 OleDbConnection</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(OleDbConnection 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" );

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

    // Call the overload that takes an array of OleDbParameters
    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 OleDbCommand (that returns a resultset and takes no parameters) against the provided OleDbTransaction.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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(OleDbTransaction transaction, CommandType commandType,
   string commandText,
   DataSet dataSet, string[] tableNames)
  {
   FillDataset (transaction, commandType, commandText, dataSet, tableNames, null);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns a resultset) against the specified OleDbTransaction
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="transaction">A valid OleDbTransaction</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(OleDbTransaction transaction, CommandType commandType,
   string commandText, DataSet dataSet, string[] tableNames,
   params OleDbParameter[] commandParameters)
  {
   FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
  }

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a resultset) against the specified
  /// OleDbTransaction 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 OleDbTransaction</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(OleDbTransaction 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" );

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

    // Call the overload that takes an array of OleDbParameters
    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 OleDbCommand (that returns a resultset) against the specified OleDbTransaction and OleDbConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection</param>
  /// <param name="transaction">A valid OleDbTransaction</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(OleDbConnection connection, OleDbTransaction transaction, CommandType commandType,
   string commandText, DataSet dataSet, string[] tableNames,
   params OleDbParameter[] commandParameters)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );
   if( dataSet == null ) throw new ArgumentNullException( "dataSet" );

   // Create a command and prepare it for execution
   OleDbCommand command = new OleDbCommand();
   bool mustCloseConnection = false;
   PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

   // Create the DataAdapter & DataSet
   using( OleDbDataAdapter dataAdapter = new OleDbDataAdapter(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 OleDbParameters 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(OleDbCommand insertCommand, OleDbCommand deleteCommand, OleDbCommand 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 OleDbDataAdapter, and dispose of it after we are done
   using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter())
   {
    // 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.:
  /// OleDbCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
  /// </remarks>
  /// <param name="connection">A valid OleDbConnection 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 OleDbCommand object</returns>
  public static OleDbCommand CreateCommand(OleDbConnection 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 OleDbCommand
   OleDbCommand cmd = new OleDbCommand( 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)
    OleDbParameter[] commandParameters = ParamsCache.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 OleDbCommand object
    AttachParameters (cmd, commandParameters);
   }

   return cmd;
  }
  #endregion

  #region ExecuteNonQueryTypedParams
  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (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 OleDbConnection</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)
    OleDbParameter[] commandParameters = ParamsCache.GetSpParameterSet(connectionString, spName);

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

    return Database.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
   }
   else
   {
    return Database.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
   }
  }

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns no resultset) against the specified OleDbConnection
  /// 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 OleDbConnection 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(OleDbConnection connection, String spName, DataRow dataRow)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );
   if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns no resultset) against the specified
  /// OleDbTransaction 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 OleDbTransaction 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(OleDbTransaction 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)
    OleDbParameter[] commandParameters = ParamsCache.GetSpParameterSet(transaction.Connection, spName);

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

    return Database.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
   }
   else
   {
    return Database.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
   }
  }
  #endregion

  #region ExecuteDatasetTypedParams
  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (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 OleDbConnection</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" );

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a resultset) against the specified OleDbConnection
  /// 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 OleDbConnection 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(OleDbConnection connection, String spName, DataRow dataRow)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );
   if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a resultset) against the specified OleDbTransaction
  /// 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 OleDbTransaction 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(OleDbTransaction 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" );

#endregion

  #region ExecuteReaderTypedParams
  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (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 OleDbConnection</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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader 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)
    OleDbParameter[] commandParameters = ParamsCache.GetSpParameterSet(connectionString, spName);

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

    return Database.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
   }
   else
   {
    return Database.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
   }
  }

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a resultset) against the specified OleDbConnection
  /// 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 OleDbConnection 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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReaderTypedParams(OleDbConnection 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)
    OleDbParameter[] commandParameters = ParamsCache.GetSpParameterSet(connection, spName);

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

    return Database.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
   }
   else
   {
    return Database.ExecuteReader(connection, CommandType.StoredProcedure, spName);
   }
  }

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a resultset) against the specified OleDbTransaction
  /// 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 OleDbTransaction 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 OleDbDataReader containing the resultset generated by the command</returns>
  public static OleDbDataReader ExecuteReaderTypedParams(OleDbTransaction 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" );

  #region ExecuteScalarTypedParams
  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (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 OleDbConnection</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)
    OleDbParameter[] commandParameters = ParamsCache.GetSpParameterSet(connectionString, spName);

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

    return Database.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
   }
   else
   {
    return Database.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
   }
  }

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a 1x1 resultset) against the specified OleDbConnection
  /// 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 OleDbConnection 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(OleDbConnection connection, String spName, DataRow dataRow)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );
   if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

  /// <summary>
  /// Execute a stored procedure via a OleDbCommand (that returns a 1x1 resultset) against the specified OleDbTransaction
  /// 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 OleDbTransaction 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(OleDbTransaction 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" );

#region RunProc

  /// <summary>
  /// 执行一个没有参数的，只返回影响行数的存储过程。(注意，不能接收return value)
  /// </summary>
  /// <param name="procName"></param>
  /// <returns></returns>
  public static int RunProc(string procName)
  {
   return ExecuteNonQuery(Database.ConnectionString,CommandType.StoredProcedure,procName,(OleDbParameter[])null);
  }

  /// <summary>
  /// 执行一个带参数的，只返回影响行数的存储过程。(注意，不能接收return value)
  /// </summary>
  /// <param name="procName"></param>
  /// <param name="prams"></param>
  /// <returns></returns>
  public static int RunProc(string procName,OleDbParameter[] prams)
  {
   return ExecuteNonQuery(Database.ConnectionString,CommandType.StoredProcedure,procName,prams);
  }
  /// <summary>
  /// 执行一个不带参数的，返回OleDbDataReader的存储过程。
  /// </summary>
  /// <param name="procName"></param>
  /// <returns></returns>
  public static void RunProc(string procName,out OleDbDataReader reader)
  {

   reader = ExecuteReader(Database.ConnectionString,CommandType.StoredProcedure,procName,(OleDbParameter[])null);
  }

  /// <summary>
  /// 执行一个带参数的，返回OleDbDataReader的存储过程。
  /// </summary>
  /// <param name="procName"></param>
  /// <param name="prams"></param>
  /// <returns></returns>
  public static void RunProc(string procName,OleDbParameter[] prams,out OleDbDataReader reader)
  {
   reader = ExecuteReader(Database.ConnectionString,CommandType.StoredProcedure,procName,prams);
  }

  /// <summary>
  /// 执行一个不带参数的，返回DataSet的存储过程。
  /// </summary>
  /// <param name="procName"></param>
  /// <returns></returns>
  public static void RunProc(string procName,out DataSet ds)
  {
   ds = ExecuteDataset(Database.ConnectionString,CommandType.StoredProcedure,procName,(OleDbParameter[])null);
  }

  /// <summary>
  /// 执行一个带参数的，返回DataSet的存储过程。
  /// </summary>
  /// <param name="procName"></param>
  /// <param name="prams"></param>
  /// <returns></returns>
  public static void RunProc(string procName,OleDbParameter[] prams,out DataSet ds)
  {
   ds = ExecuteDataset(Database.ConnectionString,CommandType.StoredProcedure,procName,prams);
  }

  /// <summary>
  /// 执行一个不带参数的，返回结果集第一行第一列的数据。为object型，通过类型强制转换得到。
  /// 例如 System.Datetime dt = (System.Datetime)RunProc("GetDate");
  /// </summary>
  /// <param name="procName">存储过程名称</param>
  /// <returns></returns>
  public static void RunProc(string procName,out object obj)
  {
   obj = ExecuteScalar(Database.ConnectionString,CommandType.StoredProcedure,procName,(OleDbParameter[])null);
  }

  /// <summary>
  /// 执行一个带参数的，返回结果集第一行第一列的数据。为object型，通过类型强制转换得到。
  /// 例如 System.Datetime dt = (System.Datetime)RunProc("GetDate");
  /// </summary>
  /// <param name="procName"></param>
  /// <param name="prams"></param>
  /// <returns></returns>
  public static void RunProc(string procName,OleDbParameter[] prams,out object obj)
  {
   obj = ExecuteScalar(Database.ConnectionString,CommandType.StoredProcedure,procName,prams);
  }

#endregion

  #region Make OleDbParameters
  /// <summary>
  /// Make input param.
  /// </summary>
  /// <param name="ParamName">Name of param.</param>
  /// <param name="DbType">Param type.</param>
  /// <param name="Size">Param size.</param>
  /// <param name="Value">Param value.</param>
  /// <returns>New parameter.</returns>
  public static OleDbParameter MakeInParam(string ParamName, OleDbType DbType, int Size, object Value)
  {
   return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
  }

  /// <summary>
  /// Make input param.
  /// </summary>
  /// <param name="ParamName">Name of param.</param>
  /// <param name="DbType">Param type.</param>
  /// <param name="Size">Param size.</param>
  /// <returns>New parameter.</returns>
  public static OleDbParameter MakeOutParam(string ParamName, OleDbType DbType, int Size)
  {
   return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
  }

  /// <summary>
  /// Make stored procedure param.
  /// </summary>
  /// <param name="ParamName">Name of param.</param>
  /// <param name="DbType">Param type.</param>
  /// <param name="Size">Param size.</param>
  /// <param name="Direction">Parm direction.</param>
  /// <param name="Value">Param value.</param>
  /// <returns>New parameter.</returns>
  public static OleDbParameter MakeParam(string ParamName, OleDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
  {
   OleDbParameter param;

   if(Size > 0)
    param = new OleDbParameter(ParamName, DbType, Size);
   else
    param = new OleDbParameter(ParamName, DbType);

   param.Direction = Direction;
   if (!(Direction == ParameterDirection.Output && Value == null))
    param.Value = Value;

   return param;
  }

}
#endregion Make OleDbParameters

/// <summary>
/// 用来存取缓存OleDbParameter[]
/// </summary>
public sealed class ParamsCache
{
#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 ParamsCache()"
  private ParamsCache() {}

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

  /// <summary>
  /// Resolve at run time the appropriate set of OleDbParameters for a stored procedure
  /// </summary>
  /// <param name="connection">A valid OleDbConnection 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 OleDbParameter[] DiscoverSpParameterSet(OleDbConnection connection, string spName, bool includeReturnValueParameter)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );
   if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

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

   connection.Open();
   OleDbCommandBuilder.DeriveParameters(cmd);

   connection.Close();

   if (!includeReturnValueParameter)
   {
    cmd.Parameters.RemoveAt(0);
   }

   OleDbParameter[] discoveredParameters = new OleDbParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);

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

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

   for (int i = 0, j = originalParameters.Length; i < j; i++)
   {
    clonedParameters[i] = (OleDbParameter)((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 OleDbConnection</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 OleDbParameter[] 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 OleDbConnection</param>
  /// <param name="commandText">The stored procedure name or T-SQL command</param>
  /// <returns>An array of SqlParamters</returns>
  public static OleDbParameter[] 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;

   OleDbParameter[] cachedParameters = paramCache[hashKey] as OleDbParameter[];
   if (cachedParameters == null)
   {
    return null;
   }
   else
   {
    return CloneParameters(cachedParameters);
   }
  }
  /// <summary>
  /// 得到缓存中的存储过程参数。如果能得到，返回true，并out 参数出来，不能得到，则parms = null，返回false;
  /// 作者：肯定是Wintle:)
  /// </summary>
  /// <param name="connectiongString"></param>
  /// <param name="spName"></param>
  /// <param name="parms"></param>
  /// <returns></returns>
  public static bool GetCachedParameterSet(string connectionString,string spName,out OleDbParameter[] parms)
  {
   bool hasCached = false;
   if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
   if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
   string hashKey = connectionString + ":" + spName;

   OleDbParameter[] cachedParameters = paramCache[hashKey] as OleDbParameter[];

   if (cachedParameters == null)
   {
    parms = null;
   }
   else
   {
    parms = CloneParameters(cachedParameters);
    hasCached = true;
   }
   return hasCached;
  }

#endregion caching functions

#region Parameter Discovery Functions

  /// <summary>
  /// Retrieves the set of OleDbParameters 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 OleDbConnection</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 OleDbParameters</returns>
  public static OleDbParameter[] 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(OleDbConnection connection = new OleDbConnection(connectionString))
   {
    return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
   }
  }

  /// <summary>
  /// Retrieves the set of OleDbParameters 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 OleDbConnection 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 OleDbParameters</returns>
  internal static OleDbParameter[] GetSpParameterSet(OleDbConnection connection, string spName, bool includeReturnValueParameter)
  {
   if( connection == null ) throw new ArgumentNullException( "connection" );
   using (OleDbConnection clonedConnection = (OleDbConnection)((ICloneable)connection).Clone())
   {
    return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
   }
  }

  /// <summary>
  /// Retrieves the set of OleDbParameters appropriate for the stored procedure
  /// </summary>
  /// <param name="connection">A valid OleDbConnection 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 OleDbParameters</returns>
  private static OleDbParameter[] GetSpParameterSetInternal(OleDbConnection 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":"");

   OleDbParameter[] cachedParameters;

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

   return CloneParameters(cachedParameters);
  }

  #endregion Parameter Discovery Functions

}
}

posted @ 2006-11-30 09:56 google 阅读(110) 评论(0) 收藏举报

刷新页面返回顶部

Access操作访问操作类

公告