C#实现Access通用访问类OleDbHelper完整实例

using System;using System.Collections;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.Common;using System.Data.OleDb;namespace Common{  /// <summary>  /// OleDb 书库访问类  /// </summary>  public static class OleDbHelper  {    /// <summary>    /// Access 的数据库连接字符串格式.    /// </summary>    public const string ACCESS_CONNECTIONSTRING_TEMPLATE = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};";    // Hashtable to store cached parameters    private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());    /// <summary>    /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.    /// </summary>    /// <param name="connString"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <returns></returns>    public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)    {      OleDbCommand cmd = new OleDbCommand();      using (OleDbConnection conn = new OleDbConnection(connString))      {        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);        int val = cmd.ExecuteNonQuery();        cmd.Parameters.Clear();        return val;      }    }    /// <summary>    /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.    /// </summary>    /// <param name="conn"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <returns></returns>    public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)    {      OleDbCommand cmd = new OleDbCommand();      PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);      int val = cmd.ExecuteNonQuery();      cmd.Parameters.Clear();      return val;    }    /// <summary>    /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.    /// </summary>    /// <param name="trans"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <returns></returns>    public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)    {      OleDbCommand cmd = new OleDbCommand();      PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms, ConnectionActionType.None);      int val = cmd.ExecuteNonQuery();      cmd.Parameters.Clear();      return val;    }    /// <summary>    /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.    /// </summary>    /// <param name="connString"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <returns></returns>    public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)    {      OleDbCommand cmd = new OleDbCommand();      OleDbConnection conn = new OleDbConnection(connString);      try      {        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);        OleDbDataReader rdr = cmd.ExecuteReader();        cmd.Parameters.Clear();        return rdr;      }      catch      {        conn.Close();        throw;      }    }    /// <summary>    /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.    /// </summary>    /// <param name="conn"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <returns></returns>    public static OleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)    {      OleDbCommand cmd = new OleDbCommand();      try      {        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);        OleDbDataReader rdr = cmd.ExecuteReader();        cmd.Parameters.Clear();        return rdr;      }      catch      {        conn.Close();        throw;      }    }    /// <summary>    /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.    /// </summary>    /// <param name="connString"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <returns></returns>    public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)    {      OleDbCommand cmd = new OleDbCommand();      using (OleDbConnection conn = new OleDbConnection(connString))      {        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);        object val = cmd.ExecuteScalar();        cmd.Parameters.Clear();        return val;      }    }    /// <summary>    /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.    /// </summary>    /// <param name="conn"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <returns></returns>    public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)    {      OleDbCommand cmd = new OleDbCommand();      PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);      object val = cmd.ExecuteScalar();      cmd.Parameters.Clear();      return val;    }    /// <summary>    /// 执行查询，并返回查询所返回的结果数据集.    /// </summary>    /// <param name="connString"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <returns></returns>    public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)    {      OleDbCommand cmd = new OleDbCommand();      using (OleDbConnection conn = new OleDbConnection(connString))      {        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);        OleDbDataAdapter da = new OleDbDataAdapter(cmd);        DataSet ds = new DataSet();        da.Fill(ds);        cmd.Parameters.Clear();        return ds;      }    }    /// <summary>    /// 执行查询，并返回查询所返回的结果数据集.    /// </summary>    /// <param name="conn"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <returns></returns>    public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)    {      OleDbCommand cmd = new OleDbCommand();      PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);      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 static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)    {      parmCache[cacheKey] = cmdParms;    }    /// <summary>    /// 从缓存获取指定的参数对象数组.    /// </summary>    /// <param name="cacheKey"></param>    /// <returns></returns>    public static 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="cmd"></param>    /// <param name="conn"></param>    /// <param name="trans"></param>    /// <param name="cmdType"></param>    /// <param name="cmdText"></param>    /// <param name="cmdParms"></param>    /// <param name="connActionType"></param>    private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms, ConnectionActionType connActionType)    {      if (connActionType == ConnectionActionType.Open)      {        conn.Open();      }      else      {        if (conn.State != ConnectionState.Open)          conn.Open();      }      cmd.Connection = conn;      cmd.CommandText = cmdText;      if (trans != null)        cmd.Transaction = trans;      cmd.CommandType = cmdType;      if (cmdParms != null)      {        foreach (OleDbParameter parm in cmdParms)          cmd.Parameters.Add(parm);      }    }    /// <summary>    /// 统一分页显示数据记录    /// </summary>    /// <param name="connString">数据库连接字符串</param>    /// <param name="pageIndex">当前页码</param>    /// <param name="pageSize">每页显示的条数</param>    /// <param name="fileds">显示的字段</param>    /// <param name="table">查询的表格</param>    /// <param name="where">查询的条件</param>    /// <param name="order">排序的规则</param>    /// <param name="pageCount">out：总页数</param>    /// <param name="recordCount">out：总条数</param>    /// <param name="id">表的主键</param>    /// <returns>返回DataTable集合</returns>    public static DataTable ExecutePager(string connString, int pageIndex, int pageSize, string fileds, string table, string where, string order, out int pageCount, out int recordCount, string id)    {      if (pageIndex < 1) pageIndex = 1;      if (pageSize < 1) pageSize = 10;      if (string.IsNullOrEmpty(fileds)) fileds = "*";      if (string.IsNullOrEmpty(order)) order = "ID desc";      using (OleDbConnection conn = new OleDbConnection(connString))      {        string myVw = string.Format(" {0} ", table);        string sqlText = string.Format(" select count(0) as recordCount from {0} {1}", myVw, where);        OleDbCommand cmdCount = new OleDbCommand(sqlText, conn);        if (conn.State == ConnectionState.Closed)          conn.Open();        recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());        if ((recordCount % pageSize) > 0)          pageCount = recordCount / pageSize + 1;        else          pageCount = recordCount / pageSize;        OleDbCommand cmdRecord;        if (pageIndex == 1)//第一页        {          cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, where, order), conn);        }        else if (pageIndex > pageCount)//超出总页数        {          cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, "where 1=2", order), conn);        }        else        {          int pageLowerBound = pageSize * pageIndex;          int pageUpperBound = pageLowerBound - pageSize;          string recordIDs = RecordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, id, myVw, where, order), pageUpperBound, conn);          cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn);        }        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);        DataTable dt = new DataTable();        dataAdapter.Fill(dt);        return dt;      }    }    private static string RecordID(string query, int passCount, OleDbConnection conn)    {      OleDbCommand cmd = new OleDbCommand(query, conn);      string result = string.Empty;      using (IDataReader dr = cmd.ExecuteReader())      {        while (dr.Read())        {          if (passCount < 1)          {            result += "," + dr.GetInt32(0);          }          passCount--;        }      }      return result.Substring(1);    }    /// <summary>    /// 连接操作类型枚举.    /// </summary>    enum ConnectionActionType    {      None = 0,      AutoDetection = 1,      Open = 2    }  }}