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

本文实例讲述了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#程序设计有所帮助。