C# 訪問 MySQL 完整框架封裝：DBUtility 庫設計、SQLHelper 通用類與增刪改查參數化實戰

專案結構

Solution
├── DBUtility (Class Library)  -- 通用數據訪問層
│   ├── SQLHelper.cs           -- 核心封裝類
│   └── App.config             -- 連接字符串配置
└── BusinessApp (Console/Web)  -- 業務調用層
    └── References → DBUtility

一、配置連接字符串

App.config / Web.config：

<connectionStrings>
  <add name="DBConnectionString"
       connectionString="Server=127.0.0.1;Database=YourDB;Uid=your_user;Pwd=your_pwd;"
       providerName="MySql.Data.MySqlClient" />
</connectionStrings>

引用 MySql.Data.dll (NuGet: MySql.Data)。

二、SQLHelper 核心封裝類

using System;
using System.Data;
using System.Collections;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace DBUtility
{
    public abstract class SQLHelper
    {
        // 讀取配置文件連接字符串
        public static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        // 參數緩存 (Hashtable 線程安全包裝)
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        #region 核心執行方法

        /// 
執行非查詢命令 (INSERT/UPDATE/DELETE)，返回影響行數
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// 
執行查詢，返回 DataReader (需手動關閉連接)
        public static MySqlDataReader ExecuteReader(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(ConnectionString);
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // 讀取器關閉時自動關連接
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// 
執行查詢，返回 DataSet (斷開式，適合小結果集)
        public static DataSet GetDataSet(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
            }
        }

        /// 
執行查詢，返回單一值 (首行首列)
        public static object ExecuteScalar(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        #endregion

        #region 參數緩存 (針對重複 SQL 優化)

        public static void CacheParameters(string cacheKey, params MySqlParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
        }

        public static MySqlParameter[] GetCachedParameters(string cacheKey)
        {
            MySqlParameter[] cached = parmCache[cacheKey] as MySqlParameter[];
            if (cached == null) return null;
            // 深拷貝避免參數值被修改影響緩存
            MySqlParameter[] cloned = new MySqlParameter[cached.Length];
            for (int i = 0; i < cached.Length; i++)
                cloned[i] = (MySqlParameter)((ICloneable)cached[i]).Clone();
            return cloned;
        }

        #endregion

        #region 內部命令準備

        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans,
                                           CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {
            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 (MySqlParameter p in cmdParms)
                {
                    // 處理 null 值
                    if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && p.Value == null)
                        p.Value = DBNull.Value;
                    cmd.Parameters.Add(p);
                }
            }
        }

        #endregion
    }
}

三、業務層 CRUD 實戰範例

假設表 T_Photo (ID bigint PK, Name varchar, UserID int, OrderCode varchar)。

1. 插入

public int Add(int userId, string name)
{
    StringBuilder sql = new StringBuilder();
    sql.Append("INSERT INTO T_Photo (Name, UserID) VALUES (?Name, ?UserID)");
    MySqlParameter[] parms = {
        new MySqlParameter("?Name", MySqlDbType.VarChar) { Value = name },
        new MySqlParameter("?UserID", MySqlDbType.Int32) { Value = userId }
    };
    return SQLHelper.ExecuteNonQuery(sql.ToString(), CommandType.Text, parms);
}

2. 刪除

public int Delete(long id, int userId)
{
    StringBuilder sql = new StringBuilder();
    sql.Append("DELETE FROM T_Photo WHERE ID = ?ID AND UserID = ?UserID");
    MySqlParameter[] parms = {
        new MySqlParameter("?ID", MySqlDbType.Int64) { Value = id },
        new MySqlParameter("?UserID", MySqlDbType.Int32) { Value = userId }
    };
    return SQLHelper.ExecuteNonQuery(sql.ToString(), CommandType.Text, parms);
}

3. 更新

public int EditName(long id, int userId, string name)
{
    StringBuilder sql = new StringBuilder();
    sql.Append("UPDATE T_Photo SET Name = ?Name WHERE ID = ?ID AND UserID = ?UserID");
    MySqlParameter[] parms = {
        new MySqlParameter("?ID", MySqlDbType.Int64) { Value = id },
        new MySqlParameter("?UserID", MySqlDbType.Int32) { Value = userId },
        new MySqlParameter("?Name", MySqlDbType.VarChar) { Value = name }
    };
    return SQLHelper.ExecuteNonQuery(sql.ToString(), CommandType.Text, parms);
}

4. 查詢 (DataReader 流式讀取)

public MySqlDataReader GetListByOrderCode(string orderCode)
{
    StringBuilder sql = new StringBuilder();
    sql.Append("SELECT ID, OrderCount, Subtotal, ProductID FROM t_orderdetail ");
    sql.Append("WHERE OrderCode = ?OrderCode ORDER BY ID DESC");
    MySqlParameter[] parms = {
        new MySqlParameter("?OrderCode", MySqlDbType.VarChar) { Value = orderCode }
    };
    return SQLHelper.ExecuteReader(sql.ToString(), CommandType.Text, parms);
}
// 調用時：
// using (var reader = GetListByOrderCode("ORD123")) { while(reader.Read()) { ... } }

5. 調用存儲過程

public int BackOrder(long orderId)
{
    MySqlParameter[] parms = {
        new MySqlParameter("?OrderId", MySqlDbType.Int64) { Value = orderId }
    };
    return SQLHelper.ExecuteNonQuery("BackOrder", CommandType.StoredProcedure, parms);
}

四、進階擴展建議

1. 事務支援：擴展 ExecuteNonQueryTrans 接受 MySqlTransaction 參數，實現多操作原子性。
2. 異步方法：.NET Core/.NET 5+ 實現 ExecuteNonQueryAsync 等，提升吞吐。
3. 分頁封裝：增加 GetPagedDataSet(sql, pageIndex, pageSize, ...) 自動拼接 LIMIT。
4. 實體映射：引入 Dapper 或自寫 DataReaderMapToList<T>() 擺脫 DataSet/DataTable。
5. 連接池監控：定期輸出 MySqlConnection.ConnectionString 中 Pooling=true;Min Pool Size=5;Max Pool Size=100 狀態。

五、總結

通過 DBUtility.SQLHelper 封裝連接生命週期、參數化防注入、命令預處理、參數緩存，構建出可復用、可維護、高性能的 C# MySQL 訪問基礎設施。業務代碼專注 SQL 與參數，不再處理連接/命令/讀取器的瑣碎資源管理。