實戰分層架構（七）：資料存取層首種實作——Access + 動態 SQL 完整實作指南

系列脈絡與本篇目標

承接前文架構搭建，本系列規劃三種 DAL 實作，本篇為第一種：Access 資料庫 + 動態生成 SQL 指令。適合中小型專案、桌面應用或輕量級 Web 系統，部署簡單、零授權成本。

前置準備三步驟

1. 建立資料庫：Web 專案下新增 AccessData 資料夾，建立 .mdb 檔，依先前 ER 圖建立資料表與關聯。
2. 配置連線字串：Web.config <appSettings> 加入：

   <add key="AccessConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={DBPath}" />
   <add key="AccessPath" value="~/AccessData/AccessDatabase.mdb" />

   {DBPath} 為佔位符，執行期以 Server.MapPath 解析絕對路徑。
3. 新增 DAL 專案：方案加入 Class Library 專案 AccessDAL，專存 Access 存取實作。

核心助手類：AccessDALHelper

封裝三大核心方法，集中管理連線、命令、參數與快取：

public sealed class AccessDALHelper
{
    // 連線字串快取：優先從 Cache 讀取，否則讀 Web.config 並建立檔案相依性快取
    private static string GetConnectionString() { ... }
    
    // 非查詢：Insert/Update/Delete
    public static void ExecuteSQLNonQuery(string sql, OleDbParameter[] parameters)
    {
        using (var conn = new OleDbConnection(GetConnectionString()))
        using (var cmd = new OleDbCommand(sql, conn))
        {
            cmd.Parameters.AddRange(parameters);
            conn.Open();
            cmd.ExecuteNonQuery();
        } // using 確保連線關閉
    }
    
    // 查詢：回傳 DataReader (呼叫端負責關閉)
    public static OleDbDataReader ExecuteSQLDataReader(string sql, OleDbParameter[] parameters)
    {
        var conn = new OleDbConnection(GetConnectionString());
        var cmd = new OleDbCommand(sql, conn);
        cmd.Parameters.AddRange(parameters);
        conn.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection); // 關閉 Reader 時自動關閉連線
    }
}

注意：ExecuteSQLDataReader 回傳 DataReader 時註解掉 conn.Close()，改用 CommandBehavior.CloseConnection，由呼叫端 reader.Close() 觸發連線釋放。

具體 DAL 實作：AdminDAL 範例

實作 IAdminDAL 介面，三類操作模式明確：

public class AdminDAL : IAdminDAL
{
    // 修改型：Insert/Delete/Update → OperateNonData 模式
    public bool Insert(AdminInfo admin) {
        string sql = "INSERT INTO [TAdmin]([Name],[Password]) VALUES(@name,@password)";
        var ps = new[] { new OleDbParameter("name", admin.Name), new OleDbParameter("password", admin.Password) };
        try { AccessDALHelper.ExecuteSQLNonQuery(sql, ps); return true; } catch { return false; }
    }
    public bool Delete(int id) { ... }
    public bool Update(AdminInfo admin) { ... }
    
    // 單一實體查詢：手動映射 DataReader → 實體
    public AdminInfo GetByID(int id) {
        string sql = "SELECT * FROM [TAdmin] WHERE [ID]=@id";
        var ps = new[] { new OleDbParameter("id", id) };
        try {
            using (var reader = AccessDALHelper.ExecuteSQLDataReader(sql, ps)) {
                if (!reader.HasRows) return null;
                reader.Read();
                return new AdminInfo { ID = (int)reader["ID"], Name = (string)reader["Name"], Password = (string)reader["Password"] };
            }
        } catch { return null; }
    }
    public AdminInfo GetByNameAndPassword(string name, string pwd) { ... }
    public AdminInfo GetByName(string name) { ... }
    
    // 集合查詢：迴圈映射填充 List
    public IList<AdminInfo> GetAll() {
        string sql = "SELECT * FROM [TAdmin]";
        try {
            using (var reader = AccessDALHelper.ExecuteSQLDataReader(sql, null)) {
                if (!reader.HasRows) return null;
                var list = new List<AdminInfo>();
                while (reader.Read()) list.Add(new AdminInfo { ID = (int)reader["ID"], Name = (string)reader["Name"], Password = (string)reader["Password"] });
                return list;
            }
        } catch { return null; }
    }
}

程式碼結構觀察

• 三類操作模式固化：修改型→ExecuteNonQuery、單一查詢→Read()一次、集合查詢→while(Read())迴圈。
• 參數化查詢：全面使用 OleDbParameter 防止 SQL 注入。
• 資源管理：非查詢用 using 自動釋放；查詢用 CommandBehavior.CloseConnection 配合 using(reader)。
• 異常吞噬：catch { return null/false; } 簡化錯誤處理，實務建議記錄日誌或拋出自訂異常。

MessageDAL、CommentDAL 結構同理，不贅述。下篇將示範如何以 Strategy 模式消除映射邏輯重複。