C#實現Access通用訪問類OleDbHelper的範例程式碼

這篇文章主要介紹了C#實現Access通用訪問類OleDbHelper,結合完整執行個體形式分析了C#針對access資料庫的串連、查詢、遍曆、分頁顯示等相關操作技巧,需要的朋友可以參考下

本文執行個體講述了C#實現Access通用訪問類OleDbHelper。分享給大家供大家參考，具體如下：

最近在做一個項目資料庫用的是Access,第一次使用Access資料庫，剛開始做有些不順，資料庫的操作和SqlServer稍有些不同，而異常跟蹤得到的資訊也沒有什麼意義，經過幾天的反覆尋找問題，總算解決了一些問題，為了訪問Access 資料庫，我寫了一個用於專門訪問的類來操作資料庫，其中包括，執行資料庫命令，返回 DataSet，返回單條記錄，返回DataReader，通用分頁方法等幾個常用的的操作方法。請各位提出意見，以便我完善這個類。雖是參考SqlHelper 但是比其簡單的多，所有的代碼如下：

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    }  }}