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#程序设计有所帮助。