C# ADO.NET 封裝通用 SQLHelper 類：參數化查詢、連接池、事務支援完整實現

初學 C# 操作 SQL Server，直接在 UI 代碼中寫連接字符串、拼接 SQL 極易導致 SQL 注入、連接洩漏、維護困難。標準做法是封裝 SQLHelper 靜態類，統一管理連接字符串、提供參數化執行方法、支援事務與異步操作。

配置連接字符串

<!-- App.config / Web.config -->
<connectionStrings>
  <add name="DefaultConnection" 
       connectionString="Server=.;Database=MyDb;User Id=sa;Password=123456;TrustServerCertificate=True;"
       providerName="System.Data.SqlClient" />
</connectionStrings>

完整 SQLHelper 實現

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

public static class SQLHelper
{
    private static readonly string ConnStr = 
        ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

    #region 同步方法

    /// 
執行增刪改，返回影響行數
    public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
    {
        using (var conn = new SqlConnection(ConnStr))
        using (var cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.AddRange(parameters);
            conn.Open();
            return cmd.ExecuteNonQuery();
        }
    }

    /// 
執行查詢單值（如 COUNT、MAX、SUM）
    public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
    {
        using (var conn = new SqlConnection(ConnStr))
        using (var cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.AddRange(parameters);
            conn.Open();
            return cmd.ExecuteScalar();
        }
    }

    /// 
執行查詢，返回 DataTable
    public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
    {
        using (var conn = new SqlConnection(ConnStr))
        using (var cmd = new SqlCommand(sql, conn))
        using (var adapter = new SqlDataAdapter(cmd))
        {
            cmd.Parameters.AddRange(parameters);
            var table = new DataTable();
            adapter.Fill(table);
            return table;
        }
    }

    /// 
執行查詢，返回 DataReader（需手動關閉連接）
    public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
    {
        var conn = new SqlConnection(ConnStr);
        var cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddRange(parameters);
        conn.Open();
        // CommandBehavior.CloseConnection: Reader 關閉時自動關閉連接
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

    #endregion

    #region 異步方法

    public static async Task ExecuteNonQueryAsync(string sql, params SqlParameter[] parameters)
    {
        using (var conn = new SqlConnection(ConnStr))
        using (var cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.AddRange(parameters);
            await conn.OpenAsync();
            return await cmd.ExecuteNonQueryAsync();
        }
    }

    public static async Task ExecuteDataTableAsync(string sql, params SqlParameter[] parameters)
    {
        using (var conn = new SqlConnection(ConnStr))
        using (var cmd = new SqlCommand(sql, conn))
        using (var adapter = new SqlDataAdapter(cmd))
        {
            cmd.Parameters.AddRange(parameters);
            var table = new DataTable();
            // SqlDataAdapter 無 FillAsync，用 Task.Run 包裝或改用 DataReader 手動填充
            await Task.Run(() => adapter.Fill(table));
            return table;
        }
    }

    #endregion

    #region 事務支援

    /// 
執行事務操作，自動提交/回滾
    public static void ExecuteTransaction(Action action)
    {
        using (var conn = new SqlConnection(ConnStr))
        {
            conn.Open();
            using (var tran = conn.BeginTransaction())
            {
                try
                {
                    action(conn, tran);
                    tran.Commit();
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
    }

    /// 
異步事務
    public static async Task ExecuteTransactionAsync(Func action)
    {
        using (var conn = new SqlConnection(ConnStr))
        {
            await conn.OpenAsync();
            using (var tran = conn.BeginTransaction())
            {
                try
                {
                    await action(conn, tran);
                    await tran.CommitAsync();
                }
                catch
                {
                    await tran.RollbackAsync();
                    throw;
                }
            }
        }
    }

    #endregion
}

使用範例

// 1. 參數化查詢防注入
var dt = SQLHelper.ExecuteDataTable(
    "SELECT * FROM Users WHERE Age > @Age AND Status = @Status",
    new SqlParameter("@Age", 18),
    new SqlParameter("@Status", 1)
);

// 2. 執行插入返回自增 ID
int newId = (int)SQLHelper.ExecuteScalar(
    "INSERT INTO Users (Name, Age) VALUES (@Name, @Age); SELECT SCOPE_IDENTITY();",
    new SqlParameter("@Name", "張三"),
    new SqlParameter("@Age", 25)
);

// 3. 事務批量操作
SQLHelper.ExecuteTransaction((conn, tran) => {
    var cmd = conn.CreateCommand();
    cmd.Transaction = tran;
    cmd.CommandText = "UPDATE Accounts SET Balance -= 100 WHERE Id = 1";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "UPDATE Accounts SET Balance += 100 WHERE Id = 2";
    cmd.ExecuteNonQuery();
});

// 4. 登錄驗證
bool isValid = (int)SQLHelper.ExecuteScalar(
    "SELECT COUNT(1) FROM Users WHERE Username = @User AND PasswordHash = @Pwd",
    new SqlParameter("@User", username),
    new SqlParameter("@Pwd", ComputeHash(password))
) > 0;

核心原則總結

進階建議

• 引入 Dapper (Micro-ORM) 簡化 Entity 映射：conn.Query(sql, param)。
• 大型專案採用 Entity Framework Core 完整 ORM，支援遷移、LINQ、追蹤。
• 連接字符串加密存儲，生產環境用 Azure Key Vault / HashiCorp Vault 管理密鑰。

從手寫 SQLHelper 開始，理解 ADO.NET 底層機制，再逐步遷移到成熟 ORM，是 .NET 開發者成長的必經之路。