C# SQL Server 資料庫存取基底類別完整實作：支援 SQL 字串、參數化查詢與預存程序三模式

企業級 .NET 應用常需封裝統一資料存取層 (DAL)。本文提供一個功能完整的 SQLDataBase 基底類別，支援三種呼叫模式：單純 SQL 字串、參數化 SQL、預存程序，每種模式均包含 執行非查詢、執行純量查詢、執行 DataReader、執行 DataSet、交易處理 五大核心方法，且完整實作 IDisposable 模式與例外處理，可直接引用專案使用。

一、 類別架構總覽

namespace SQLDataBase
{
    public class SQLDataBase
    {
        // 連線字串從設定檔讀取
        protected static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        #region 模式一：單純 SQL 字串 (5 方法 + 交易)
        public int ExecuteSql(string SQLString) { ... }
        public void ExecuteSqlTran(ArrayList SQLStringList) { ... }
        public object GetSingle(string SQLString) { ... }
        public DbDataReader ExecuteReader(string strSQL) { ... }
        public DataSet GetDataSet(string SQLString) { ... }
        #endregion

        #region 模式二：參數化 SQL (5 方法 + 交易)
        public int ExecuteSql(string SQLString, DbParameter[] cmdParms) { ... }
        public void ExecuteSqlTran(Hashtable SQLStringList) { ... } // Key=SQL, Value=SqlParameter[]
        public object GetSingle(string SQLString, DbParameter[] cmdParms) { ... }
        public DbDataReader ExecuteReader(string SQLString, DbParameter[] cmdParms) { ... }
        public DataSet GetDataSet(string SQLString, DbParameter[] cmdParms) { ... }
        #endregion

        #region 模式三：預存程序 (5 方法 + 交易)
        public int RunProcedureExecuteSql(string storeProcName, DbParameter[] parameters) { ... }
        public object RunProcedureGetSingle(string storeProcName, DbParameter[] parameters) { ... }
        public DbDataReader RunProcedureGetDataReader(string storedProcName, DbParameter[] parameters) { ... }
        public DataSet RunProcedureGetDataSet(string storedProcName, DbParameter[] parameters) { ... }
        public bool RunProcedureTran(Hashtable SQLStringList) { ... } // Key=SqlParameter[], Value=SP Name
        #endregion

        // 內部輔助方法
        private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, DbParameter[] cmdParms) { ... }
        private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, DbParameter[] parameters) { ... }
    }
}

二、 核心設計亮點

1. 統一連線管理與資源釋放

• 全域採用 using (SqlConnection ...) 確保連線正確關閉釋放。
• DataReader 使用 CommandBehavior.CloseConnection 自動關閉連線。
• 參數集合執行後即時 cmd.Parameters.Clear() 避免參數殘留。

2. 交易處理完整支援

• 單純 SQL：ArrayList 批次執行，失敗 Rollback。
• 參數化 SQL：Hashtable (Key=SQL, Value=SqlParameter[])，逐條準備命令執行。
• 預存程序：Hashtable (Key=SqlParameter[], Value=SP Name)，支援多 SP 單一交易。

3. 安全性：拒絕 SQL Injection

• 參數化模式與預存程序模式強制使用 SqlParameter，從根源杜絕注入攻擊。
• 單純 SQL 模式僅建議用於靜態、受信任查詢 (如 DDL、維護腳本)。

4. 相容性與擴充性

• 回傳介面型別 DbDataReader、DbParameter[]，易於未來遷移至其他 ADO.NET Provider (Oracle, PostgreSQL 等)。
• 連線字串外部化至 web.config/app.config，支援多環境部署。

三、 使用範例

App.config 設定

<connectionStrings>
  <add name="ConnectionString" connectionString="Server=.;Database=MyDB;Uid=sa;Pwd=123;" providerName="System.Data.SqlClient" />
</connectionStrings>

呼叫程式碼

var db = new SQLDataBase.SQLDataBase();

// 1. 參數化查詢 (推薦)
string sql = "SELECT * FROM Users WHERE UserId = @UserId AND Status = @Status";
var prms = new SqlParameter[] {
    new SqlParameter("@UserId", 1001),
    new SqlParameter("@Status", 1)
};
DataSet ds = db.GetDataSet(sql, prms);

// 2. 預存程序
DataSet ds2 = db.RunProcedureGetDataSet("usp_GetUserById", new SqlParameter[] {
    new SqlParameter("@UserId", 1001)
});

// 3. 交易範例 (參數化)
var ht = new Hashtable();
ht.Add("INSERT INTO Log (Msg) VALUES (@Msg)", new SqlParameter[] { new SqlParameter("@Msg", "Test") });
ht.Add("UPDATE Counter SET Cnt = Cnt + 1", new SqlParameter[0]);
db.ExecuteSqlTran(ht);

四、 注意事項與改進方向

• DataReader 使用：呼叫端務必在 using 區塊或 finally 中關閉 Reader，否則連線洩漏。
• 非同步支援：現代應用建議擴充 ExecuteReaderAsync、ExecuteNonQueryAsync 等非同步方法。
• 連線重試：雲端資料庫 (Azure SQL) 需加入 SqlConnection.RetryLogic 或 Polly 重試策略。
• 記錄與監控：建議在 PrepareCommand 加入 SQL 執行時間記錄 (MiniProfiler/Serilog)。