C#實現(xiàn)Access通用訪問類OleDbHelper完整實例
更新時間：2017年02月23日 10:53:42 作者：藍之風(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>
/// 準備命令對象.
/// </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">當前頁碼</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)文章
winform dateTime數(shù)據(jù)類型轉(zhuǎn)換方法
這篇文章主要介紹了winform dateTime數(shù)據(jù)類型轉(zhuǎn)換方法,需要的朋友可以參考下2017-02-02
這篇文章主要為大家分享了C#線程倒計時器源碼，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-01-01
中間件是一種裝配到應(yīng)用管道以處理請求和響應(yīng)的軟件，是介于request與response處理過程之間的一個插件，本文主要介紹了如何自定義書寫中間件，需要的可以參考下2023-08-08
本文詳細講解了WinForm中的Application.Idle方法，文中通過示例代碼介紹的非常詳細。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-03-03
在這篇文章中，我們將深入討論?C#?中yield?return的機制和用法，幫助您更好地理解這個強大的功能，并在實際開發(fā)中靈活使用它，感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2023-05-05
這篇文章主要介紹了C#自定義緩存封裝類,涉及C#針對緩存的寫入、讀取及設(shè)置過期時間等常用操作,并封裝進一個類中便于調(diào)用,非常具有實用價值,需要的朋友可以參考下2015-03-03
C#實現(xiàn)幾十萬級數(shù)據(jù)導(dǎo)出Excel及Excel各種操作實例
本篇文章主要介紹了C#實現(xiàn)幾十萬級數(shù)據(jù)導(dǎo)出Excel及Excel各種操作實例，這里整理了詳細的代碼,有需要的小伙伴可以參考下。2017-02-02
C#如何Task執(zhí)行任務(wù),等待任務(wù)完成
這篇文章主要介紹了C#如何Task執(zhí)行任務(wù)，等待任務(wù)完成，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-06-06
多線程是C#中一個重要的概念,多線程指的是在同一進程中同時運行多個線程的機制,多線程適用于需要提高系統(tǒng)并發(fā)性、吞吐量和響應(yīng)速度的場景,可以充分利用多核處理器和系統(tǒng)資源,提高應(yīng)用程序的性能和效率,這篇文章主要介紹了C#實現(xiàn)多線程的幾種方式,需要的朋友可以參考下2024-05-05
C#結(jié)合數(shù)據(jù)庫實現(xiàn)驗證識別ID卡內(nèi)容的方法
這篇文章主要介紹了C#結(jié)合數(shù)據(jù)庫實現(xiàn)驗證識別ID卡內(nèi)容的方法,非常實用的功能,需要的朋友可以參考下2014-07-07