C# sample code to implement Access universal access class OleDbHelper

This article mainly introduces C# to implement the Access universal access class OleDbHelper, and analyzes C#'s related operation skills for access database connection, query, traversal, paging display and other related operation skills in the form of a complete example. Friends in need You can refer to the following

The example of this article describes the implementation of Access universal access class OleDbHelper in C#. I share it with you for your reference. The details are as follows:

I am currently working on a project database using Access. It is my first time to use an Access database. It was a bit awkward at first. The operation of the database is slightly different from that of SqlServer. The information obtained by exception tracking is meaningless. After several days of repeatedly searching for problems, I finally solved some problems. In order to access the Access database, I wrote a class for specialized access to operate the database, including executing database commands. , return DataSet, return a single record, return DataReader, general paging method and several other commonly used operation methods. Please give your opinions so that I can improve this class. Although it is a reference to SqlHelper, it is much simpler than it. All the codes are as follows:

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
    }
  }
}

The above is the detailed content of C# sample code to implement Access universal access class OleDbHelper. For more information, please follow other related articles on the PHP Chinese website!