Exemple de code C# pour implémenter la classe d'accès universel Access OleDbHelper

Cet article présente principalement C# pour implémenter la classe d'accès universel Access OleDbHelper, et analyse les compétences opérationnelles associées de C# pour la connexion à la base de données d'accès, les requêtes, la traversée, l'affichage de pagination et d'autres compétences opérationnelles connexes sous la forme d'un exemple complet. Amis dans le besoin Vous pouvez vous référer à ce qui suit

Cet article décrit l'implémentation de la classe d'accès universel Access OleDbHelper en C#. Je voudrais le partager avec vous pour votre référence. Les détails sont les suivants :

Je travaille actuellement sur une base de données de projet utilisant Access. C'est la première fois que j'utilise la base de données Access. Tout d'abord, le fonctionnement de la base de données est légèrement différent de celui de SqlServer. Les informations obtenues par le suivi des exceptions n'ont aucun sens. Après plusieurs jours de recherche répétée de problèmes, j'ai finalement résolu certains problèmes. Afin d'accéder à la base de données Access, j'ai écrit un. classe pour un accès spécialisé pour faire fonctionner la base de données, y compris l'exécution de commandes de base de données, renvoyer DataSet, renvoyer un seul enregistrement, renvoyer DataReader, la méthode de pagination générale et plusieurs autres méthodes de fonctionnement couramment utilisées. Merci de donner votre avis afin que je puisse améliorer ce cours. Bien qu'il s'agisse d'une référence à SqlHelper, c'est beaucoup plus simple que cela. Tous les codes sont les suivants :

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!