<connectionStrings>
<add name="NorthwindConnectionString"
connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
string connStr = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
// 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
// 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
// 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
// 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
// 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
// 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,同樣支援存儲過程映射,核心原理不變。