SQL Server 帶參數預存程序執行：SqlHelper.ExecuteNonQuery 參數化實作範例

場景：通用資料存取元件呼叫參數化預存程序

採用 SqlHelper 類別（Microsoft Data Access Application Block 經典模式）執行帶參數的預存程序，核心方法 ExecuteNonQuery。

方法簽名與參數說明

public static int ExecuteNonQuery(
    string connectionString,      // 連線字串
    CommandType commandType,      // CommandType.StoredProcedure
    string commandText,           // 預存程序名稱
    params SqlParameter[] commandParameters  // 參數陣列
)

完整呼叫範例

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;  // SqlHelper 所在命名空間

public class OrderRepository
{
    private readonly string _connStr = "Server=.;Database=Northwind;Integrated Security=True;";

    public void UpdateOrderStatus(int orderId, string newStatus, DateTime updatedAt)
    {
        // 1. 定義參數陣列
        SqlParameter[] parameters = new SqlParameter[]
        {
            new SqlParameter("@OrderID", SqlDbType.Int) { Value = orderId },
            new SqlParameter("@NewStatus", SqlDbType.NVarChar, 50) { Value = newStatus },
            new SqlParameter("@UpdatedAt", SqlDbType.DateTime) { Value = updatedAt },
            // 輸出參數範例
            new SqlParameter("@RowsAffected", SqlDbType.Int) { Direction = ParameterDirection.Output }
        };

        // 2. 執行
        int result = SqlHelper.ExecuteNonQuery(
            _connStr,
            CommandType.StoredProcedure,
            "usp_UpdateOrderStatus",
            parameters
        );

        // 3. 取得輸出參數
        int rows = (int)parameters[3].Value;
        Console.WriteLine($"執行完成，影響列數: {rows}, ExecuteNonQuery 回傳: {result}");
    }
}

對應預存程序範本

CREATE PROCEDURE usp_UpdateOrderStatus
    @OrderID INT,
    @NewStatus NVARCHAR(50),
    @UpdatedAt DATETIME,
    @RowsAffected INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Orders
    SET Status = @NewStatus, UpdatedAt = @UpdatedAt
    WHERE OrderID = @OrderID;
    SET @RowsAffected = @@ROWCOUNT;
END
GO

參數建立最佳實務

進階：封裝參數建立的 Helper 方法

public static class SqlParam
{
    public static SqlParameter Create(string name, SqlDbType type, object value, int size = 0)
    {
        var p = new SqlParameter(name, type);
        if (size > 0) p.Size = size;
        p.Value = value ?? DBNull.Value;
        return p;
    }

    public static SqlParameter Output(string name, SqlDbType type, int size = 0)
    {
        var p = Create(name, type, null, size);
        p.Direction = ParameterDirection.Output;
        return p;
    }
}

// 使用簡化版
var parameters = new[]
{
    SqlParam.Create("@OrderID", SqlDbType.Int, orderId),
    SqlParam.Create("@NewStatus", SqlDbType.NVarChar, newStatus, 50),
    SqlParam.Create("@UpdatedAt", SqlDbType.DateTime, updatedAt),
    SqlParam.Output("@RowsAffected", SqlDbType.Int)
};

注意事項

• SqlHelper 為舊版 DAAB 元件，新專案建議改用 Dapper、Entity Framework Core 或 ADO.NET 原生 非同步模式（ExecuteNonQueryAsync）。
• 連線字串建議從 ConfigurationManager.ConnectionStrings 讀取，勿硬編碼。
• 大量批次操作考慮 SqlBulkCopy 或 Table-Valued Parameters (TVP)。