Home >Database >Mysql Tutorial >sqlhelper for access

sqlhelper for access

WBOY
WBOYOriginal
2016-06-07 15:31:161194browse

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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn