C# 操作 Access 2003 通用數據訪問類：OleDb 參數化查詢與連接池最佳實踐

背景與適用場景

雖然 Access 2003（.mdb）屬於舊技術，但在嵌入式設備、輕量級桌面應用、遷移專案中仍常見。本文提供一個生產級 AccessHelper 靜態類，封裝 OleDb 參數化查詢、事務支援、參數緩存與連接管理。

核心設計亮點

• 參數化查詢：防 SQL 注入，支援 OleDbParameter 陣列。
• 多重載入口：支援連接字串、現有連接、現有事務三種模式。
• 統一命令準備：PrepareCommand 集中處理連接狀態、事務綁定、參數添加。
• 參數緩存：Hashtable.Synchronized 線程安全緩存 OleDbParameter[]，減少重複創建開銷。
• 資源安全：using 區塊確保連接釋放，try/catch/finally 保證異常時關閉連接。

完整代碼實現

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using System.Web;

public static class AccessHelper
{
    // 連接字串：從 Web.config 讀取，自動拼接物理路徑
    public static readonly string ConnString = 
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        HttpContext.Current.Request.PhysicalApplicationPath +
        ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    // 線程安全參數緩存
    private static readonly Hashtable ParmCache = Hashtable.Synchronized(new Hashtable());

    #region ExecuteNonQuery 系列
    public static int ExecuteNonQuery(string connStr, string cmdText, params OleDbParameter[] cmdParms) {
        using (var conn = new OleDbConnection(connStr)) {
            var cmd = new OleDbCommand();
            PrepareCommand(cmd, conn, null, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
    }

    public static int ExecuteNonQuery(OleDbConnection conn, string cmdText, params OleDbParameter[] cmdParms) {
        var cmd = new OleDbCommand();
        PrepareCommand(cmd, conn, null, cmdText, cmdParms);
        int val = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        return val;
    }

    public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] cmdParms) {
        var cmd = new OleDbCommand();
        PrepareCommand(cmd, trans.Connection, trans, cmdText, cmdParms);
        int val = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        return val;
    }
    #endregion

    #region ExecuteReader 返回 DataReader
    public static OleDbDataReader ExecuteReader(string connStr, string cmdText, params OleDbParameter[] cmdParms) {
        var cmd = new OleDbCommand();
        var conn = new OleDbConnection(connStr);
        try {
            PrepareCommand(cmd, conn, null, cmdText, cmdParms);
            // CommandBehavior.CloseConnection: Reader 關閉時自動關閉連接
            var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return reader;
        }
        catch {
            conn.Close();
            throw;
        }
    }
    #endregion

    #region ExecuteDataSet 返回 DataSet
    public static DataSet ExecuteDataSet(string connStr, string cmdText, params OleDbParameter[] cmdParms) {
        using (var conn = new OleDbConnection(connStr)) {
            var cmd = new OleDbCommand();
            PrepareCommand(cmd, conn, null, cmdText, cmdParms);
            var da = new OleDbDataAdapter(cmd);
            var ds = new DataSet();
            try {
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
            catch {
                conn.Close();
                throw;
            }
        }
    }
    #endregion

    #region ExecuteScalar 返回單值
    public static object ExecuteScalar(string connStr, string cmdText, params OleDbParameter[] cmdParms) {
        using (var conn = new OleDbConnection(connStr)) {
            var cmd = new OleDbCommand();
            PrepareCommand(cmd, conn, null, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
    }

    public static object ExecuteScalar(OleDbConnection conn, string cmdText, params OleDbParameter[] cmdParms) {
        var cmd = new OleDbCommand();
        PrepareCommand(cmd, conn, null, cmdText, cmdParms);
        object val = cmd.ExecuteScalar();
        cmd.Parameters.Clear();
        return val;
    }
    #endregion

    #region 參數緩存
    public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms) {
        ParmCache[cacheKey] = cmdParms;
    }

    public static OleDbParameter[] GetCachedParameters(string cacheKey) {
        var cached = (OleDbParameter[])ParmCache[cacheKey];
        if (cached == null) return null;
        // 必須深度克隆，避免多線程共享同一參數實例導致值污染
        var cloned = new OleDbParameter[cached.Length];
        for (int i = 0; i < cached.Length; i++)
            cloned[i] = (OleDbParameter)((ICloneable)cached[i]).Clone();
        return cloned;
    }
    #endregion

    #region 核心：統一命令準備
    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; // Access 不支援存儲過程，固定為 Text
        if (cmdParms != null) {
            foreach (var p in cmdParms) cmd.Parameters.Add(p);
        }
    }
    #endregion
}

使用範例

1. 參數化查詢用戶

string sql = "SELECT * FROM Users WHERE Username = ? AND Status = ?";
var parms = new[] {
    new OleDbParameter("@User", "admin"),
    new OleDbParameter("@Status", 1)
};
using (var reader = AccessHelper.ExecuteReader(AccessHelper.ConnString, sql, parms)) {
    while (reader.Read()) {
        Console.WriteLine(reader["Email"]);
    }
}

2. 事務批量插入

using (var conn = new OleDbConnection(AccessHelper.ConnString)) {
    conn.Open();
    using (var trans = conn.BeginTransaction()) {
        try {
            for (int i = 0; i < 100; i++) {
                AccessHelper.ExecuteNonQuery(trans, 
                    "INSERT INTO Logs (Msg, CreateTime) VALUES (?, ?)",
                    new OleDbParameter("@Msg", $"Log {i}"),
                    new OleDbParameter("@Time", DateTime.Now));
            }
            trans.Commit();
        }
        catch {
            trans.Rollback();
            throw;
        }
    }
}

3. 參數緩存複用

// 應用啟動時緩存常用參數結構
AccessHelper.CacheParameters("UserLogin",
    new OleDbParameter("@User", ""),
    new OleDbParameter("@Pwd", ""));

// 業務代碼中獲取克隆副本並賦值
var parms = AccessHelper.GetCachedParameters("UserLogin");
parms[0].Value = "admin";
parms[1].Value = "123456";
var ds = AccessHelper.ExecuteDataSet(AccessHelper.ConnString, 
    "SELECT * FROM Users WHERE Username = ? AND Password = ?", parms);

關鍵注意事項

現代化替代建議

• 新專案優先選 SQLite（單檔、零配置、支援更大並發）或 SQL Server LocalDB。
• 若必須維護 Access，考慮引入 Dapper 簡化映射：conn.Query<User>(sql, parms)。
• 遷移策略：AccessHelper 介面保持不變，內部逐步替換為 SQLite 實現，業務代碼零修改。