C#实现的ACCESS数据库操作类完整实例

本文实例讲述了c#实现的access数据库操作类。分享给大家供大家参考，具体如下：

这个是针对access数据库操作的类，同样也是从sqlhelper提取而来，分页程序的调用可以参考mssql那个类的调用，差不多的，只是提取所有记录的数量的时候有多一个参数，这个需要注意一下！

using system;
using system.text;
using system.collections;
using system.collections.specialized;
using system.data;
using system.data.oledb;
using system.configuration;
namespace hovertree.web.dbutility
{  /// <summary>
  /// 数据访问抽象基础类(access)
  /// copyright (c) 2006-2007 hovertree.net
  /// all rights reserved
  /// </summary>
  public abstract class dbhelperace
  {
    //数据库连接字符串(web.config来配置)
    //public static string connectionstring = configurationmanager.appsettings["connectionstring"];
    // public static string connectionstring = system.web.httpcontext.current.server.mappath(configurationmanager.appsettings["accessconnectionstring"]);
    public static string connectionstring = configurationmanager.appsettings["accessconnectionstring"];
    public dbhelperace()
    {
    }
    #region 公用方法
    public static int getmaxid(string fieldname, string tablename)
    {
      string strsql = "select max(" + fieldname + ")+1 from " + tablename;
      object obj = dbhelperace.getsingle(strsql);
      if (obj == null)
      {
        return 1;
      }
      else
      {
        return int.parse(obj.tostring());
      }
    }
    public static bool exists(string strsql)
    {
      object obj = dbhelperace.getsingle(strsql);
      int cmdresult;
      if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
      {
        cmdresult = 0;
      }
      else
      {
        cmdresult = int.parse(obj.tostring());
      }
      if (cmdresult == 0)
      {
        return false;
      }
      else
      {
        return true;
      }
    }
    public static bool exists(string strsql, params oledbparameter[] cmdparms)
    {
      object obj = dbhelperace.getsingle(strsql, cmdparms);
      int cmdresult;
      if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
      {
        cmdresult = 0;
      }
      else
      {
        cmdresult = int.parse(obj.tostring());
      }
      if (cmdresult == 0)
      {
        return false;
      }
      else
      {
        return true;
      }
    }
    #endregion
    #region 执行简单sql语句
    /// <summary>
    /// 执行sql语句，返回影响的记录数
    /// </summary>
    /// <param name="sqlstring">sql语句</param>
    /// <returns>影响的记录数</returns>
    public static int executesql(string sqlstring)
    {
      using (oledbconnection connection = new oledbconnection(connectionstring))
      {
        using (oledbcommand cmd = new oledbcommand(sqlstring, connection))
        {
          try
          {
            connection.open();
            int rows = cmd.executenonquery();
            return rows;
          }
          catch (system.data.oledb.oledbexception e)
          {
            connection.close();
            throw new exception(e.message);
          }
        }
      }
    }
    /// <summary>
    /// 执行sql语句，设置命令的执行等待时间
    /// </summary>
    /// <param name="sqlstring"></param>
    /// <param name="times"></param>
    /// <returns></returns>
    public static int executesqlbytime(string sqlstring, int times)
    {
      using (oledbconnection connection = new oledbconnection(connectionstring))
      {
        using (oledbcommand cmd = new oledbcommand(sqlstring, connection))
        {
          try
          {
            connection.open();
            cmd.commandtimeout = times;
            int rows = cmd.executenonquery();
            return rows;
          }
          catch (system.data.oledb.oledbexception e)
          {
            connection.close();
            throw new exception(e.message);
          }
        }
      }
    }
    /// <summary>
    /// 执行多条sql语句，实现数据库事务。
    /// </summary>
    /// <param name="sqlstringlist">多条sql语句</param>
    public static void executesqltran(arraylist sqlstringlist)
    {
      using (oledbconnection conn = new oledbconnection(connectionstring))
      {
        conn.open();
        oledbcommand cmd = new oledbcommand();
        cmd.connection = conn;
        oledbtransaction tx = conn.begintransaction();
        cmd.transaction = tx;
        try
        {
          for (int n = 0; n < sqlstringlist.count; n++)
          {
            string strsql = sqlstringlist[n].tostring();
            if (strsql.trim().length > 1)
            {
              cmd.commandtext = strsql;
              cmd.executenonquery();
            }
          }
          tx.commit();
        }
        catch (system.data.oledb.oledbexception e)
        {
          tx.rollback();
          throw new exception(e.message);
        }
      }
    }
    /// <summary>
    /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
    /// </summary>
    /// <param name="strsql">sql语句</param>
    /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
    /// <returns>影响的记录数</returns>
    public static int executesqlinsertimg(string strsql, byte[] fs)
    {
      using (oledbconnection connection = new oledbconnection(connectionstring))
      {
        oledbcommand cmd = new oledbcommand(strsql, connection);
        system.data.oledb.oledbparameter myparameter = new system.data.oledb.oledbparameter("@fs", sqldbtype.image);
        myparameter.value = fs;
        cmd.parameters.add(myparameter);
        try
        {
          connection.open();
          int rows = cmd.executenonquery();
          return rows;
        }
        catch (system.data.oledb.oledbexception e)
        {
          throw new exception(e.message);
        }
        finally
        {
          cmd.dispose();
          connection.close();
        }
      }
    }
    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="sqlstring">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object getsingle(string sqlstring)
    {
      using (oledbconnection connection = new oledbconnection(connectionstring))
      {
        using (oledbcommand cmd = new oledbcommand(sqlstring, connection))
        {
          try
          {
            connection.open();
            object obj = cmd.executescalar();
            if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
            {
              return null;
            }
            else
            {
              return obj;
            }
          }
          catch (system.data.oledb.oledbexception e)
          {
            connection.close();
            throw new exception(e.message);
          }
        }
      }
    }
    /// <summary>
    /// 执行查询语句，返回sqldatareader(使用该方法切记要手工关闭sqldatareader和连接)
    /// </summary>
    /// <param name="strsql">查询语句</param>
    /// <returns>sqldatareader</returns>
    public static oledbdatareader executereader(string strsql)
    {
      oledbconnection connection = new oledbconnection(connectionstring);
      oledbcommand cmd = new oledbcommand(strsql, connection);
      try
      {
        connection.open();
        oledbdatareader myreader = cmd.executereader();
        return myreader;
      }
      catch (system.data.oledb.oledbexception e)
      {
        throw new exception(e.message);
      }
      //finally //不能在此关闭，否则，返回的对象将无法使用
      //{
      // cmd.dispose();
      // connection.close();
      //}
    }
    /// <summary>
    /// 执行查询语句，返回dataset
    /// </summary>
    /// <param name="sqlstring">查询语句</param>
    /// <returns>dataset</returns>
    public static dataset query(string sqlstring)
    {
      using (oledbconnection connection = new oledbconnection(connectionstring))
      {
        dataset ds = new dataset();
        try
        {
          connection.open();
          oledbdataadapter command = new oledbdataadapter(sqlstring, connection);
          command.fill(ds, "ds");
        }
        catch (system.data.oledb.oledbexception ex)
        {
          throw new exception(ex.message);
        }
        return ds;
      }
    }
    /// <summary>
    /// 执行查询语句，返回dataset,设置命令的执行等待时间
    /// </summary>
    /// <param name="sqlstring"></param>
    /// <param name="times"></param>
    /// <returns></returns>
    public static dataset query(string sqlstring, int times)
    {
      using (oledbconnection connection = new oledbconnection(connectionstring))
      {
        dataset ds = new dataset();
        try
        {
          connection.open();
          oledbdataadapter command = new oledbdataadapter(sqlstring, connection);
          command.selectcommand.commandtimeout = times;
          command.fill(ds, "ds");
        }
        catch (system.data.oledb.oledbexception ex)
        {
          throw new exception(ex.message);
        }
        return ds;
      }
    }
    #endregion
    #region 执行带参数的sql语句
    /// <summary>
    /// 执行sql语句，返回影响的记录数
    /// </summary>
    /// <param name="sqlstring">sql语句</param>
    /// <returns>影响的记录数</returns>
    public static int executesql(string sqlstring, params oledbparameter[] cmdparms)
    {
      using (oledbconnection connection = new oledbconnection(connectionstring))
      {
        using (oledbcommand cmd = new oledbcommand())
        {
          try
          {
            preparecommand(cmd, connection, null, sqlstring, cmdparms);
            int rows = cmd.executenonquery();
            cmd.parameters.clear();
            return rows;
          }
          catch (system.data.oledb.oledbexception e)
          {
            throw new exception(e.message);
          }
        }
      }
    }
    /// <summary>
    /// 执行多条sql语句，实现数据库事务。
    /// </summary>
    /// <param name="sqlstringlist">sql语句的哈希表（key为sql语句，value是该语句的oledbparameter[]）</param>
    public static void executesqltran(hashtable sqlstringlist)
    {
      using (oledbconnection conn = new oledbconnection(connectionstring))
      {
        conn.open();
        using (oledbtransaction trans = conn.begintransaction())
        {
          oledbcommand cmd = new oledbcommand();
          try
          {
            //循环
            foreach (dictionaryentry myde in sqlstringlist)
            {
              string cmdtext = myde.key.tostring();
              oledbparameter[] cmdparms = (oledbparameter[])myde.value;
              preparecommand(cmd, conn, trans, cmdtext, cmdparms);
              int val = cmd.executenonquery();
              cmd.parameters.clear();
              trans.commit();
            }
          }
          catch
          {
            trans.rollback();
            throw;
          }
        }
      }
    }
    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="sqlstring">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object getsingle(string sqlstring, params oledbparameter[] cmdparms)
    {
      using (oledbconnection connection = new oledbconnection(connectionstring))
      {
        using (oledbcommand cmd = new oledbcommand())
        {
          try
          {
            preparecommand(cmd, connection, null, sqlstring, cmdparms);
            object obj = cmd.executescalar();
            cmd.parameters.clear();
            if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
            {
              return null;
            }
            else
            {
              return obj;
            }
          }
          catch (system.data.oledb.oledbexception e)
          {
            throw new exception(e.message);
          }
        }
      }
    }  
    /// <summary>
    /// 执行查询语句，返回sqldatareader (使用该方法切记要手工关闭sqldatareader和连接)
    /// </summary>
    /// <param name="strsql">查询语句</param>
    /// <returns>sqldatareader</returns>
    public static oledbdatareader executereader(string sqlstring, params oledbparameter[] cmdparms)
    {
      oledbconnection connection = new oledbconnection(connectionstring);
      oledbcommand cmd = new oledbcommand();
      try
      {
        preparecommand(cmd, connection, null, sqlstring, cmdparms);
        oledbdatareader myreader = cmd.executereader();
        cmd.parameters.clear();
        return myreader;
      }
      catch (system.data.oledb.oledbexception e)
      {
        throw new exception(e.message);
      }
      //finally //不能在此关闭，否则，返回的对象将无法使用
      //{
      // cmd.dispose();
      // connection.close();
      //}
    }
    /// <summary>
    /// 执行查询语句，返回dataset
    /// </summary>
    /// <param name="sqlstring">查询语句</param>
    /// <returns>dataset</returns>
    public static dataset query(string sqlstring, params oledbparameter[] cmdparms)
    {
      using (oledbconnection connection = new oledbconnection(connectionstring))
      {
        oledbcommand cmd = new oledbcommand();
        preparecommand(cmd, connection, null, sqlstring, cmdparms);
        using (oledbdataadapter da = new oledbdataadapter(cmd))
        {
          dataset ds = new dataset();
          try
          {
            da.fill(ds, "ds");
            cmd.parameters.clear();
          }
          catch (system.data.oledb.oledbexception ex)
          {
            throw new exception(ex.message);
          }
          return ds;
        }
      }
    }
    private static void preparecommand(oledbcommand cmd, oledbconnection conn, oledbtransaction trans, string cmdtext, oledbparameter[] cmdparms)
    {
      if (conn.state != connectionstate.open)
        conn.open();
      cmd.connection = conn;
      cmd.commandtext = cmdtext;
      if (trans != null)
        cmd.transaction = trans;
      cmd.commandtype = commandtype.text;//cmdtype;
      if (cmdparms != null)
      {
        foreach (oledbparameter parameter in cmdparms)
        {
          if ((parameter.direction == parameterdirection.inputoutput || parameter.direction == parameterdirection.input) &&
            (parameter.value == null))
          {
            parameter.value = dbnull.value;
          }
          cmd.parameters.add(parameter);
        }
      }
    }
    #endregion
    #region 获取根据指定字段排序并分页查询。
    
