C#实现Access通用访问类OleDbHelper完整实例
本文实例讲述了C#实现Access通用访问类OleDbHelper。分享给大家供大家参考,具体如下:
最近在做一个项目数据库用的是Access,第一次使用Access数据库,刚开始做有些不顺,数据库的操作和SqlServer稍有些不同,而异常跟踪得到的信息也没有什么意义,经过几天的反复寻找问题,总算解决了一些问题,为了访问Access数据库,我写了一个用于专门访问的类来操作数据库,其中包括,执行数据库命令,返回DataSet,返回单条记录,返回DataReader,通用分页方法等几个常用的的操作方法。请各位提出意见,以便我完善这个类。虽是参考SqlHelper但是比其简单的多,所有的代码如下:
usingSystem; usingSystem.Collections; usingSystem.Collections.Generic; usingSystem.Text; usingSystem.Data; usingSystem.Data.Common; usingSystem.Data.OleDb; namespaceCommon { ///<summary> ///OleDb书库访问类 ///</summary> publicstaticclassOleDbHelper { ///<summary> ///Access的数据库连接字符串格式. ///</summary> publicconststringACCESS_CONNECTIONSTRING_TEMPLATE="Provider=Microsoft.Jet.OLEDB.4.0;DataSource={0};"; //Hashtabletostorecachedparameters privatestaticHashtableparmCache=Hashtable.Synchronized(newHashtable()); ///<summary> ///针对System.Data.OleDb.OleDbCommand.Connection执行SQL语句并返回受影响的行数. ///</summary> ///<paramname="connString"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<returns></returns> publicstaticintExecuteNonQuery(stringconnString,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) { OleDbCommandcmd=newOleDbCommand(); using(OleDbConnectionconn=newOleDbConnection(connString)) { PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms,ConnectionActionType.Open); intval=cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); returnval; } } ///<summary> ///针对System.Data.OleDb.OleDbCommand.Connection执行SQL语句并返回受影响的行数. ///</summary> ///<paramname="conn"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<returns></returns> publicstaticintExecuteNonQuery(OleDbConnectionconn,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) { OleDbCommandcmd=newOleDbCommand(); PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms,ConnectionActionType.AutoDetection); intval=cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); returnval; } ///<summary> ///针对System.Data.OleDb.OleDbCommand.Connection执行SQL语句并返回受影响的行数. ///</summary> ///<paramname="trans"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<returns></returns> publicstaticintExecuteNonQuery(OleDbTransactiontrans,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) { OleDbCommandcmd=newOleDbCommand(); PrepareCommand(cmd,trans.Connection,trans,cmdType,cmdText,cmdParms,ConnectionActionType.None); intval=cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); returnval; } ///<summary> ///将System.Data.OleDb.OleDbCommand.CommandText发送到System.Data.OleDb.OleDbCommand.Connection并生成一个System.Data.OleDb.OleDbDataReader. ///</summary> ///<paramname="connString"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<returns></returns> publicstaticOleDbDataReaderExecuteReader(stringconnString,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) { OleDbCommandcmd=newOleDbCommand(); OleDbConnectionconn=newOleDbConnection(connString); try { PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms,ConnectionActionType.Open); OleDbDataReaderrdr=cmd.ExecuteReader(); cmd.Parameters.Clear(); returnrdr; } catch { conn.Close(); throw; } } ///<summary> ///将System.Data.OleDb.OleDbCommand.CommandText发送到System.Data.OleDb.OleDbCommand.Connection并生成一个System.Data.OleDb.OleDbDataReader. ///</summary> ///<paramname="conn"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<returns></returns> publicstaticOleDbDataReaderExecuteReader(OleDbConnectionconn,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) { OleDbCommandcmd=newOleDbCommand(); try { PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms,ConnectionActionType.AutoDetection); OleDbDataReaderrdr=cmd.ExecuteReader(); cmd.Parameters.Clear(); returnrdr; } catch { conn.Close(); throw; } } ///<summary> ///执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行. ///</summary> ///<paramname="connString"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<returns></returns> publicstaticobjectExecuteScalar(stringconnString,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) { OleDbCommandcmd=newOleDbCommand(); using(OleDbConnectionconn=newOleDbConnection(connString)) { PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms,ConnectionActionType.Open); objectval=cmd.ExecuteScalar(); cmd.Parameters.Clear(); returnval; } } ///<summary> ///执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行. ///</summary> ///<paramname="conn"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<returns></returns> publicstaticobjectExecuteScalar(OleDbConnectionconn,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) { OleDbCommandcmd=newOleDbCommand(); PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms,ConnectionActionType.AutoDetection); objectval=cmd.ExecuteScalar(); cmd.Parameters.Clear(); returnval; } ///<summary> ///执行查询,并返回查询所返回的结果数据集. ///</summary> ///<paramname="connString"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<returns></returns> publicstaticDataSetExecuteDataset(stringconnString,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) { OleDbCommandcmd=newOleDbCommand(); using(OleDbConnectionconn=newOleDbConnection(connString)) { PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms,ConnectionActionType.Open); OleDbDataAdapterda=newOleDbDataAdapter(cmd); DataSetds=newDataSet(); da.Fill(ds); cmd.Parameters.Clear(); returnds; } } ///<summary> ///执行查询,并返回查询所返回的结果数据集. ///</summary> ///<paramname="conn"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<returns></returns> publicstaticDataSetExecuteDataset(OleDbConnectionconn,CommandTypecmdType,stringcmdText,paramsOleDbParameter[]cmdParms) { OleDbCommandcmd=newOleDbCommand(); PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms,ConnectionActionType.AutoDetection); OleDbDataAdapterda=newOleDbDataAdapter(cmd); DataSetds=newDataSet(); da.Fill(ds); cmd.Parameters.Clear(); returnds; } ///<summary> ///缓存查询的OleDb参数对象. ///</summary> ///<paramname="cacheKey"></param> ///<paramname="cmdParms"></param> publicstaticvoidCacheParameters(stringcacheKey,paramsOleDbParameter[]cmdParms) { parmCache[cacheKey]=cmdParms; } ///<summary> ///从缓存获取指定的参数对象数组. ///</summary> ///<paramname="cacheKey"></param> ///<returns></returns> publicstaticOleDbParameter[]GetCachedParameters(stringcacheKey) { OleDbParameter[]cachedParms=(OleDbParameter[])parmCache[cacheKey]; if(cachedParms==null) returnnull; OleDbParameter[]clonedParms=newOleDbParameter[cachedParms.Length]; for(inti=0,j=cachedParms.Length;i<j;i++) clonedParms[i]=(OleDbParameter)((ICloneable)cachedParms[i]).Clone(); returnclonedParms; } ///<summary> ///准备命令对象. ///</summary> ///<paramname="cmd"></param> ///<paramname="conn"></param> ///<paramname="trans"></param> ///<paramname="cmdType"></param> ///<paramname="cmdText"></param> ///<paramname="cmdParms"></param> ///<paramname="connActionType"></param> privatestaticvoidPrepareCommand(OleDbCommandcmd,OleDbConnectionconn,OleDbTransactiontrans,CommandTypecmdType,stringcmdText,OleDbParameter[]cmdParms,ConnectionActionTypeconnActionType) { 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(OleDbParameterparmincmdParms) cmd.Parameters.Add(parm); } } ///<summary> ///统一分页显示数据记录 ///</summary> ///<paramname="connString">数据库连接字符串</param> ///<paramname="pageIndex">当前页码</param> ///<paramname="pageSize">每页显示的条数</param> ///<paramname="fileds">显示的字段</param> ///<paramname="table">查询的表格</param> ///<paramname="where">查询的条件</param> ///<paramname="order">排序的规则</param> ///<paramname="pageCount">out:总页数</param> ///<paramname="recordCount">out:总条数</param> ///<paramname="id">表的主键</param> ///<returns>返回DataTable集合</returns> publicstaticDataTableExecutePager(stringconnString,intpageIndex,intpageSize,stringfileds,stringtable,stringwhere,stringorder,outintpageCount,outintrecordCount,stringid) { if(pageIndex<1)pageIndex=1; if(pageSize<1)pageSize=10; if(string.IsNullOrEmpty(fileds))fileds="*"; if(string.IsNullOrEmpty(order))order="IDdesc"; using(OleDbConnectionconn=newOleDbConnection(connString)) { stringmyVw=string.Format("{0}",table); stringsqlText=string.Format("selectcount(0)asrecordCountfrom{0}{1}",myVw,where); OleDbCommandcmdCount=newOleDbCommand(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; OleDbCommandcmdRecord; if(pageIndex==1)//第一页 { cmdRecord=newOleDbCommand(string.Format("selecttop{0}{1}from{2}{3}orderby{4}",pageSize,fileds,myVw,where,order),conn); } elseif(pageIndex>pageCount)//超出总页数 { cmdRecord=newOleDbCommand(string.Format("selecttop{0}{1}from{2}{3}orderby{4}",pageSize,fileds,myVw,"where1=2",order),conn); } else { intpageLowerBound=pageSize*pageIndex; intpageUpperBound=pageLowerBound-pageSize; stringrecordIDs=RecordID(string.Format("selecttop{0}{1}from{2}{3}orderby{4}",pageLowerBound,id,myVw,where,order),pageUpperBound,conn); cmdRecord=newOleDbCommand(string.Format("select{0}from{1}where{4}in({2})orderby{3}",fileds,myVw,recordIDs,order,id),conn); } OleDbDataAdapterdataAdapter=newOleDbDataAdapter(cmdRecord); DataTabledt=newDataTable(); dataAdapter.Fill(dt); returndt; } } privatestaticstringRecordID(stringquery,intpassCount,OleDbConnectionconn) { OleDbCommandcmd=newOleDbCommand(query,conn); stringresult=string.Empty; using(IDataReaderdr=cmd.ExecuteReader()) { while(dr.Read()) { if(passCount<1) { result+=","+dr.GetInt32(0); } passCount--; } } returnresult.Substring(1); } ///<summary> ///连接操作类型枚举. ///</summary> enumConnectionActionType { None=0, AutoDetection=1, Open=2 } } }
更多关于C#相关内容感兴趣的读者可查看本站专题:《C#程序设计之线程使用技巧总结》、《C#操作Excel技巧总结》、《C#中XML文件操作技巧汇总》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构与算法教程》、《C#数组操作技巧总结》及《C#面向对象程序设计入门教程》
希望本文所述对大家C#程序设计有所帮助。