这篇文章主要介绍了c#实现access通用访问类oledbhelper,结合完整实例形式分析了c#针对access数据库的连接、查询、遍历、分页显示等相关操作技巧,需要的朋友可以参考下
本文实例讲述了C#实现Access通用访问类OleDbHelper。分享给大家供大家参考，具体如下：
最近在做一个项目数据库用的是Access,第一次使用Access数据库，刚开始做有些不顺，数据库的操作和SqlServer稍有些不同，而异常跟踪得到的信息也没有什么意义，经过几天的反复寻找问题，总算解决了一些问题，为了访问Access 数据库，我写了一个用于专门访问的类来操作数据库，其中包括，执行数据库命令，返回 DataSet，返回单条记录，返回DataReader，通用分页方法等几个常用的的操作方法。请各位提出意见，以便我完善这个类。虽是参考SqlHelper 但是比其简单的多，所有的代码如下：
using System;using System.Collections;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.Common;using System.Data.OleDb;namespace Common{ /// /// OleDb 书库访问类 /// public static class OleDbHelper { /// /// Access 的数据库连接字符串格式. /// public const string ACCESS_CONNECTIONSTRING_TEMPLATE = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};"; // Hashtable to store cached parameters private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数. /// /// /// /// /// /// public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数. /// /// /// /// /// /// public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数. /// /// /// /// /// /// public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms, ConnectionActionType.None); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader. /// /// /// /// /// /// public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); OleDbConnection conn = new OleDbConnection(connString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); OleDbDataReader rdr = cmd.ExecuteReader(); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader. /// /// /// /// /// /// public static OleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); OleDbDataReader rdr = cmd.ExecuteReader(); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行. /// /// /// /// /// /// public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行. /// /// /// /// /// /// public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// /// 执行查询，并返回查询所返回的结果数据集. /// /// /// /// /// /// public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cmd.Parameters.Clear(); return ds; } } /// /// 执行查询，并返回查询所返回的结果数据集. /// /// /// /// /// /// public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cmd.Parameters.Clear(); return ds; } /// /// 缓存查询的 OleDb 参数对象. /// /// /// public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms) { parmCache[cacheKey] = cmdParms; } /// /// 从缓存获取指定的参数对象数组. /// /// /// public static OleDbParameter[] GetCachedParameters(string cacheKey) { OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey]; if (cachedParms == null) return null; OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } /// /// 准备命令对象. /// /// /// /// /// /// /// /// private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, 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 parm in cmdParms) cmd.Parameters.Add(parm); } } /// /// 统一分页显示数据记录 /// /// 数据库连接字符串 /// 当前页码 /// 每页显示的条数 /// 显示的字段 /// 查询的表格 /// 查询的条件 /// 排序的规则 /// out：总页数 /// out：总条数 /// 表的主键 /// 返回DataTable集合 public static DataTable ExecutePager(string connString, int pageIndex, int pageSize, string fileds, string table, string where, string order, out int pageCount, out int recordCount, string id) { if (pageIndex < 1) pageIndex = 1; if (pageSize 0) pageCount = recordCount / pageSize + 1; else pageCount = recordCount / pageSize; OleDbCommand cmdRecord; if (pageIndex == 1)//第一页 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, where, order), conn); } else if (pageIndex > pageCount)//超出总页数 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, "where 1=2", order), conn); } else { int pageLowerBound = pageSize * pageIndex; int pageUpperBound = pageLowerBound - pageSize; string recordIDs = RecordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, id, myVw, where, order), pageUpperBound, conn); cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn); } OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord); DataTable dt = new DataTable(); dataAdapter.Fill(dt); return dt; } } private static string RecordID(string query, int passCount, OleDbConnection conn) { OleDbCommand cmd = new OleDbCommand(query, conn); string result = string.Empty; using (IDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (passCount < 1) { result += "," + dr.GetInt32(0); } passCount--; } } return result.Substring(1); } /// /// 连接操作类型枚举. /// enum ConnectionActionType { None = 0, AutoDetection = 1, Open = 2 } }}
以上就是C#实现Access通用访问类OleDbHelper的示例代码的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1432353.html
微信扫一扫 
支付宝扫一扫 
