C# 存儲過程調用兩大模式深度比較與最佳實踐

存儲過程分類與適用場景

模式一：標準參數化調用 (官方推薦、功能完整)

適用於：需輸出參數、返回值、交易控制、型別安全的嚴謹場景。

完整範本 (含輸入/輸出/返回值參數)

public int ExecuteWithOutputParams(DateTime startDate, out int totalCount, out decimal avgAmount) {
    totalCount = 0; avgAmount = 0;
    int returnValue = -1;

    using (SqlConnection conn = new SqlConnection(connStr))
    using (SqlCommand cmd = new SqlCommand("usp_GetOrderStats", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;

        // 輸入參數
        cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate;

        // 輸出參數
        SqlParameter pCount = cmd.Parameters.Add("@TotalCount", SqlDbType.Int);
        pCount.Direction = ParameterDirection.Output;

        SqlParameter pAvg = cmd.Parameters.Add("@AvgAmount", SqlDbType.Decimal);
        pAvg.Precision = 18; pAvg.Scale = 2;
        pAvg.Direction = ParameterDirection.Output;

        // 返回值參數 (必須為第一個參數)
        SqlParameter pRet = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
        pRet.Direction = ParameterDirection.ReturnValue;

        conn.Open();
        cmd.ExecuteNonQuery(); // 或 ExecuteReader() 若有結果集

        // 讀取輸出與返回值
        totalCount = (int)pCount.Value;
        avgAmount = (decimal)pAvg.Value;
        returnValue = (int)pRet.Value;
    }
    return returnValue;
}

關鍵細節

• 參數順序：返回值參數必須最先加入，且 Direction = ReturnValue。
• Decimal 精度：輸出參數為 SqlDbType.Decimal 時，必須設定 Precision/Scale，否則預設 (18,0) 導致小數截斷。
• NULL 處理：輸出參數若為 DBNull，.Value 會拋例外，需先檢查 Convert.IsDBNull(p.Value)。
• 多結果集：使用 SqlDataReader.NextResult() 依序讀取。

模式二：動態 SQL 字串調用 (簡潔、開發快、功能受限)

將參數內插進 SQL 字串，如同在 SSMS 執行 EXEC usp_GetOrder @Id=1, @Name='Test'。

實作範例

public DataTable ExecuteDynamic(string procName, params object[] paramValues) {
    // 產生 @p0, @p1... 佔位符
    var paramNames = Enumerable.Range(0, paramValues.Length)
                               .Select(i => "@p" + i)
                               .ToArray();
    
    string sql = string.Format("EXEC {0} {1}", procName, 
                               string.Join(", ", paramNames.Select((n, i) => n + "=" + FormatValue(paramValues[i]))));

    using (SqlConnection conn = new SqlConnection(connStr))
    using (SqlDataAdapter da = new SqlDataAdapter(sql, conn)) {
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
}

// 簡易值格式化 (僅示範，實務請用參數化防注入)
string FormatValue(object v) {
    if (v == null) return "NULL";
    if (v is string || v is DateTime) return "'" + v.ToString().Replace("'", "''") + "'";
    return v.ToString();
}

優缺點對照

進階封裝：通用輔助類 (結合兩者優點)

public static class SqlHelper {
    // 1. 執行無回傳值存儲過程 (行為型)
    public static int ExecuteNonQuery(string procName, params SqlParameter[] parameters) {
        using (var conn = new SqlConnection(connStr))
        using (var cmd = new SqlCommand(procName, conn)) {
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null) cmd.Parameters.AddRange(parameters);
            conn.Open();
            return cmd.ExecuteNonQuery();
        }
    }

    // 2. 執行單一值存儲過程 (標量型)
    public static T ExecuteScalar(string procName, params SqlParameter[] parameters) {
        using (var conn = new SqlConnection(connStr))
        using (var cmd = new SqlCommand(procName, conn)) {
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null) cmd.Parameters.AddRange(parameters);
            conn.Open();
            object result = cmd.ExecuteScalar();
            return result == null || result == DBNull.Value ? default(T) : (T)result;
        }
    }

    // 3. 執行結果集存儲過程 - DataTable
    public static DataTable ExecuteDataTable(string procName, params SqlParameter[] parameters) {
        using (var conn = new SqlConnection(connStr))
        using (var cmd = new SqlCommand(procName, conn)) {
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null) cmd.Parameters.AddRange(parameters);
            using (var da = new SqlDataAdapter(cmd)) {
                var dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }
    }

    // 4. 執行結果集存儲過程 - 強型別列舉 (高效能)
    public static IEnumerable ExecuteReader(string procName, Func mapper, params SqlParameter[] parameters) {
        using (var conn = new SqlConnection(connStr))
        using (var cmd = new SqlCommand(procName, conn)) {
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null) cmd.Parameters.AddRange(parameters);
            conn.Open();
            using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
                while (reader.Read()) yield return mapper(reader);
            }
        }
    }
}

使用範例

// 行為型
SqlHelper.ExecuteNonQuery("usp_DeleteOrder", 
    new SqlParameter("@OrderId", 1001));

// 標量型
int newId = SqlHelper.ExecuteScalar("usp_InsertOrder", 
    new SqlParameter("@CustomerId", 50),
    new SqlParameter("@Total", 123.45m));

// 結果集 - DataTable
DataTable dt = SqlHelper.ExecuteDataTable("usp_GetOrdersByDate",
    new SqlParameter("@StartDate", DateTime.Today.AddDays(-30)));

// 結果集 - 強型別 (零 Boxing、高效能)
var orders = SqlHelper.ExecuteReader("usp_GetOrdersByDate",
    r => new Order { Id = r.GetInt32(0), Date = r.GetDateTime(1), Total = r.GetDecimal(2) },
    new SqlParameter("@StartDate", DateTime.Today.AddDays(-30)));

最佳實踐總結

1. 預設使用標準參數化：安全、效能佳、功能完整。
2. 僅在原型開發、臨時查詢、無輸出參數需求時考慮動態字串。
3. 封裝通用輔助類：減少重複代碼，統一連線管理、例外處理、參數建立邏輯。
4. 大量資料操作：考慮 SqlBulkCopy 或 Table-Valued Parameters (TVP)。
5. 非同步支援：.NET 4.5+ 使用 ExecuteNonQueryAsync、ExecuteReaderAsync 避免阻塞執行緒。