sqlhelper for access

using System; using System.Data; using System.Xml; using System.Data.OleDb; using System.Collections; using System.Configuration; namespace Microsoft.ApplicationBlocks.Data { /// summary /// The SqlHelper class is intended to encapsulate h

 using System;

using System.Data;

using System.Xml;

using System.Data.OleDb;

using System.Collections;

using System.Configuration;

namespace Microsoft.ApplicationBlocks.Data

{

    ///

    /// The SqlHelper class is intended to encapsulate high performance, scalable best practices for 

    /// common uses of OleDbClient

    ///

    public sealed class SqlHelper

    {

       

        public static readonly string Conn = System.Configuration.ConfigurationManager.AppSettings["Conn"].ToString() + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["dbPath"]) + ";"; 

        public static readonly string Conn2 = System.Configuration.ConfigurationManager.AppSettings["Conn2"].ToString() + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["dbPath2"]) + ";"; 

       

        #region private utility methods & constructors

     

        private SqlHelper() { }

       

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

                    }

                }

            }

        }

       

        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

                    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++;

            }

        }

        

        private static void AssignParameterValues(OleDbParameter[] commandParameters, object[] parameterValues)

        {

            if ((commandParameters == null) || (parameterValues == null))

            {

                // Do nothing if we get no data

                return;

            }

            // We must have the same number of values as we pave parameters to put them in

            if (commandParameters.Length != parameterValues.Length)

            {

                throw new ArgumentException("Parameter count does not match Parameter Value count.");

            }

            // Iterate through the OleDbParameters, assigning the values from the corresponding position in the 

            // value array

            for (int i = 0, j = commandParameters.Length; 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];

                }

            }

        }

      

        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 OleDb statement)

            command.CommandText = commandText;

            // If we were provided a transaction, assign it

            if (transaction != null)

            {

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

                command.Transaction = transaction;

            }

            // Set the command type

            command.CommandType = commandType;

            // Attach the command parameters if they are provided

            if (commandParameters != null)

            {

                AttachParameters(command, commandParameters);

            }

            return;

        }

        #endregion private utility methods & constructors

        #region ExecuteNonQuery

      

        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)

        {

            // Pass through the call providing null for the set of OleDbParameters

            return ExecuteNonQuery(connectionString, commandType, commandText, (OleDbParameter[])null);

        }

       

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

            }

        }

       

        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 = OleDbHelperParameterCache.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);

            }

        }

    

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

        }

       

        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;

        }

        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");

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

                // Assign the provided values to these parameters based on parameter order

                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes an array of OleDbParameters

                return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);

            }

            else

            {

                // Otherwise we can just call the SP without params

                return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);

            }

        }

      

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

        }

        

        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;

        }

     

        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");

            // 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 = OleDbHelperParameterCache.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 ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);

            }

            else

            {

                // Otherwise we can just call the SP without params

                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);

            }

        }

        #endregion ExecuteNonQuery

        #region ExecuteDataset

      

        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)

        {

            // Pass through the call providing null for the set of OleDbParameters

            return ExecuteDataset(connectionString, commandType, commandText, (OleDbParameter[])null);

        }

      

        public static DataSet ExecuteDataset(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 ExecuteDataset(connection, commandType, commandText, commandParameters);

            }

        }

      

        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 = OleDbHelperParameterCache.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);

            }

        }

      

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

        }

      

        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;

            }

        }

     

        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");

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

                // Assign the provided values to these parameters based on parameter order

                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes an array of OleDbParameters

                return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);

            }

            else

            {

                // Otherwise we can just call the SP without params

                return ExecuteDataset(connection, CommandType.StoredProcedure, spName);

            }

        }

       

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

        }

       

        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;

            }

        }

      

        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 = OleDbHelperParameterCache.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

        private enum OleDbConnectionOwnership

        {

            ///

Connection is owned and managed by SqlHelper

            Internal,

            ///

Connection is owned and managed by the caller

            External

        }

       

        private static OleDbDataReader ExecuteReader(OleDbConnection connection, OleDbTransaction transaction, CommandType commandType, string commandText, OleDbParameter[] commandParameters, OleDbConnectionOwnership 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 == OleDbConnectionOwnership.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 OleDbReader 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();

                }

                return dataReader;

            }

            catch

            {

                if (mustCloseConnection)

                    connection.Close();

                throw;

            }

        }

      

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

        }

      

        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, OleDbConnectionOwnership.Internal);

            }

            catch

            {

                // If we fail to return the OleDbDatReader, we need to close the connection ourselves

                if (connection != null) connection.Close();

                throw;

            }

        }

        

        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 = OleDbHelperParameterCache.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);

            }

        }

       

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

        }

       

        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, OleDbConnectionOwnership.External);

        }

      

        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 = OleDbHelperParameterCache.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);

            }

        }

       

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

        }

     

        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, OleDbConnectionOwnership.External);

        }

      

        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");

            // If we receive parameter values, we need to figure out where they go

            if ((parameterValues != null) && (parameterValues.Length > 0))

            {

                OleDbParameter[] commandParameters = OleDbHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);

            }

            else

            {

                // Otherwise we can just call the SP without params

                return ExecuteReader(transaction, CommandType.StoredProcedure, spName);

            }

        }

        #endregion ExecuteReader

        #region ExecuteScalar

     

        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)

        {

            // Pass through the call providing null for the set of OleDbParameters

            return ExecuteScalar(connectionString, commandType, commandText, (OleDbParameter[])null);

        }

     

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

            }

        }

      

        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 = OleDbHelperParameterCache.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);

            }

        }

      

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

        }

    

        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;

        }

      

        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");

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

                // Assign the provided values to these parameters based on parameter order

                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes an array of OleDbParameters

                return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParamet