这个是针对ACCESS数据库操作的类，同样也是从SQLHELPER提取而来，分页程序的调用可以参考MSSQL那个类的调用，差不多的，只是提取所有记录的数量的时候有多一个参数，这个需要注意一下！
c# 代码
Code
usingSystem;
usingSystem.Text;
usingSystem.Collections;
usingSystem.Collections.Specialized;
usingSystem.Data;
usingSystem.Data.OleDb;
usingSystem.Configuration;
namespaceNMJU.Web.DBUtility
{///<summary>
///数据访问抽象基础类(ACCESS)
///Copyright(C)-
///Allrightsreserved
///</summary>
publicabstractclassDbHelperACE
{
//数据库连接字符串(web.config来配置)
//publicstaticstringconnectionString=ConfigurationManager.AppSettings["ConnectionString"];
//publicstaticstringconnectionString=System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["AccessConnectionString"]);
publicstaticstringconnectionString=ConfigurationManager.AppSettings["AccessConnectionString"];
publicDbHelperACE()
{
}
#region公用方法
publicstaticintGetMaxID(stringFieldName,stringTableName)
{
stringstrsql="selectmax("+FieldName+")+1from"+TableName;
objectobj=DbHelperACE.GetSingle(strsql);
if(obj==null)
{
return1;
}
else
{
returnint.Parse(obj.ToString());
}
}
publicstaticboolExists(stringstrSql)
{
objectobj=DbHelperACE.GetSingle(strSql);
intcmdresult;
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
cmdresult=0;
}
else
{
cmdresult=int.Parse(obj.ToString());
}
if(cmdresult==0)
{
returnfalse;
}
else
{
returntrue;
}
}
publicstaticboolExists(stringstrSql,paramsOleDbParameter[]cmdParms)
{
objectobj=DbHelperACE.GetSingle(strSql,cmdParms);
intcmdresult;
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
cmdresult=0;
}
else
{
cmdresult=int.Parse(obj.ToString());
}
if(cmdresult==0)
{
returnfalse;
}
else
{
returntrue;
}
}
#endregion
#region执行简单SQL语句
///<summary>
///执行SQL语句，返回影响的记录数
///</summary>
///<paramname="SQLString">SQL语句</param>
///<returns>影响的记录数</returns>
publicstaticintExecuteSql(stringSQLString)
{
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
using(OleDbCommandcmd=newOleDbCommand(SQLString,connection))
{
try
{
connection.Open();
introws=cmd.ExecuteNonQuery();
returnrows;
}
catch(System.Data.OleDb.OleDbExceptionE)
{
connection.Close();
thrownewException(E.Message);
}
}
}
}
///<summary>
///执行SQL语句，设置命令的执行等待时间
///</summary>
///<paramname="SQLString"></param>
///<paramname="Times"></param>
///<returns></returns>
publicstaticintExecuteSqlByTime(stringSQLString,intTimes)
{
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
using(OleDbCommandcmd=newOleDbCommand(SQLString,connection))
{
try
{
connection.Open();
mandTimeout=Times;
introws=cmd.ExecuteNonQuery();
returnrows;
}
catch(System.Data.OleDb.OleDbExceptionE)
{
connection.Close();
thrownewException(E.Message);
}
}
}
}
///<summary>
///执行多条SQL语句，实现数据库事务。
///</summary>
///<paramname="SQLStringList">多条SQL语句</param>
publicstaticvoidExecuteSqlTran(ArrayListSQLStringList)
{
using(OleDbConnectionconn=newOleDbConnection(connectionString))
{
conn.Open();
OleDbCommandcmd=newOleDbCommand();
cmd.Connection=conn;
OleDbTransactiontx=conn.BeginTransaction();
cmd.Transaction=tx;
try
{
for(intn=0;n<SQLStringList.Count;n++)
{
stringstrsql=SQLStringList[n].ToString();
if(strsql.Trim().Length>1)
{
mandText=strsql;
cmd.ExecuteNonQuery();
}
}
mit();
}
catch(System.Data.OleDb.OleDbExceptionE)
{
tx.Rollback();
thrownewException(E.Message);
}
}
}
///<summary>
///向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
///</summary>
///<paramname="strSQL">SQL语句</param>
///<paramname="fs">图像字节,数据库的字段类型为image的情况</param>
///<returns>影响的记录数</returns>
publicstaticintExecuteSqlInsertImg(stringstrSQL,byte[]fs)
{
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
OleDbCommandcmd=newOleDbCommand(strSQL,connection);
System.Data.OleDb.OleDbParametermyParameter=newSystem.Data.OleDb.OleDbParameter("@fs",SqlDbType.Image);
myParameter.Value=fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
introws=cmd.ExecuteNonQuery();
returnrows;
}
catch(System.Data.OleDb.OleDbExceptionE)
{
thrownewException(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///<summary>
///执行一条计算查询结果语句，返回查询结果（object）。
///</summary>
///<paramname="SQLString">计算查询结果语句</param>
///<returns>查询结果（object）</returns>
publicstaticobjectGetSingle(stringSQLString)
{
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
using(OleDbCommandcmd=newOleDbCommand(SQLString,connection))
{
try
{
connection.Open();
objectobj=cmd.ExecuteScalar();
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
returnnull;
}
else
{
returnobj;
}
}
catch(System.Data.OleDb.OleDbExceptione)
{
connection.Close();
thrownewException(e.Message);
}
}
}
}
///<summary>
///执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
///</summary>
///<paramname="strSQL">查询语句</param>
///<returns>SqlDataReader</returns>
publicstaticOleDbDataReaderExecuteReader(stringstrSQL)
{
OleDbConnectionconnection=newOleDbConnection(connectionString);
OleDbCommandcmd=newOleDbCommand(strSQL,connection);
try
{
connection.Open();
OleDbDataReadermyReader=cmd.ExecuteReader();
returnmyReader;
}
catch(System.Data.OleDb.OleDbExceptione)
{
thrownewException(e.Message);
}
//finally//不能在此关闭，否则，返回的对象将无法使用
//{
//cmd.Dispose();
//connection.Close();
//}
}
///<summary>
///执行查询语句，返回DataSet
///</summary>
///<paramname="SQLString">查询语句</param>
///<returns>DataSet</returns>
publicstaticDataSetQuery(stringSQLString)
{
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
DataSetds=newDataSet();
try
{
connection.Open();
OleDbDataAdaptercommand=newOleDbDataAdapter(SQLString,connection);
command.Fill(ds,"ds");
}
catch(System.Data.OleDb.OleDbExceptionex)
{
thrownewException(ex.Message);
}
returnds;
}
}
///<summary>
///执行查询语句，返回DataSet,设置命令的执行等待时间
///</summary>
///<paramname="SQLString"></param>
///<paramname="Times"></param>
///<returns></returns>
publicstaticDataSetQuery(stringSQLString,intTimes)
{
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
DataSetds=newDataSet();
try
{
connection.Open();
OleDbDataAdaptercommand=newOleDbDataAdapter(SQLString,connection);
mandTimeout=Times;
command.Fill(ds,"ds");
}
catch(System.Data.OleDb.OleDbExceptionex)
{
thrownewException(ex.Message);
}
returnds;
}
}
#endregion
#region执行带参数的SQL语句
///<summary>
///执行SQL语句，返回影响的记录数
///</summary>
///<paramname="SQLString">SQL语句</param>
///<returns>影响的记录数</returns>
publicstaticintExecuteSql(stringSQLString,paramsOleDbParameter[]cmdParms)
{
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
using(OleDbCommandcmd=newOleDbCommand())
{
try
{
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
introws=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
returnrows;
}
catch(System.Data.OleDb.OleDbExceptionE)
{
thrownewException(E.Message);
}
}
}
}
///<summary>
///执行多条SQL语句，实现数据库事务。
///</summary>
///<paramname="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OleDbParameter[]）</param>
publicstaticvoidExecuteSqlTran(HashtableSQLStringList)
{
using(OleDbConnectionconn=newOleDbConnection(connectionString))
{
conn.Open();
using(OleDbTransactiontrans=conn.BeginTransaction())
{
OleDbCommandcmd=newOleDbCommand();
try
{
//循环
foreach(DictionaryEntrymyDEinSQLStringList)
{
stringcmdText=myDE.Key.ToString();
OleDbParameter[]cmdParms=(OleDbParameter[])myDE.Value;
PrepareCommand(cmd,conn,trans,cmdText,cmdParms);
intval=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
mit();
}
}
catch
{
trans.Rollback();
throw;
}
}
}
}
///<summary>
///执行一条计算查询结果语句，返回查询结果（object）。
///</summary>
///<paramname="SQLString">计算查询结果语句</param>
///<returns>查询结果（object）</returns>
publicstaticobjectGetSingle(stringSQLString,paramsOleDbParameter[]cmdParms)
{
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
using(OleDbCommandcmd=newOleDbCommand())
{
try
{
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
objectobj=cmd.ExecuteScalar();
cmd.Parameters.Clear();
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
returnnull;
}
else
{
returnobj;
}
}
catch(System.Data.OleDb.OleDbExceptione)
{
thrownewException(e.Message);
}
}
}
}
///<summary>
///执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
///</summary>
///<paramname="strSQL">查询语句</param>
///<returns>SqlDataReader</returns>
publicstaticOleDbDataReaderExecuteReader(stringSQLString,paramsOleDbParameter[]cmdParms)
{
OleDbConnectionconnection=newOleDbConnection(connectionString);
OleDbCommandcmd=newOleDbCommand();
try
{
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
OleDbDataReadermyReader=cmd.ExecuteReader();
cmd.Parameters.Clear();
returnmyReader;
}
catch(System.Data.OleDb.OleDbExceptione)
{
thrownewException(e.Message);
}
//finally//不能在此关闭，否则，返回的对象将无法使用
//{
//cmd.Dispose();
//connection.Close();
//}
}
///<summary>
///执行查询语句，返回DataSet
///</summary>
///<paramname="SQLString">查询语句</param>
///<returns>DataSet</returns>
publicstaticDataSetQuery(stringSQLString,paramsOleDbParameter[]cmdParms)
{
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
{
OleDbCommandcmd=newOleDbCommand();
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
using(OleDbDataAdapterda=newOleDbDataAdapter(cmd))
{
DataSetds=newDataSet();
try
{
da.Fill(ds,"ds");
cmd.Parameters.Clear();
}
catch(System.Data.OleDb.OleDbExceptionex)
{
thrownewException(ex.Message);
}
returnds;
}
}
}
privatestaticvoidPrepareCommand(OleDbCommandcmd,OleDbConnectionconn,OleDbTransactiontrans,stringcmdText,OleDbParameter[]cmdParms)
{
if(conn.State!=ConnectionState.Open)
conn.Open();
cmd.Connection=conn;
mandText=cmdText;
if(trans!=null)
cmd.Transaction=trans;
mandType=CommandType.Text;//cmdType;
if(cmdParms!=null)
{
foreach(OleDbParameterparameterincmdParms)
{
if((parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input)&&
(parameter.Value==null))
{
parameter.Value=DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
#endregion
#region获取根据指定字段排序并分页查询。
/**/
///<summary>
///分页查询数据记录总数获取
///</summary>
///<paramname="_tbName">----要显示的表或多个表的连接</param>
///<paramname="_ID">----主表的主键</param>
///<paramname="_strCondition">----查询条件,不需where</param>
///<paramname="_Dist">----是否添加查询字段的DISTINCT默认0不添加/1添加</param>
///<returns></returns>
publicstaticstringgetPageListCounts(string_ID,string_tbName,string_strCondition,int_Dist)
{
//---存放取得查询结果总数的查询语句
//---对含有DISTINCT的查询进行SQL构造
//---对含有DISTINCT的总数查询进行SQL构造
stringstrTmp="",SqlSelect="",SqlCounts="";
if(_Dist==0)
{
SqlSelect="Select";
SqlCounts="COUNT(*)";
}
else
{
SqlSelect="SelectDISTINCT";
SqlCounts="COUNT(DISTINCT"+_ID+")";
}
if(_strCondition==string.Empty)
{
strTmp=SqlSelect+""+SqlCounts+"FROM"+_tbName;
}
else
{
strTmp=SqlSelect+""+SqlCounts+"FROM"+"Where(1=1)"+_strCondition;
}
returnstrTmp;
}
///<summary>
///智能返回SQL语句
///</summary>
///<paramname="primaryKey">主键（不能为空）</param>
///<paramname="queryFields">提取字段（不能为空）</param>
///<paramname="tableName">表（理论上允许多表）</param>
///<paramname="condition">条件（可以空）</param>
///<paramname="OrderBy">排序，格式：字段名+""+ASC（可以空）</param>
///<paramname="pageSize">分页数（不能为空）</param>
///<paramname="pageIndex">当前页，起始为：1（不能为空）</param>
///<returns></returns>
publicstaticstringgetPageListSql(stringprimaryKey,stringqueryFields,stringtableName,stringcondition,stringorderBy,intpageSize,intpageIndex)
{
stringstrTmp="";//---strTmp用于返回的SQL语句
stringSqlSelect="",SqlPrimaryKeySelect="",strOrderBy="",strWhere="where1=1",strTop="";
//0：分页数量
//1:提取字段
//2:表
//3:条件
//4:主键不存在的记录
//5:排序
SqlSelect="selecttop{0}{1}from{2}{3}{4}{5}";
//0:主键
//1:TOP数量,为分页数*(排序号-1)
//2:表
//3:条件
//4:排序
SqlPrimaryKeySelect="and{0}notin(select{1}{0}from{2}{3}{4})";
if(orderBy!="")
strOrderBy="orderby"+orderBy;
if(condition!="")
strWhere+="and"+condition;
intpageindexsize=(pageIndex-1)*pageSize;
if(pageindexsize>0)
{
strTop="top"+pageindexsize.ToString();
SqlPrimaryKeySelect=String.Format(SqlPrimaryKeySelect,primaryKey,strTop,tableName,strWhere,strOrderBy);
strTmp=String.Format(SqlSelect,pageSize.ToString(),queryFields,tableName,strWhere,SqlPrimaryKeySelect,strOrderBy);
}
else
{
strTmp=String.Format(SqlSelect,pageSize.ToString(),queryFields,tableName,strWhere,"",strOrderBy);
}
returnstrTmp;
}
///<summary>
///获取根据指定字段排序并分页查询。DataSet
///</summary>
///<paramname="pageSize">每页要显示的记录的数目</param>
///<paramname="pageIndex">要显示的页的索引</param>
///<paramname="tableName">要查询的数据表</param>
///<paramname="queryFields">要查询的字段,如果是全部字段请填写：*</param>
///<paramname="primaryKey">主键字段，类似排序用到</param>
///<paramname="orderBy">是否为升序排列：0为升序，1为降序</param>
///<paramname="condition">查询的筛选条件</param>
///<returns>返回排序并分页查询的DataSet</returns>
publicstaticDataSetGetPagingList(stringprimaryKey,stringqueryFields,stringtableName,stringcondition,stringorderBy,intpageSize,intpageIndex)
{
stringsql=getPageListSql(primaryKey,queryFields,tableName,condition,orderBy,pageSize,pageIndex);
returnQuery(sql);
}
publicstaticstringGetPagingListSQL(stringprimaryKey,stringqueryFields,stringtableName,stringcondition,stringorderBy,intpageSize,intpageIndex)
{
stringsql=getPageListSql(primaryKey,queryFields,tableName,condition,orderBy,pageSize,pageIndex);
returnsql;
}
publicstaticintGetRecordCount(string_ID,string_tbName,string_strCondition,int_Dist)
{
stringsql=getPageListCounts(_ID,_tbName,_strCondition,_Dist);
objectobj=DbHelperACE.GetSingle(sql);
if(obj==null)
{
return1;
}
else
{
returnint.Parse(obj.ToString());
}
}
#endregion
}
}
如果觉得《C# ACCESS数据库操作类》对你有帮助，请点赞、收藏，并留下你的观点哦！
