Access——简单的数据库访问帮助类

using System;
using System.Collections.Generic;
using System.Threading;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

namespace Chn.gzGISer.DataBase.Access
{
    /// <summary>
    /// Access数据库访问帮助类
    /// </summary>
    public class AccessDataBase
    {
        private OleDbConnection _connection;
        /// <summary>
        /// 构造函数
        /// </summary>
        public AccessDataBase()
        {
            
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        public AccessDataBase(string connectionString)
        {
            _connection = new OleDbConnection(connectionString);
        }
        /// <summary>
        /// 从配置文件中初始化连接字段
        /// </summary>
        /// <param name="connectionStringName">数据库连接配置文件（.config）中的数据库连接字段的标签名</param>
        public void InitOleDbConnectionFromConfig(string connectionStringName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
            _connection = new OleDbConnection(connectionString);
        }
        /// <summary>
        /// 打开Connection连接
        /// </summary>
        public void EnsureConnectionOpen()
        {
            var retries = 3;
            if (_connection.State == ConnectionState.Open)
            {
                return;
            }
            else
            {
                while (retries >= 0 && _connection.State != ConnectionState.Open)
                {
                    _connection.Open();
                    retries--;
                    Thread.Sleep(30);
                }
            }
        }
        /// <summary>
        /// 关闭Connection连接
        /// </summary>
        public void EnsureConnectionClose()
        {
            if (_connection.State == ConnectionState.Open)
            {
                _connection.Close();
            }
        }
        /// <summary>
        /// 释放Connection连接
        /// </summary>
        public void Dispose()
        {
            if (_connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }
        }
        /// <summary>
        /// 执行 SQL 查询语句并返回一个表的数据副本
        /// </summary>
        /// <param name="commadText">SQL 语句</param>
        /// <param name="parameters">SQL 语句参数列表</param>
        /// <returns>表的数据副本</returns>
        public DataTable ExecuteQuery(string commadText, Dictionary<string, object> parameters)
        {
            if (string.IsNullOrEmpty(commadText))
            {
                throw new Exception("查询条件为空");
            }

            DataTable result = new DataTable();
            try
            {
                EnsureConnectionOpen();
                OleDbCommand command = CreatCommand(commadText, parameters);
                using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter())
                {
                    dataAdapter.Fill(result);
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                EnsureConnectionClose();
            }
            return result;
        }
        /// <summary>
        /// 执行非查询 SQL 语句
        /// </summary>
        /// <param name="commadText">SQL 语句</param>
        /// <param name="parameters">SQL 语句参数列表</param>
        /// <returns>受 SQL 语句影响的行数</returns>
        public int ExcuteNonQuery(string commadText, Dictionary<string, object> parameters)
        {
            if (string.IsNullOrEmpty(commadText))
            {
                throw new Exception("查询条件为空");
            }

            int result = 0;
            try
            {
                EnsureConnectionOpen();
                OleDbCommand command = CreatCommand(commadText, parameters);
                result = command.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                EnsureConnectionClose();
            }
            return result;
        }
        /// <summary>
        /// 执行一个 SQL 语句并返回一个标量
        /// </summary>
        /// <param name="commadText">SQL 语句</param>
        /// <param name="parameters">SQL 语句参数列表</param>
        /// <returns></returns>
        public object QueryValue(string commadText, Dictionary<string, object> parameters)
        {
            if (string.IsNullOrEmpty(commadText))
            {
                throw new Exception("查询条件为空");
            }

            object result = null;
            try
            {
                EnsureConnectionOpen();
                OleDbCommand command = CreatCommand(commadText, parameters);
                result = command.ExecuteScalar();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                EnsureConnectionClose();
            }
            return result;
        }
        private OleDbCommand CreatCommand(string commadText, Dictionary<string, object> parameters)
        {
            OleDbCommand command = _connection.CreateCommand();
            command.CommandText = commadText;
            AddParameters(command, parameters);
            return command;
        }
        private void AddParameters(OleDbCommand command, Dictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                return;
            }

            foreach (KeyValuePair<string, object> param in parameters)
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = param.Key;
                parameter.Value = param.Value ?? DBNull.Value;
                command.Parameters.Add(parameter);
            }
        }
    }
}