Access数据库的ADO.NET访问类

/**********************************************************************************************************************************
 * 开发者：丁丁
 * 网址：http://bbs.prana.cn/
 * 博客：http://blog.csdn.net/greenvc/
 * 日期：2009年12月8日
 * 说明：Access数据库操作类
**********************************************************************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;

namespace Dingy.Data
{
    /// <summary>
    /// Access数据库访问类
    /// </summary>
    public class OleDB
    {
        #region 参数定义

        /// <summary>
        /// Access的数据库链接字符串，需要在配置文件中提供如下节点内容
        /// <connectionStrings>
        ///        <add name="OleConnString" connectionString="Provider=Microsoft.Jet.OleDb.4.0;Data Source=数据库路径/数据库名称;Persist Security Info=True;"/>
        /// </connectionStrings>
        /// </summary>
        protected readonly string OleConnString = System.Configuration.ConfigurationManager.ConnectionStrings["OleConnString"].ConnectionString;
       
        /// <summary>
        /// 数据库链接对象
        /// </summary>
        private OleDbConnection OleDbConn;

        /// <summary>
        /// Hashtable中缓存的参数
        /// </summary>
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 数据库事务对象
        /// </summary>
        private OleDbTransaction _oleDbTran;

        public OleDbTransaction oleDbTran
        {
            get
            {
                return _oleDbTran;
            }
            set
            {
                _oleDbTran = value;
            }
        }

        #endregion
       
        /// <summary>
        /// 构造函数,创建数据库连接
        /// </summary>
        public OleDB()
        {
            if (OleDbConn == null)
            {
                OleDbConn = new OleDbConnection(OleConnString);
                OleDbConn.Open();
            }
        }

        #region 标准操作

        /// <summary>
        /// 创建Sql命令对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>成功创建的OleDbCommand对象</returns>
        private OleDbCommand CreateOleDbCommand(string Sql)
        {
            OleDbCommand oleDbCmd = new OleDbCommand();
            oleDbCmd.Connection = OleDbConn;
            oleDbCmd.CommandType = CommandType.Text;
            oleDbCmd.CommandText = Sql;

            return oleDbCmd;
        }

        /// <summary>
        /// 执行 SQL 语句并返回受影响的行数
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string Sql)
        {
            return CreateOleDbCommand(Sql).ExecuteNonQuery();
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>结果集中的第一行第一列</returns>
        public object ExecuteScalar(string Sql)
        {
            return CreateOleDbCommand(Sql).ExecuteScalar();
        }

        /// <summary>
        /// 执行查询，并返回查询所返回OleDbDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>OleDbDataReader</returns>
        public OleDbDataReader ExecuteReader(string Sql)
        {
            return CreateOleDbCommand(Sql).ExecuteReader();
        }

        /// <summary>
        /// 执行查询，并返回查询的结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>结果数据集</returns>
        public DataSet ExecuteDataset(string Sql)
        {
            OleDbDataAdapter da = new OleDbDataAdapter(CreateOleDbCommand(Sql));
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 执行查询，并返回查询的DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql)
        {
            OleDbDataAdapter da = new OleDbDataAdapter(CreateOleDbCommand(Sql));
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);
            return dataTable;
        }

        /// <summary>
        /// 执行查询，返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataset(string Sql, int PageIndex, int PageSize)
        {
            //设置导入的起始地址
            int firstPage = PageIndex * PageSize;

            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(CreateOleDbCommand(Sql));
            da.Fill(ds, firstPage, PageSize, "PranaTable");

            return ds;
        }

        #endregion

        #region 事务操作

        /// <summary>
        /// 打开数据库链接
        /// </summary>
        public void OpenConnection()
        {
            if (OleDbConn != null && OleDbConn.State != ConnectionState.Open)
            {
                OleDbConn.Open();
            }
        }

        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public void CloseConnection()
        {
            if (OleDbConn != null && OleDbConn.State == ConnectionState.Open)
            {
                OleDbConn.Close();
            }
        }

        /// <summary>
        /// 开始一个数据库事务
        /// </summary>
        public void BeginTransaction()
        {
            if (OleDbConn.State == ConnectionState.Closed)
            {
                OleDbConn.Open();
            }

            try
            {
                oleDbTran = OleDbConn.BeginTransaction();
            }
            catch (OleDbException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }

        /// <summary>
        /// 开始一个数据库事务
        /// </summary>
        /// <param name="aIsolationLevel">连接的事务锁定行为</param>
        public void BeginTransaction(IsolationLevel isolationLevel)
        {
            try
            {
                oleDbTran = OleDbConn.BeginTransaction(isolationLevel);
            }
            catch (OleDbException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }

        /// <summary>
        /// 提交一个数据库事务
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                oleDbTran.Commit();
                CloseConnection();
            }
            catch (OleDbException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }

        /// <summary>
        /// 回退一个数据库事务
        /// </summary>
        public void RollBackTransaction()
        {
            try
            {
                oleDbTran.Rollback();
                CloseConnection();
            }
            catch (OleDbException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }

        /// <summary>
        /// 准备命令对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="trans">事务</param>
        /// <param name="cmdParms">命令参数</param>
        private void PrepareCommand(string Sql, OleDbCommand oleDbCommand, OleDbParameter[] cmdParms)
        {
            if(OleDbConn.State != ConnectionState.Open)
            {
                OleDbConn.Open();
            }

            oleDbCommand.Connection = OleDbConn;
            oleDbCommand.CommandText = Sql;

            oleDbCommand.Transaction = oleDbTran;

            if (cmdParms != null)
            {
                foreach (OleDbParameter parm in cmdParms)
                {
                    oleDbCommand.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// 执行 SQL 语句并返回受影响的行数
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="cmdParms">参数对象数组</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string Sql, params OleDbParameter[] cmdParms)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(Sql, cmd, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
           
            return val;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="cmdParms">参数对象数组</param>
        /// <returns>结果集中的第一行第一列</returns>
        public object ExecuteScalar(string Sql, params OleDbParameter[] cmdParms)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(Sql, cmd, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();

            return val;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回OleDbDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="cmdParms">参数对象数组</param>
        /// <returns>OleDbDataReader</returns>
        public OleDbDataReader ExecuteReader(string Sql, params OleDbParameter[] cmdParms)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(Sql, cmd, cmdParms);
            OleDbDataReader OleDbDR = cmd.ExecuteReader();
            cmd.Parameters.Clear();

            return OleDbDR;
        }

        /// <summary>
        /// 执行查询，并返回查询的DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="cmdParms">参数对象数组</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql, params OleDbParameter[] cmdParms)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(Sql, cmd, cmdParms);  
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);
            cmd.Parameters.Clear();

            return dataTable;
        }
       
        /// <summary>
        /// 执行查询，并返回查询的结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="cmdParms">参数对象数组</param>
        /// <returns>结果数据集</returns>
        public DataSet ExecuteDataset(string Sql, params OleDbParameter[] cmdParms)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(Sql, cmd, cmdParms);           
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cmd.Parameters.Clear();

            return ds;
        }

        /// <summary>
        /// 缓存查询的OleDb参数对象.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <param name="cmdParms"></param>
        public void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
        }

        /// <summary>
        /// 从缓存获取指定的参数对象数组.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public OleDbParameter[] GetCachedParameters(string cacheKey)
        {
            OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];

            if (cachedParms == null)
            {
                return null;
            }

            OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
            for (int i = 0, j = cachedParms.Length; i < j; i++)
            {
                clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
            }

            return clonedParms;
        }

        /// <summary>
        /// 执行查询，返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="cmdParms">参数对象数组</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataset(string Sql, int PageIndex, int PageSize, params OleDbParameter[] cmdParms)
        {
            int firstPage = PageIndex * PageSize;

            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(Sql, cmd, cmdParms);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, firstPage, PageSize, "PranaTable");
            cmd.Parameters.Clear();

            return ds;
        }

        #endregion
    }
}