C#实现Access通用访问类OleDbHelper完整实例

时间：2022-06-25 07:51:47 编辑：袖梨来源：一聚教程网

最近在做一个项目数据库用的是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：总条数

/// 表的主键

/// 返回DataTable集合

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

}

推荐专题

最新下载

热门教程

C#实现Access通用访问类OleDbHelper完整实例

相关文章

热门栏目

php教程

asp.net教程

手机开发

css教程

网页制作

办公数码

jsp教程