    /// <summary>
    /// 分页查询数据记录总数获取
    /// </summary>
    /// <param name="_tbname">----要显示的表或多个表的连接</param>
    /// <param name="_id">----主表的主键</param>
    /// <param name="_strcondition">----查询条件,不需where</param>
    /// <param name="_dist">----是否添加查询字段的 distinct 默认0不添加/1添加</param>
    /// <returns></returns>
    public static string getpagelistcounts(string _id, string _tbname, string _strcondition, int _dist)
    {
      //---存放取得查询结果总数的查询语句
      //---对含有distinct的查询进行sql构造
      //---对含有distinct的总数查询进行sql构造
      string strtmp = "", sqlselect = "", sqlcounts = "";
      if (_dist == 0)
      {
        sqlselect = "select ";
        sqlcounts = "count(*)";
      }
      else
      {
        sqlselect = "select distinct ";
        sqlcounts = "count(distinct " + _id + ")";
      }
      if (_strcondition == string.empty)
      {
        strtmp = sqlselect + " "+ sqlcounts + " from " + _tbname;
      }
      else
      {
        strtmp = sqlselect + " "+ sqlcounts + " from " + " where (1=1) " + _strcondition;
      }
      return strtmp;
    }
    /// <summary>
    /// 智能返回sql语句
    /// </summary>
    /// <param name="primarykey">主键（不能为空）</param>
    /// <param name="queryfields">提取字段（不能为空）</param>
    /// <param name="tablename">表（理论上允许多表）</param>
    /// <param name="condition">条件（可以空）</param>
    /// <param name="orderby">排序，格式：字段名+""+asc（可以空）</param>
    /// <param name="pagesize">分页数（不能为空）</param>
    /// <param name="pageindex">当前页，起始为：1（不能为空）</param>
    /// <returns></returns>
    public static string getpagelistsql(string primarykey, string queryfields, string tablename, string condition, string orderby, int pagesize, int pageindex)
    {
      string strtmp = ""; //---strtmp用于返回的sql语句
      string sqlselect = "", sqlprimarykeyselect = "", strorderby = "", strwhere = " where 1=1 ", strtop = "";
      //0：分页数量
      //1:提取字段
      //2:表
      //3:条件
      //4:主键不存在的记录
      //5:排序
      sqlselect = " select top {0} {1} from {2} {3} {4} {5}";
      //0:主键
      //1:top数量,为分页数*(排序号-1)
      //2:表
      //3:条件
      //4:排序
      sqlprimarykeyselect = " and {0} not in (select {1} {0} from {2} {3} {4}) ";
      if (orderby != "")
        strorderby = " order by " + orderby;
      if (condition != "")
        strwhere += " and " + condition;
      int pageindexsize = (pageindex - 1) * pagesize;
      if (pageindexsize > 0)
      {
        strtop = " top " + pageindexsize.tostring();
        sqlprimarykeyselect = string.format(sqlprimarykeyselect, primarykey, strtop, tablename, strwhere, strorderby);
        strtmp = string.format(sqlselect, pagesize.tostring(), queryfields, tablename, strwhere, sqlprimarykeyselect, strorderby);
      }
      else
      {
        strtmp = string.format(sqlselect, pagesize.tostring(), queryfields, tablename, strwhere, "", strorderby);
      }
      return strtmp;
    }
    /// <summary>
    /// 获取根据指定字段排序并分页查询。dataset
    /// </summary>
    /// <param name="pagesize">每页要显示的记录的数目</param>
    /// <param name="pageindex">要显示的页的索引</param>
    /// <param name="tablename">要查询的数据表</param>
    /// <param name="queryfields">要查询的字段,如果是全部字段请填写：*</param>
    /// <param name="primarykey">主键字段，类似排序用到</param>
    /// <param name="orderby">是否为升序排列：0为升序，1为降序</param>
    /// <param name="condition">查询的筛选条件</param>
    /// <returns>返回排序并分页查询的dataset</returns>
    public static dataset getpaginglist(string primarykey, string queryfields, string tablename, string condition, string orderby, int pagesize, int pageindex)
    {
      string sql = getpagelistsql(primarykey, queryfields, tablename, condition, orderby, pagesize, pageindex);
      return query(sql);
    }
    public static string getpaginglistsql(string primarykey, string queryfields, string tablename, string condition, string orderby, int pagesize, int pageindex)
    {
      string sql = getpagelistsql(primarykey, queryfields, tablename, condition, orderby, pagesize, pageindex);
      return sql;
    }
    public static int getrecordcount(string _id, string _tbname, string _strcondition, int _dist)
    {
      string sql = getpagelistcounts( _id, _tbname, _strcondition, _dist);
      object obj = dbhelperace.getsingle(sql);
      if (obj == null)
      {
        return 1;
      }
      else
      {
        return int.parse(obj.tostring());
      }
    }
    #endregion
  }
}

更多关于c#相关内容感兴趣的读者可查看本站专题：《c#程序设计之线程使用技巧总结》、《c#操作excel技巧总结》、《c#中xml文件操作技巧汇总》、《c#常见控件用法教程》、《winform控件用法总结》、《c#数据结构与算法教程》、《c#数组操作技巧总结》及《c#面向对象程序设计入门教程》

希望本文所述对大家c#程序设计有所帮助。