欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

C#實現(xiàn)Access通用訪問類OleDbHelper完整實例

 更新時間:2017年02月23日 10:53:42   作者:藍(lán)之風(fēng)  
這篇文章主要介紹了C#實現(xiàn)Access通用訪問類OleDbHelper,結(jié)合完整實例形式分析了C#針對access數(shù)據(jù)庫的連接、查詢、遍歷、分頁顯示等相關(guān)操作技巧,需要的朋友可以參考下

本文實例講述了C#實現(xiàn)Access通用訪問類OleDbHelper。分享給大家供大家參考,具體如下:

最近在做一個項目數(shù)據(jù)庫用的是Access,第一次使用Access數(shù)據(jù)庫,剛開始做有些不順,數(shù)據(jù)庫的操作和SqlServer稍有些不同,而異常跟蹤得到的信息也沒有什么意義,經(jīng)過幾天的反復(fù)尋找問題,總算解決了一些問題,為了訪問Access 數(shù)據(jù)庫,我寫了一個用于專門訪問的類來操作數(shù)據(jù)庫,其中包括,執(zhí)行數(shù)據(jù)庫命令,返回 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 的數(shù)據(jù)庫連接字符串格式.
    /// </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 執(zhí)行 SQL 語句并返回受影響的行數(shù).
    /// </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 執(zhí)行 SQL 語句并返回受影響的行數(shù).
    /// </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 執(zhí)行 SQL 語句并返回受影響的行數(shù).
    /// </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 發(fā)送到 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 發(fā)送到 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>
    /// 執(zhí)行查詢,并返回查詢所返回的結(jié)果集中第一行的第一列。忽略其他列或行.
    /// </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>
    /// 執(zhí)行查詢,并返回查詢所返回的結(jié)果集中第一行的第一列。忽略其他列或行.
    /// </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>
    /// 執(zhí)行查詢,并返回查詢所返回的結(jié)果數(shù)據(jù)集.
    /// </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>
    /// 執(zhí)行查詢,并返回查詢所返回的結(jié)果數(shù)據(jù)集.
    /// </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 參數(shù)對象.
    /// </summary>
    /// <param name="cacheKey"></param>
    /// <param name="cmdParms"></param>
    public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
    {
      parmCache[cacheKey] = cmdParms;
    }
    /// <summary>
    /// 從緩存獲取指定的參數(shù)對象數(shù)組.
    /// </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>
    /// 準(zhǔn)備命令對象.
    /// </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>
    /// 統(tǒng)一分頁顯示數(shù)據(jù)記錄
    /// </summary>
    /// <param name="connString">數(shù)據(jù)庫連接字符串</param>
    /// <param name="pageIndex">當(dāng)前頁碼</param>
    /// <param name="pageSize">每頁顯示的條數(shù)</param>
    /// <param name="fileds">顯示的字段</param>
    /// <param name="table">查詢的表格</param>
    /// <param name="where">查詢的條件</param>
    /// <param name="order">排序的規(guī)則</param>
    /// <param name="pageCount">out:總頁數(shù)</param>
    /// <param name="recordCount">out:總條數(shù)</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)//超出總頁數(shù)
        {
          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
    }
  }
}

更多關(guān)于C#相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《C#程序設(shè)計之線程使用技巧總結(jié)》、《C#操作Excel技巧總結(jié)》、《C#中XML文件操作技巧匯總》、《C#常見控件用法教程》、《WinForm控件用法總結(jié)》、《C#數(shù)據(jù)結(jié)構(gòu)與算法教程》、《C#數(shù)組操作技巧總結(jié)》及《C#面向?qū)ο蟪绦蛟O(shè)計入門教程

希望本文所述對大家C#程序設(shè)計有所幫助。

相關(guān)文章

  • C#調(diào)用第三方工具完成FTP操作

    C#調(diào)用第三方工具完成FTP操作

    這篇文章介紹了C#調(diào)用第三方工具完成FTP操作的方法,文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2022-05-05
  • C#操作SQLite數(shù)據(jù)庫方法小結(jié)

    C#操作SQLite數(shù)據(jù)庫方法小結(jié)

    這篇文章介紹了C#操作SQLite數(shù)據(jù)庫的方法,文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2022-06-06
  • c# 委托和事件實例學(xué)習(xí)

    c# 委托和事件實例學(xué)習(xí)

    今天把委托和事件研究了一個,winForm環(huán)境下,一般的小例子都是字符界面,我為了運用一下,寫了winForm
    2009-01-01
  • C#讀取QQ純真IP數(shù)據(jù)庫QQWry.Dat的代碼

    C#讀取QQ純真IP數(shù)據(jù)庫QQWry.Dat的代碼

    QQ純真IP庫算是IP地址收集較為全的一個IP庫,對于IP查詢來說這個是不錯的選擇。下面是如何查詢純真IP庫的一個類,使用C#代碼。
    2007-03-03
  • C#實現(xiàn)CSV文件讀寫的示例詳解

    C#實現(xiàn)CSV文件讀寫的示例詳解

    這篇文章主要介紹了CsvHelper、TextFieldParser、正則表達(dá)式三種解析CSV文件的方法,順帶也會介紹一下CSV文件的寫方法,需要的可以參考一下
    2023-05-05
  • HTML文本框的值改變后觸發(fā)后臺代碼的方法

    HTML文本框的值改變后觸發(fā)后臺代碼的方法

    asp.net用日期插件,當(dāng)選中一個日期時觸發(fā)一個事件,以查詢當(dāng)前日期的數(shù)據(jù)。這是要跟數(shù)據(jù)庫交互的。先貼出控件代碼:
    2013-04-04
  • C#中Lambda表達(dá)式的三種寫法

    C#中Lambda表達(dá)式的三種寫法

    這篇文章介紹了C#中Lambda表達(dá)式的三種寫法,文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2022-05-05
  • c#多線程程序設(shè)計實例方法

    c#多線程程序設(shè)計實例方法

    在本篇文章里小編給大家分享了關(guān)于c#多線程程序設(shè)計實例步驟,需要的朋友們學(xué)習(xí)參考下。
    2019-02-02
  • C#中WPF依賴屬性的正確學(xué)習(xí)方法

    C#中WPF依賴屬性的正確學(xué)習(xí)方法

    這篇文章主要介紹了C#中WPF依賴屬性的正確學(xué)習(xí)方法 ,非常不錯,具有一定的參考借鑒價值,需要的朋友可以參考下
    2019-08-08
  • 利用C#實現(xiàn)修改圖片透明度功能

    利用C#實現(xiàn)修改圖片透明度功能

    這篇文章主要為大家詳細(xì)介紹了如何利用C#實現(xiàn)修改圖片透明度功能,文中的示例代碼講解詳細(xì),對我們學(xué)習(xí)C#有一定的幫助,感興趣的小伙伴可以了解一下
    2022-12-12

最新評論