【www.bbyears.com--Access】
最近在做一个项目数据库用的是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
}
}
}