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 { /// /// OleDb 书库访问类 /// publicstaticclassOleDbHelper { /// /// Access 的数据库连接字符串格式. /// publicconststringACCESS_CONNECTIONSTRING_TEMPLATE ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};"; // Hashtable to store cached parameters privatestaticHashtable parmCache = Hashtable.Synchronized(newHashtable()); /// /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数. /// /// /// /// /// /// publicstaticintExecuteNonQuery(stringconnString, CommandType cmdType,stringcmdText,paramsOleDbParameter[] cmdParms) { OleDbCommand cmd =newOleDbCommand(); using(OleDbConnection conn =newOleDbConnection(connString)) { PrepareCommand(cmd, conn,null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); intval = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); returnval; } } /// /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数. /// /// /// /// /// /// publicstaticintExecuteNonQuery(OleDbConnection conn, CommandType cmdType,stringcmdText,paramsOleDbParameter[] cmdParms) { OleDbCommand cmd =newOleDbCommand(); PrepareCommand(cmd, conn,null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); intval = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); returnval; } /// /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数. /// /// /// /// /// /// publicstaticintExecuteNonQuery(OleDbTransaction trans, CommandType cmdType,stringcmdText,paramsOleDbParameter[] cmdParms) { OleDbCommand cmd =newOleDbCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms, ConnectionActionType.None); intval = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); returnval; } /// /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader. /// /// /// /// /// /// publicstaticOleDbDataReader ExecuteReader(stringconnString, CommandType cmdType,stringcmdText,paramsOleDbParameter[] cmdParms) { OleDbCommand cmd =newOleDbCommand(); OleDbConnection conn =newOleDbConnection(connString); try { PrepareCommand(cmd, conn,null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); OleDbDataReader rdr = cmd.ExecuteReader(); cmd.Parameters.Clear(); returnrdr; } catch { conn.Close(); throw; } } /// /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader. /// /// /// /// /// /// publicstaticOleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType,stringcmdText,paramsOleDbParameter[] cmdParms) { OleDbCommand cmd =newOleDbCommand(); try { PrepareCommand(cmd, conn,null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); OleDbDataReader rdr = cmd.ExecuteReader(); cmd.Parameters.Clear(); returnrdr; } catch { conn.Close(); throw; } } /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行. /// /// /// /// /// /// publicstaticobjectExecuteScalar(stringconnString, CommandType cmdType,stringcmdText,paramsOleDbParameter[] cmdParms) { OleDbCommand cmd =newOleDbCommand(); using(OleDbConnection conn =newOleDbConnection(connString)) { PrepareCommand(cmd, conn,null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); objectval = cmd.ExecuteScalar(); cmd.Parameters.Clear(); returnval; } } /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行. /// /// /// /// /// /// publicstaticobjectExecuteScalar(OleDbConnection conn, CommandType cmdType,stringcmdText,paramsOleDbParameter[] cmdParms) { OleDbCommand cmd =newOleDbCommand(); PrepareCommand(cmd, conn,null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); objectval = cmd.ExecuteScalar(); cmd.Parameters.Clear(); returnval; } /// /// 执行查询，并返回查询所返回的结果数据集. /// /// /// /// /// /// publicstaticDataSet ExecuteDataset(stringconnString, CommandType cmdType,stringcmdText,paramsOleDbParameter[] cmdParms) { OleDbCommand cmd =newOleDbCommand(); using(OleDbConnection conn =newOleDbConnection(connString)) { PrepareCommand(cmd, conn,null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); OleDbDataAdapter da =newOleDbDataAdapter(cmd); DataSet ds =newDataSet(); da.Fill(ds); cmd.Parameters.Clear(); returnds; } } /// /// 执行查询，并返回查询所返回的结果数据集. /// /// /// /// /// /// publicstaticDataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType,stringcmdText,paramsOleDbParameter[] cmdParms) { OleDbCommand cmd =newOleDbCommand(); PrepareCommand(cmd, conn,null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); OleDbDataAdapter da =newOleDbDataAdapter(cmd); DataSet ds =newDataSet(); da.Fill(ds); cmd.Parameters.Clear(); returnds; } /// /// 缓存查询的 OleDb 参数对象. /// /// /// publicstaticvoidCacheParameters(stringcacheKey,paramsOleDbParameter[] cmdParms) { parmCache[cacheKey] = cmdParms; } /// /// 从缓存获取指定的参数对象数组. /// /// /// 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; } /// /// 准备命令对象. /// /// /// /// /// /// /// /// privatestaticvoidPrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType,stringcmdText, 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 parmincmdParms) cmd.Parameters.Add(parm); } } /// /// 统一分页显示数据记录 /// /// 数据库连接字符串 /// 当前页码 /// 每页显示的条数 /// 显示的字段 /// 查询的表格 /// 查询的条件 /// 排序的规则 /// out：总页数 /// out：总条数 /// 表的主键 /// publicstaticDataTable ExecutePager(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 ="ID desc"; using(OleDbConnection conn =newOleDbConnection(connString)) { stringmyVw =string.Format(" {0} ", table); stringsqlText =string.Format(" select count(0) as recordCount from {0} {1}", myVw, where); OleDbCommand cmdCount =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; OleDbCommand cmdRecord; if(pageIndex == 1)//第一页 { cmdRecord =newOleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, where, order), conn); } elseif(pageIndex > pageCount)//超出总页数 { cmdRecord =newOleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw,"where 1=2", order), conn); } else { intpageLowerBound = pageSize * pageIndex; intpageUpperBound = pageLowerBound - pageSize; stringrecordIDs = RecordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, id, myVw, where, order), pageUpperBound, conn); cmdRecord =newOleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn); } OleDbDataAdapter dataAdapter =newOleDbDataAdapter(cmdRecord); DataTable dt =newDataTable(); dataAdapter.Fill(dt); returndt; } } privatestaticstringRecordID(stringquery,intpassCount, OleDbConnection conn) { OleDbCommand cmd =newOleDbCommand(query, conn); stringresult =string.Empty; using(IDataReader dr = cmd.ExecuteReader()) { while(dr.Read()) { if(passCount < 1) { result +=","+ dr.GetInt32(0); } passCount--; } } returnresult.Substring(1); } /// /// 连接操作类型枚举. /// enumConnectionActionType { None = 0, AutoDetection = 1, Open = 2 } } } | |
