萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

.NET 呼叫 SQL Server 存儲過程完整實戰:六大參數模式與最佳實踐

基礎配置

<connectionStrings>
  <add name="NorthwindConnectionString" 
       connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True" 
       providerName="System.Data.SqlClient" />
</connectionStrings>
string connStr = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

六大參數模式完整實現

模式 1:僅返回結果集

// C#
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("Categories_GetAll", conn) { CommandType = CommandType.StoredProcedure })
using (var adapter = new SqlDataAdapter(cmd))
{
    var dt = new DataTable();
    adapter.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
}
-- SQL
CREATE PROCEDURE Categories_GetAll
AS
SELECT CategoryID, CategoryName, Description FROM Categories
GO

模式 2:無參數、無返回值(純執行)

// C#
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("Categories_InsertTest", conn) { CommandType = CommandType.StoredProcedure })
{
    conn.Open();
    int affected = cmd.ExecuteNonQuery();
    lblResult.Text = $"影響行數: {affected}";
}
-- SQL
CREATE PROCEDURE Categories_InsertTest
AS
INSERT INTO Categories (CategoryName, Description) VALUES ('Test', 'Test Desc')
GO

模式 3:返回值

// C#
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("Categories_InsertWithReturn", conn) { CommandType = CommandType.StoredProcedure })
{
    var retParam = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
    retParam.Direction = ParameterDirection.ReturnValue;
    
    conn.Open();
    int affected = cmd.ExecuteNonQuery();
    int returnValue = (int)retParam.Value;
    lblResult.Text = $"影響行數: {affected}, 返回值: {returnValue}";
}
-- SQL
CREATE PROCEDURE Categories_InsertWithReturn
AS
INSERT INTO Categories (CategoryName, Description) VALUES ('Test', 'Test Desc')
RETURN @@ROWCOUNT
GO

模式 4:輸入參數 + 輸出參數

// C#
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("Categories_InsertWithOutput", conn) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15).Value = "New Category";
    var idParam = cmd.Parameters.Add("@NewId", SqlDbType.Int);
    idParam.Direction = ParameterDirection.Output;
    
    conn.Open();
    int affected = cmd.ExecuteNonQuery();
    int newId = (int)idParam.Value;
    lblResult.Text = $"影響行數: {affected}, 新ID: {newId}";
}
-- SQL
CREATE PROCEDURE Categories_InsertWithOutput
    @CategoryName NVARCHAR(15),
    @NewId INT OUTPUT
AS
INSERT INTO Categories (CategoryName, Description) VALUES (@CategoryName, 'Desc')
SET @NewId = SCOPE_IDENTITY() -- 推薦用 SCOPE_IDENTITY() 而非 @@IDENTITY
GO

模式 5:輸入 + 輸出 + 返回值

// C#
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("Categories_Complex", conn) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15).Value = "Complex";
    var idParam = cmd.Parameters.Add("@NewId", SqlDbType.Int);
    idParam.Direction = ParameterDirection.Output;
    var retParam = cmd.Parameters.Add("@RetVal", SqlDbType.Int);
    retParam.Direction = ParameterDirection.ReturnValue;
    
    conn.Open();
    int affected = cmd.ExecuteNonQuery();
    int newId = (int)idParam.Value;
    int retVal = (int)retParam.Value;
    lblResult.Text = $"行數:{affected}, ID:{newId}, 返回:{retVal}";
}
-- SQL
CREATE PROCEDURE Categories_Complex
    @CategoryName NVARCHAR(15),
    @NewId INT OUTPUT
AS
INSERT INTO Categories (CategoryName, Description) VALUES (@CategoryName, 'Desc')
SET @NewId = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
GO

模式 6:輸出參數 + 返回值 + 結果集

