C# 呼叫 SQL Server 預存程序完整範例：參數、輸出值、結果集綁定

概述

預存程序是資料庫端封裝業務邏輯的核心機制。本文示範 C# 如何正確呼叫帶輸入/輸出參數的預存程序，並將結果集綁定至 GridView、取得輸出參數值。

資料庫端：預存程序定義

CREATE PROCEDURE P_GetCustomerByName
    @Name VARCHAR(50),
    @RowCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT CustomerID, Name, Address, Phone, CreateDate
    FROM T_Customer 
    WHERE Name = @Name;
    
    SET @RowCount = @@ROWCOUNT;
    
    RETURN 0; -- 成功代碼
END
GO

-- 測試執行
DECLARE @rc INT;
EXEC P_GetCustomerByName '張三', @rc OUTPUT;
SELECT @rc AS RowCount;

Web.config 連接字串設定

<configuration>
  <connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Server=.;Database=TestDB;User ID=sa;Password=your_password;TrustServerCertificate=True;" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

C# 完整呼叫代碼

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;

public partial class CustomerQuery : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            QueryCustomer("張三");
    }

    private void QueryCustomer(string name)
    {
        string connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        
        using (SqlConnection conn = new SqlConnection(connStr))
        using (SqlCommand cmd = new SqlCommand("P_GetCustomerByName", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 30; // 秒
            
            // 1. 輸入參數
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = name;
            
            // 2. 輸出參數（關鍵：指定 Direction）
            SqlParameter outputParam = cmd.Parameters.Add("@RowCount", SqlDbType.Int);
            outputParam.Direction = ParameterDirection.Output;
            
            // 3. 回傳值參數（可選）
            SqlParameter returnParam = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
            returnParam.Direction = ParameterDirection.ReturnValue;

            try
            {
                conn.Open();
                
                // 4. 執行並取得結果集
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "Customers");
                    
                    // 綁定 GridView
                    gvCustomers.DataSource = ds.Tables["Customers"];
                    gvCustomers.DataBind();
                }
                
                // 5. 讀取輸出參數（必須在讀取完結果集後）
                int rowCount = (int)cmd.Parameters["@RowCount"].Value;
                int returnCode = (int)cmd.Parameters["@RETURN_VALUE"].Value;
                
                lblResult.Text = $"查詢完成：{rowCount} 筆資料，回傳代碼：{returnCode}";
            }
            catch (SqlException ex)
            {
                lblResult.Text = $"資料庫錯誤：{ex.Message}";
                // 記錄日誌...
            }
            catch (Exception ex)
            {
                lblResult.Text = $"系統錯誤：{ex.Message}";
            }
        }
    }
}

關鍵點總結

進階場景

多個結果集

using (SqlDataReader reader = cmd.ExecuteReader())
{
    // 第一個結果集
    if (reader.HasRows) { /* 處理 */ }
    
    // 移至下一個結果集
    if (reader.NextResult())
    {
        if (reader.HasRows) { /* 處理第二個結果集 */ }
    }
}
// 此時才能讀取 Output 參數

Table-Valued Parameter (TVP) - SQL Server 2008+

-- 資料庫端建立型別
CREATE TYPE CustomerList AS TABLE (Name VARCHAR(50), Email VARCHAR(100));

-- C# 傳遞 DataTable
DataTable tvp = new DataTable();
tvp.Columns.Add("Name", typeof(string));
tvp.Columns.Add("Email", typeof(string));
// ... 填充資料

SqlParameter tvpParam = cmd.Parameters.Add("@CustomerList", SqlDbType.Structured);
tvpParam.TypeName = "dbo.CustomerList";
tvpParam.Value = tvp;

非同步呼叫 (.NET 4.5+)

await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
// 或
using (var reader = await cmd.ExecuteReaderAsync()) { /* ... */ }

最佳實踐檢查清單

• ✅ 使用 using 確保連線/命令/讀取器正確釋放
• ✅ 參數化查詢防止 SQL 注入
• ✅ 設定 CommandTimeout 避免長時間阻塞
• ✅ 輸出參數務必設定 Direction
• ✅ 捕捉 SqlException 依 Number 區分錯誤類型（死結、逾時、權限等）
• ✅ 記錄執行時間與參數值供效能分析
• ✅ 考慮使用 Dapper/EF Core 簡化代碼

總結

掌握預存程序呼叫細節是 ADO.NET 開發基本功。現代專案建議搭配 Dapper QueryMultipleAsync 或 EF Core FromSqlRaw 簡化樣板代碼，但理解底層參數綁定機制對除錯與效能調優仍不可或缺。