一个好的Access底层数据库类

using System; using System.Collections.Generic;

using System.Data; using System.Data.OleDb; using System.Configuration;

namespace MyShop.AccessDAL {     /// <summary>     /// 数据访问层，提供处理Access数据库的各种方法     /// </summary>     public sealed  class Database     {         public static string connectionString ;         static Database()         {             connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ToString();         }

        #region ExecuteNonQuery

        /// <summary>         /// execute the sql and return the count of line         /// </summary>         /// <param name="sql"></param>         /// <returns></returns>         public static  int ExecuteNonQuery(string commandText)         {             return ExecuteNonQuery(commandText, (OleDbParameter[])null);         }

        public static int ExecuteNonQuery(string commandText, OleDbParameter[] parameters)         {             CommandType text = CommandType.Text;             return ExecuteNonQuery(text, commandText, parameters);         }

 

 

 

        /// <summary>         /// Execute a OleDbCommand (that returns no resultset) against the database specified in the connection string         /// using the provided prams         /// </summary>         /// <Remarks>         /// e.g.:          ///  int result = ExecuteNonQuery( CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));         /// </Remarks>         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>         /// <param name="commandText">The stored procedure name or T-SQL command</param>         /// <param name="commandprams">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( CommandType commandType, string commandText, params OleDbParameter[] commandprams)         {             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, commandprams);             }         }

        /// <summary>         /// Execute a OleDbCommand (that returns no resultset) against the specified OleDbConnection         /// using the provided prams.         /// </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="commandprams">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[] commandprams)         {             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, commandprams, out mustCloseConnection);

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

            // Detach the OleDbprams from the command object, so they can be used again             cmd.Parameters.Clear();             if (mustCloseConnection)                 connection.Close();             return retval;         }

        public static int ExecuteNonQuery(CommandType commandType, out int intIdentity, string commandText, params OleDbParameter[] commandParameters)         {             return ExecuteNonQuery(connectionString, commandType, out intIdentity, commandText, commandParameters);         }

        public static int ExecuteNonQuery(string connectionString, CommandType commandType, out int intIdentity, string commandText, params OleDbParameter[] commandParameters)         {             if ((connectionString == null) || (connectionString.Length == 0))             {                 throw new ArgumentNullException("connectionString");             }             using (OleDbConnection connection = new OleDbConnection(connectionString))             {                 connection.Open();                 return ExecuteNonQuery(connection, commandType, out intIdentity, commandText, commandParameters);             }         }

          public static int ExecuteNonQuery(OleDbConnection connection, CommandType commandType, out int intIdentity, string commandText, params OleDbParameter[] commandParameters)         {             if (connection == null)             {                 throw new ArgumentNullException("connection");             }             OleDbCommand command = new OleDbCommand();             bool mustCloseConnection = false;             PrepareCommand(command, connection, null, commandType, commandText, commandParameters, out mustCloseConnection);             int num = command.ExecuteNonQuery();             command.Parameters.Clear();             command.CommandText = "SELECT @@identity";             intIdentity = Convert.ToInt32(command.ExecuteScalar());             if (mustCloseConnection)             {                 connection.Close();             }             return num;         }

        #endregion

        #region ExecuteScalar

        public static string ExecuteScalar(string commandText)         {             CommandType commandType =  CommandType.Text ;             object ec = ExecuteScalar(commandType, commandText);             if (ec == null)             {                 return "";             }             return ec.ToString();         }

        public static object ExecuteScalar( CommandType commandType, string commandText)         {             // Pass through the call providing null for the set of OleDbprams             return ExecuteScalar( commandType, commandText, (OleDbParameter[])null);         }

        /// <summary>         /// Execute a OleDbCommand (that returns a 1x1 resultset) against the database specified in the connection string         /// using the provided prams.         /// </summary>         /// <Remarks>         /// e.g.:          ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new OleDbParameter("@prodid", 24));         /// </Remarks>         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>         /// <param name="commandText">The stored procedure name or T-SQL command</param>         /// <param name="commandprams">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( CommandType commandType, string commandText, params OleDbParameter[] commandprams)         {             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, commandprams);             }         }

        /// <summary>         /// Execute a OleDbCommand (that returns a 1x1 resultset) against the specified OleDbConnection         /// using the provided prams.         /// </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="commandprams">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[] commandprams)         {

            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, commandprams, out mustCloseConnection);

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

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

            if (mustCloseConnection)                 connection.Close();

            return retval;         }

        #endregion

        #region ExecuteDataSet

        public static DataSet ExecuteDataSet(string commandText)         {             return ExecuteDataSet(commandText, (OleDbParameter[])null); ;         }

        /// <summary>         ///         /// </summary>         /// <param name="sql"></param>         /// <param name="prams"></param>         /// <returns></returns>         public static DataSet ExecuteDataSet(string commandText, OleDbParameter[] prams)         {             if (string.IsNullOrEmpty(commandText) )                 return null;             CommandType commandType = CommandType.Text;             return ExecuteDataSet(commandType, commandText, prams);         }

        /// <summary>         /// Execute a OleDbCommand (that returns a resultset) against the database specified in the connection string         /// using the provided prams.         /// </summary>         /// <Remarks>         /// e.g.:          ///  DataSet ds = ExecuteDataSet( CommandType.StoredProcedure, "GetOrders", new OleDbParameter("@prodid", 24));         /// </Remarks>         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>         /// <param name="commandText">The stored procedure name or T-SQL command</param>         /// <param name="commandprams">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( CommandType commandType, string commandText, params OleDbParameter[] commandprams)         {             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, commandprams);             }         }

        /// <summary>         /// Execute a OleDbCommand (that returns a resultset) against the specified OleDbConnection         /// using the provided prams.         /// </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="commandprams">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[] commandprams)         {             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, commandprams, 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 OleDbprams from the command object, so they can be used again                 cmd.Parameters.Clear();

                if (mustCloseConnection)                     connection.Close();

                // Return the dataset                 return ds;             }         }

        #endregion

        #region Make OleDbprams         /// <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

        #region PrepareCommand

        /// <summary>         /// This method opens (if necessary) and assigns a connection, transaction, command type and prams         /// 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="commandprams">An array of OleDbprams to be associated with the command or 'null' if no prams 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[] commandprams, 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 prams if they are provided             if (commandprams != null)             {                 Attachprams(command, commandprams);             }             return;         }

        /// <summary>         /// This method is used to attach array of OleDbprams 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 prams will be added</param>         /// <param name="commandprams">An array of OleDbprams to be added to command</param>         private static void Attachprams(OleDbCommand command, OleDbParameter[] commandprams)         {             if (command == null) throw new ArgumentNullException("command");             if (commandprams != null)             {                 foreach (OleDbParameter p in commandprams)                 {                     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);                     }                 }             }         }

        #endregion     } }