ASP.NET 調用 SQL Server 存儲過程完整實戰：從基礎到參數、返回值處理

存儲過程基礎認知

• 定義：預編譯的 SQL 批次，接受參數、執行邏輯、返回結果（結果集/輸出參數/返回碼）。
• 優勢：性能（預編譯）、安全（參數化/權限控制）、封裝（業務邏輯下沉）、維護性（分層解耦）。
• 核心語法：CREATE PROCEDURE schema.name @param type [OUTPUT] AS BEGIN ... END

場景一：無參存儲過程調用

-- T-SQL
CREATE PROCEDURE dbo.GetUserIDs
AS
    SELECT UserID FROM Users ORDER BY UserID DESC;

C# 調用關鍵點：

using (SqlConnection conn = ADConnection.CreateConnection())
using (SqlCommand cmd = new SqlCommand("GetUserIDs", conn))
{
    cmd.CommandType = CommandType.StoredProcedure; // 關鍵：聲明為存儲過程
    conn.Open();
    using (SqlDataReader dr = cmd.ExecuteReader())
    {
        while (dr.Read())
        {
            // 處理 dr[0] 等列
        }
    }
}

本質：將原內聯 SQL 字符串替換為過程名，並設置 CommandType.StoredProcedure。

場景二：帶輸入參數存儲過程

-- T-SQL
CREATE PROCEDURE dbo.GetCommentsByDateRange
    @StartDate VARCHAR(16),
    @EndDate VARCHAR(16)
AS
    SELECT ID FROM Table_AX
    WHERE CommentDateTime > @StartDate AND CommentDateTime < @EndDate
    ORDER BY ContentOwnerID DESC;

C# 使用 SqlDataAdapter 填充 DataSet：

using (SqlConnection conn = ADConnection.CreateConnection())
using (SqlDataAdapter da = new SqlDataAdapter("GetCommentsByDateRange", conn))
{
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    da.SelectCommand.Parameters.Add(new SqlParameter("@StartDate", startDate));
    da.SelectCommand.Parameters.Add(new SqlParameter("@EndDate", endDate));
    DataSet ds = new DataSet();
    da.Fill(ds); // 自動打開/關閉連接
    return ds;
}

參數化查詢防注入，類型安全，計劃復用。

場景三：捕獲執行狀態（返回碼/輸出參數）

-- T-SQL
CREATE PROCEDURE dbo.UpdatePassword
    @UserName NVARCHAR(20),
    @Password NVARCHAR(20)
AS
    DECLARE @Err INT;
    UPDATE WL_User SET Password = @Password WHERE UserName = @UserName;
    SET @Err = @@ERROR; -- 0 成功，非 0 失敗
    RETURN @Err; -- 或 SELECT @Err AS ErrCode 輸出參數

C# 獲取返回值：

SqlParameter retParam = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
retParam.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int result = (int)retParam.Value; // 0 成功

或使用 OUTPUT 參數返回更豐富信息。

最佳實踐總結

1. 始終使用 using 確保連接釋放。
2. 參數集合顯式添加，避免 AddWithValue 類型推斷問題。
3. 業務邏輯下沉存儲過程，應用層僅負責調度與呈現。
4. 統一錯誤處理模式：返回碼表示執行狀態，結果集/輸出參數攜帶數據。