欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-03 16:32:25
本文实例讲述了c#实现的access数据库操作类。分享给大家供大家参考,具体如下: 这个是针对access数据库操作的类,同样也是从sqlhelper提取而来,分页程序的...

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