C#实现的ACCESS数据库操作类完整实例
本文实例讲述了C#实现的ACCESS数据库操作类。分享给大家供大家参考,具体如下:
这个是针对ACCESS数据库操作的类,同样也是从SQLHELPER提取而来,分页程序的调用可以参考MSSQL那个类的调用,差不多的,只是提取所有记录的数量的时候有多一个参数,这个需要注意一下!
usingSystem; usingSystem.Text; usingSystem.Collections; usingSystem.Collections.Specialized; usingSystem.Data; usingSystem.Data.OleDb; usingSystem.Configuration; namespaceHoverTree.Web.DBUtility {//////数据访问抽象基础类(ACCESS) ///Copyright(C)2006-2007hovertree.net ///Allrightsreserved /// 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语句 //////执行SQL语句,返回影响的记录数 /// ///SQL语句 /// 影响的记录数 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); } } } } //////执行SQL语句,设置命令的执行等待时间 /// ////// /// publicstaticintExecuteSqlByTime(stringSQLString,intTimes) { using(OleDbConnectionconnection=newOleDbConnection(connectionString)) { using(OleDbCommandcmd=newOleDbCommand(SQLString,connection)) { try { connection.Open(); cmd.CommandTimeout=Times; introws=cmd.ExecuteNonQuery(); returnrows; } catch(System.Data.OleDb.OleDbExceptionE) { connection.Close(); thrownewException(E.Message); } } } } /// ///执行多条SQL语句,实现数据库事务。 /// ///多条SQL语句 publicstaticvoidExecuteSqlTran(ArrayListSQLStringList) { using(OleDbConnectionconn=newOleDbConnection(connectionString)) { conn.Open(); OleDbCommandcmd=newOleDbCommand(); cmd.Connection=conn; OleDbTransactiontx=conn.BeginTransaction(); cmd.Transaction=tx; try { for(intn=0;n 1) { cmd.CommandText=strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch(System.Data.OleDb.OleDbExceptionE) { tx.Rollback(); thrownewException(E.Message); } } } /// ///向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// ///SQL语句 /// 图像字节,数据库的字段类型为image的情况 /// 影响的记录数 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(); } } } //////执行一条计算查询结果语句,返回查询结果(object)。 /// ///计算查询结果语句 /// 查询结果(object) 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); } } } } //////执行查询语句,返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接) /// ///查询语句 /// SqlDataReader 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(); //} } //////执行查询语句,返回DataSet /// ///查询语句 /// DataSet 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; } } //////执行查询语句,返回DataSet,设置命令的执行等待时间 /// ////// /// publicstaticDataSetQuery(stringSQLString,intTimes) { using(OleDbConnectionconnection=newOleDbConnection(connectionString)) { DataSetds=newDataSet(); try { connection.Open(); OleDbDataAdaptercommand=newOleDbDataAdapter(SQLString,connection); command.SelectCommand.CommandTimeout=Times; command.Fill(ds,"ds"); } catch(System.Data.OleDb.OleDbExceptionex) { thrownewException(ex.Message); } returnds; } } #endregion #region执行带参数的SQL语句 /// ///执行SQL语句,返回影响的记录数 /// ///SQL语句 /// 影响的记录数 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); } } } } //////执行多条SQL语句,实现数据库事务。 /// ///SQL语句的哈希表(key为sql语句,value是该语句的OleDbParameter[]) 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(); trans.Commit(); } } catch { trans.Rollback(); throw; } } } } /// ///执行一条计算查询结果语句,返回查询结果(object)。 /// ///计算查询结果语句 /// 查询结果(object) 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); } } } } //////执行查询语句,返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接) /// ///查询语句 /// SqlDataReader 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(); //} } //////执行查询语句,返回DataSet /// ///查询语句 /// DataSet 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; cmd.CommandText=cmdText; if(trans!=null) cmd.Transaction=trans; cmd.CommandType=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获取根据指定字段排序并分页查询。 //////分页查询数据记录总数获取 /// ///----要显示的表或多个表的连接 /// ----主表的主键 /// ----查询条件,不需where /// ----是否添加查询字段的DISTINCT默认0不添加/1添加 /// 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; } /// ///智能返回SQL语句 /// ///主键(不能为空) /// 提取字段(不能为空) /// 表(理论上允许多表) /// 条件(可以空) /// 排序,格式:字段名+""+ASC(可以空) /// 分页数(不能为空) /// 当前页,起始为:1(不能为空) /// 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; } /// ///获取根据指定字段排序并分页查询。DataSet /// ///每页要显示的记录的数目 /// 要显示的页的索引 /// 要查询的数据表 /// 要查询的字段,如果是全部字段请填写:* /// 主键字段,类似排序用到 /// 是否为升序排列:0为升序,1为降序 /// 查询的筛选条件 /// 返回排序并分页查询的DataSet 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#相关内容感兴趣的读者可查看本站专题:《C#程序设计之线程使用技巧总结》、《C#操作Excel技巧总结》、《C#中XML文件操作技巧汇总》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构与算法教程》、《C#数组操作技巧总结》及《C#面向对象程序设计入门教程》
希望本文所述对大家C#程序设计有所帮助。