您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

C# 通用資料存取元件 SqlHelper 完整實作：參數化、緩存、事務支援

編輯：關於SqlServer

企業級 .NET 專案離不開穩健的資料存取層。本文提供生產就緒的 SqlHelper 靜態類別，涵蓋 ExecuteNonQuery、ExecuteReader、ExecuteScalar 三大核心方法，支援 **連線字串/現有連線/事務** 三種上下文、**參數陣列**、**參數緩存**、**CommandBehavior.CloseConnection** 自動資源釋放，採標準 ADO.NET 無第三方依賴。

核心設計亮點

多重多型：每個方法提供 3 個 overload (connectionString / SqlConnection / SqlTransaction)
參數陣列：params SqlParameter[] 簡化呼叫，防 SQL 注入
參數緩存：CacheParameters(key, ...) / GetCachedParameters(key) 減少重複建構開銷
安全資源管理：using 區塊 + CommandBehavior.CloseConnection 確保連線釋放
異常透傳：ExecuteReader 捕獲異常關閉連線後 throw 保留堆疊

完整程式碼 (可直接複製使用)

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

public abstract class SqlHelper
{
    // 唯讀連線字串 (從 config 讀取)
    public static readonly string ConnectionString =
        ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

    // 參數緩存 (Thread-safe)
    private static readonly Hashtable ParmCache = Hashtable.Synchronized(new Hashtable());

    #region ExecuteNonQuery
    public static int ExecuteNonQuery(string connStr, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        using (var conn = new SqlConnection(connStr))
        using (var cmd = new SqlCommand())
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
    }

    public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        using (var cmd = new SqlCommand())
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
    }

    public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        using (var cmd = new SqlCommand())
        {
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
    }
    #endregion

    #region ExecuteReader
    public static SqlDataReader ExecuteReader(string connStr, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        var cmd = new SqlCommand();
        var conn = new SqlConnection(connStr);
        try
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }
        catch
        {
            conn.Close();
            throw;
        }
    }
    #endregion

    #region ExecuteScalar
    public static object ExecuteScalar(string connStr, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        using (var conn = new SqlConnection(connStr))
        using (var cmd = new SqlCommand())
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
    }

    public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
    {
        using (var cmd = new SqlCommand())
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
    }
    #endregion

    #region Parameter Cache
    public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
    {
        ParmCache[cacheKey] = cmdParms;
    }

    public static SqlParameter[] GetCachedParameters(string cacheKey)
    {
        var cached = ParmCache[cacheKey] as SqlParameter[];
        if (cached == null) return null;

        // 深層複製 (Clone) 避免多執行緒共用同一參數物件導致狀態汙染
        var cloned = new SqlParameter[cached.Length];
        for (int i = 0; i < cached.Length; i++)
            cloned[i] = (SqlParameter)((ICloneable)cached[i]).Clone();
        return cloned;
    }
    #endregion

    #region Private Helper
    private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans,
        CommandType cmdType, string cmdText, SqlParameter[] 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 (var p in cmdParms) cmd.Parameters.Add(p);
    }
    #endregion
}

實戰呼叫範例

// 1. 簡單查詢
var users = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text,
    "SELECT Id, Name FROM Users WHERE Status = @Status", new SqlParameter("@Status", 1));

// 2. 預存程序 + OUTPUT 參數
var p = new SqlParameter("@NewId", SqlDbType.Int) { Direction = ParameterDirection.Output };
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.StoredProcedure, "usp_AddUser",
    new SqlParameter("@Name", "John"), p);
int newId = (int)p.Value;

// 3. 事務批次
using (var conn = new SqlConnection(SqlHelper.ConnectionString))
{
    conn.Open();
    using (var trans = conn.BeginTransaction())
    {
        try
        {
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE Accounts SET Balance -= 100 WHERE Id = 1");
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE Accounts SET Balance += 100 WHERE Id = 2");
            trans.Commit();
        }
        catch { trans.Rollback(); throw; }
    }
}

// 4. 參數緩存 (高頻呼叫的預存程序)
SqlHelper.CacheParameters("usp_GetUser", new SqlParameter("@Id", SqlDbType.Int));
var cached = SqlHelper.GetCachedParameters("usp_GetUser");
cached[0].Value = 123;
var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.StoredProcedure, "usp_GetUser", cached);