// C#
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("Categories_InsertAndList", conn) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15).Value = "WithResultSet";
    var idParam = cmd.Parameters.Add("@NewId", SqlDbType.Int);
    idParam.Direction = ParameterDirection.Output;
    var retParam = cmd.Parameters.Add("@RetVal", SqlDbType.Int);
    retParam.Direction = ParameterDirection.ReturnValue;
    
    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        var dt = new DataTable();
        dt.Load(reader); // 讀取結果集
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    // 注意:輸出參數/返回值需在 reader 關閉後讀取
    int newId = (int)idParam.Value;
    int retVal = (int)retParam.Value;
    lblResult.Text = $"ID:{newId}, 返回:{retVal}";
}
-- SQL
CREATE PROCEDURE Categories_InsertAndList
    @CategoryName NVARCHAR(15),
    @NewId INT OUTPUT
AS
INSERT INTO Categories (CategoryName, Description) VALUES (@CategoryName, 'Desc')
SET @NewId = SCOPE_IDENTITY()
SELECT CategoryID, CategoryName, Description FROM Categories -- 返回結果集
RETURN @@ROWCOUNT
GO

關鍵最佳實踐

實踐說明
使用 using 區塊確保連接、命令、讀取器、適配器正確釋放,即使發生異常
參數化查詢防止 SQL 注入、執行計劃重用、型別安全
SCOPE_IDENTITY()替代 @@IDENTITY,避免觸發器導致取錯身份值
設定 CommandTimeout長時間運行存儲過程需調大(預設 30 秒)
異步執行`ExecuteNonQueryAsync`、`ExecuteReaderAsync` 配合 async/await 釋放執行緒
批次處理大量操作使用 Table-Valued Parameters 或 SqlBulkCopy
錯誤處理捕獲 SqlException,檢查 Number (2627=PK違規, 547=FK違規, 2601=唯一約束)
輸出參數讀取時機使用 DataReader 時,必須在 reader 關閉後才能讀取 Output/ReturnValue

現代化封裝:通用存儲過程執行器


public class StoredProcExecutor
{
    private readonly string _connStr;
    
    public StoredProcExecutor(string connStr) => _connStr = connStr;

    public async Task QueryAsync(string procName, params SqlParameter[] parameters)
    {
        using var conn = new SqlConnection(_connStr);
        using var cmd = new SqlCommand(procName, conn) { CommandType = CommandType.StoredProcedure };
        if (parameters?.Length > 0) cmd.Parameters.AddRange(parameters);
        
        await conn.OpenAsync();
        using var reader = await cmd.ExecuteReaderAsync();
        var dt = new DataTable();
        dt.Load(reader);
        return dt;
    }

    public async Task<(int AffectedRows, TOutput OutputParam, int ReturnValue)> ExecuteAsync(
        string procName, 
        Action setupParams)
    {
        using var conn = new SqlConnection(_connStr);
        using var cmd = new SqlCommand(procName, conn) { CommandType = CommandType.StoredProcedure };
        
        setupParams?.Invoke(cmd.Parameters);
        
        await conn.OpenAsync();
        int affected = await cmd.ExecuteNonQueryAsync();
        
        TOutput output = default;
        foreach (SqlParameter p in cmd.Parameters)
        {
            if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.InputOutput)
            {
                output = (TOutput)p.Value;
            }
        }
        int returnVal = (int)cmd.Parameters["@RETURN_VALUE"].Value; // 需顯式添加
        
        return (affected, output, returnVal);
    }
}

// 使用範例
var executor = new StoredProcExecutor(connStr);
var dt = await executor.QueryAsync("Categories_GetAll");

var (rows, newId, retVal) = await executor.ExecuteAsync("Categories_InsertWithOutput", p => 
{
    p.Add("@CategoryName", SqlDbType.NVarChar, 15).Value = "Test";
    p.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;
    p.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
});

結語

存儲過程仍是高效能、高安全性資料庫操作的核心手段。掌握六大參數模式與正確的參數方向設定,配合現代化的異步封裝,能讓資料存取層既強健又優雅。新專案若採用 EF Core/Dapper,同樣支援存儲過程映射,核心原理不變。

copyright © 萬盛學電腦網 all rights reserved