本文实例讲述了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 { /// <summary> /// OleDb 书库访问类 /// </summary> public static class OleDbHelper { /// <summary> /// Access 的数据库连接字符串格式. /// </summary> 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()); /// <summary> /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数. /// </summary> /// <param name="connString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> 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; } } /// <summary> /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数. /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> 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; } /// <summary> /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数. /// </summary> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> 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; } /// <summary> /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader. /// </summary> /// <param name="connString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> 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; } } /// <summary> /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader. /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> 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; } } /// <summary> /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行. /// </summary> /// <param name="connString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> 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; } } /// <summary> /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行. /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> 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; } /// <summary> /// 执行查询,并返回查询所返回的结果数据集. /// </summary> /// <param name="connString"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> 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; } } /// <summary> /// 执行查询,并返回查询所返回的结果数据集. /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> 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; } /// <summary> /// 缓存查询的 OleDb 参数对象. /// </summary> /// <param name="cacheKey"></param> /// <param name="cmdParms"></param> public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms) { parmCache[cacheKey] = cmdParms; } /// <summary> /// 从缓存获取指定的参数对象数组. /// </summary> /// <param name="cacheKey"></param> /// <returns></returns> 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; } /// <summary> /// 准备命令对象. /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <param name="connActionType"></param> 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); } } /// <summary> /// 统一分页显示数据记录 /// </summary> /// <param name="connString">数据库连接字符串</param> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">每页显示的条数</param> /// <param name="fileds">显示的字段</param> /// <param name="table">查询的表格</param> /// <param name="where">查询的条件</param> /// <param name="order">排序的规则</param> /// <param name="pageCount">out:总页数</param> /// <param name="recordCount">out:总条数</param> /// <param name="id">表的主键</param> /// <returns>返回DataTable集合</returns> 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 < 1) pageSize = 10; if (string.IsNullOrEmpty(fileds)) fileds = "*"; if (string.IsNullOrEmpty(order)) order = "ID desc"; using (OleDbConnection conn = new OleDbConnection(connString)) { string myVw = string.Format(" {0} ", table); string sqlText = string.Format(" select count(0) as recordCount from {0} {1}", myVw, where); OleDbCommand cmdCount = new OleDbCommand(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 = 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); } /// <summary> /// 连接操作类型枚举. /// </summary> enum ConnectionActionType { None = 0, AutoDetection = 1, Open = 2 } } }
更多关于C#相关内容感兴趣的读者可查看本站专题:《C#程序设计之线程使用技巧总结》、《C#操作Excel技巧总结》、《C#中XML文件操作技巧汇总》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构与算法教程》、《C#数组操作技巧总结》及《C#面向对象程序设计入门教程》
希望本文所述对大家C#程序设计有所帮助。
本文向大家介绍C#实现3D效果完整实例,包括了C#实现3D效果完整实例的使用技巧和注意事项,需要的朋友参考一下 本文实例讲述了C#实现3D效果的方法。分享给大家供大家参考,具体如下: 一、新建一类文件 注意引用命名空间: 二、页面调用 更多关于C#相关内容感兴趣的读者可查看本站专题:《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构与算法教程》、《C#面向对象程序设计入门教程
本文向大家介绍C#实现的ZPL条码打印类完整实例,包括了C#实现的ZPL条码打印类完整实例的使用技巧和注意事项,需要的朋友参考一下 本文实例讲述了C#实现的ZPL条码打印类。分享给大家供大家参考,具体如下: 更多关于C#相关内容感兴趣的读者可查看本站专题:《C#程序设计之线程使用技巧总结》、《C#操作Excel技巧总结》、《C#中XML文件操作技巧汇总》、《C#常见控件用法教程》、《WinForm
本文向大家介绍PHP实现的通过参数生成MYSQL语句类完整实例,包括了PHP实现的通过参数生成MYSQL语句类完整实例的使用技巧和注意事项,需要的朋友参考一下 本文实例讲述了PHP实现的通过参数生成MYSQL语句类。分享给大家供大家参考,具体如下: 这个类可以通过指定的表和字段参数创建SELECT ,INSERT , UPDATE 和 DELETE 语句。 这个类可以创建SQL语句的WHERE条件
本文向大家介绍js实现简单实用的AJAX完整实例,包括了js实现简单实用的AJAX完整实例的使用技巧和注意事项,需要的朋友参考一下 本文实例讲述了js实现简单实用的AJAX的方法。分享给大家供大家参考,具体如下: 更多关于ajax相关内容感兴趣的读者可查看本站专题:《JavaScript中ajax操作技巧总结》及《jquery中Ajax用法总结》 希望本文所述对大家ajax程序设计有所帮助。
本文向大家介绍java实现的2048游戏完整实例,包括了java实现的2048游戏完整实例的使用技巧和注意事项,需要的朋友参考一下 本文实例讲述了java实现的2048游戏。分享给大家供大家参考,具体如下: 先来看看运行效果: 具体代码如下: PS:这里再为大家推荐另两款本站2048游戏供大家参考(JS实现) 在线2048游戏: http://tools.jb51.net/games/game20
本文向大家介绍java实现短信通信的完整教程,包括了java实现短信通信的完整教程的使用技巧和注意事项,需要的朋友参考一下 前言 短信信息的发送目前已经是项目中必不可少的部分,我们怎么通过web页面来实现把信息推送到别人手机上呢?简单点,编码的方式简单点!看完本篇文章,以后要实现短信的发送都只需要知道发什么?发给谁?就OK了,代码如下,是不是很简单^_^ 要实现在页面或控制台输入一些信息再指定一个