C#實(shí)現(xiàn)Access通用訪問(wèn)類(lèi)OleDbHelper完整實(shí)例
本文實(shí)例講述了C#實(shí)現(xiàn)Access通用訪問(wèn)類(lèi)OleDbHelper。分享給大家供大家參考,具體如下:
最近在做一個(gè)項(xiàng)目數(shù)據(jù)庫(kù)用的是Access,第一次使用Access數(shù)據(jù)庫(kù),剛開(kāi)始做有些不順,數(shù)據(jù)庫(kù)的操作和SqlServer稍有些不同,而異常跟蹤得到的信息也沒(méi)有什么意義,經(jīng)過(guò)幾天的反復(fù)尋找問(wèn)題,總算解決了一些問(wèn)題,為了訪問(wèn)Access 數(shù)據(jù)庫(kù),我寫(xiě)了一個(gè)用于專(zhuān)門(mén)訪問(wèn)的類(lèi)來(lái)操作數(shù)據(jù)庫(kù),其中包括,執(zhí)行數(shù)據(jù)庫(kù)命令,返回 DataSet,返回單條記錄,返回DataReader,通用分頁(yè)方法等幾個(gè)常用的的操作方法。請(qǐng)各位提出意見(jiàn),以便我完善這個(gè)類(lèi)。雖是參考SqlHelper 但是比其簡(jiǎn)單的多,所有的代碼如下:
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 書(shū)庫(kù)訪問(wèn)類(lèi) /// </summary> public static class OleDbHelper { /// <summary> /// Access 的數(shù)據(jù)庫(kù)連接字符串格式. /// </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> /// 針對(duì) System.Data.OleDb.OleDbCommand.Connection 執(zhí)行 SQL 語(yǔ)句并返回受影響的行數(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> /// 針對(duì) System.Data.OleDb.OleDbCommand.Connection 執(zhí)行 SQL 語(yǔ)句并返回受影響的行數(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> /// 針對(duì) System.Data.OleDb.OleDbCommand.Connection 執(zhí)行 SQL 語(yǔ)句并返回受影響的行數(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 并生成一個(gè) 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 并生成一個(gè) 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í)行查詢(xún),并返回查詢(xún)所返回的結(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í)行查詢(xún),并返回查詢(xún)所返回的結(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í)行查詢(xún),并返回查詢(xún)所返回的結(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í)行查詢(xún),并返回查詢(xún)所返回的結(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> /// 緩存查詢(xún)的 OleDb 參數(shù)對(duì)象. /// </summary> /// <param name="cacheKey"></param> /// <param name="cmdParms"></param> public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms) { parmCache[cacheKey] = cmdParms; } /// <summary> /// 從緩存獲取指定的參數(shù)對(duì)象數(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)備命令對(duì)象. /// </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)一分頁(yè)顯示數(shù)據(jù)記錄 /// </summary> /// <param name="connString">數(shù)據(jù)庫(kù)連接字符串</param> /// <param name="pageIndex">當(dāng)前頁(yè)碼</param> /// <param name="pageSize">每頁(yè)顯示的條數(shù)</param> /// <param name="fileds">顯示的字段</param> /// <param name="table">查詢(xún)的表格</param> /// <param name="where">查詢(xún)的條件</param> /// <param name="order">排序的規(guī)則</param> /// <param name="pageCount">out:總頁(yè)數(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)//第一頁(yè) { 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)//超出總頁(yè)數(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> /// 連接操作類(lèi)型枚舉. /// </summary> enum ConnectionActionType { None = 0, AutoDetection = 1, Open = 2 } } }
更多關(guān)于C#相關(guān)內(nèi)容感興趣的讀者可查看本站專(zhuān)題:《C#程序設(shè)計(jì)之線程使用技巧總結(jié)》、《C#操作Excel技巧總結(jié)》、《C#中XML文件操作技巧匯總》、《C#常見(jiàn)控件用法教程》、《WinForm控件用法總結(jié)》、《C#數(shù)據(jù)結(jié)構(gòu)與算法教程》、《C#數(shù)組操作技巧總結(jié)》及《C#面向?qū)ο蟪绦蛟O(shè)計(jì)入門(mén)教程》
希望本文所述對(duì)大家C#程序設(shè)計(jì)有所幫助。
相關(guān)文章
C#操作SQLite數(shù)據(jù)庫(kù)方法小結(jié)
這篇文章介紹了C#操作SQLite數(shù)據(jù)庫(kù)的方法,文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-06-06C#讀取QQ純真IP數(shù)據(jù)庫(kù)QQWry.Dat的代碼
QQ純真IP庫(kù)算是IP地址收集較為全的一個(gè)IP庫(kù),對(duì)于IP查詢(xún)來(lái)說(shuō)這個(gè)是不錯(cuò)的選擇。下面是如何查詢(xún)純真IP庫(kù)的一個(gè)類(lèi),使用C#代碼。2007-03-03C#實(shí)現(xiàn)CSV文件讀寫(xiě)的示例詳解
這篇文章主要介紹了CsvHelper、TextFieldParser、正則表達(dá)式三種解析CSV文件的方法,順帶也會(huì)介紹一下CSV文件的寫(xiě)方法,需要的可以參考一下2023-05-05HTML文本框的值改變后觸發(fā)后臺(tái)代碼的方法
asp.net用日期插件,當(dāng)選中一個(gè)日期時(shí)觸發(fā)一個(gè)事件,以查詢(xún)當(dāng)前日期的數(shù)據(jù)。這是要跟數(shù)據(jù)庫(kù)交互的。先貼出控件代碼:2013-04-04C#中WPF依賴(lài)屬性的正確學(xué)習(xí)方法
這篇文章主要介紹了C#中WPF依賴(lài)屬性的正確學(xué)習(xí)方法 ,非常不錯(cuò),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-08-08