預存程序 是資料庫端預先編譯的 SQL 批次,具備效能優勢、減少網路傳輸、封裝商業邏輯等優點。本文示範如何在 C# 中使用 SqlCommand 呼叫帶有輸出參數的預存程序 AddProduct。
CREATE PROCEDURE AddProduct
@MyProductID INT OUTPUT,
@MyProductName NVARCHAR(40),
@MySupplierID INT,
@MyCategoryID INT,
@MyQuantityPerUnit NVARCHAR(20),
@MyUnitPrice MONEY,
@MyUnitsInStock SMALLINT,
@MyUnitsOnOrder SMALLINT,
@MyReorderLevel SMALLINT,
@MyDiscontinued BIT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES (@MyProductName, @MySupplierID, @MyCategoryID, @MyQuantityPerUnit, @MyUnitPrice,
@MyUnitsInStock, @MyUnitsOnOrder, @MyReorderLevel, @MyDiscontinued);
SET @MyProductID = SCOPE_IDENTITY(); -- 回傳新增的自增 ID
END
using System;
using System.Data;
using System.Data.SqlClient;
namespace ExecuteStorageProcess
{
public class ExecuteStorageProcess
{
public static void Main()
{
string connStr = "server=localhost;database=Northwind;uid=sa;pwd=sa";
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
// 1. 指定預存程序名稱與 CommandType
cmd.CommandText = "AddProduct";
cmd.CommandType = CommandType.StoredProcedure;
// 2. 定義參數:方向、型別、值
// 輸出參數
SqlParameter pId = new SqlParameter("@MyProductID", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(pId);
// 輸入參數
cmd.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget";
cmd.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box";
cmd.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99m;
cmd.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10;
cmd.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;
cmd.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5;
cmd.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1;
// 3. 執行
cmd.ExecuteNonQuery();
// 4. 讀取輸出參數
Console.WriteLine($"新產品 ID = {cmd.Parameters["@MyProductID"].Value}");
}
}
}
}
}
| 項目 | 說明 |
|---|---|
| CommandType.StoredProcedure | 告知 ADO.NET 這是預存程序,而非 SQL 字串 |
| ParameterDirection.Output | 標記參數為輸出,執行後由資料庫回填值 |
| SCOPE_IDENTITY() | 取得當前範圍內最後插入的自增識別值,比 @@IDENTITY 更安全 |
| 參數順序 | 使用 CommandType.StoredProcedure 時,參數順序不重要,靠名稱對應 |
| SqlDbType 對應 | 需與資料庫欄位型別精確對應,避免隱含轉換導致效能損耗或錯誤 |
cmd.ExecuteReader() 並呼叫 reader.NextResult() 切換。RETURN @code,C# 端加參數 Direction = ParameterDirection.ReturnValue。SqlDbType.Structured。await cmd.ExecuteNonQueryAsync(),避免阻塞執行緒。using 確保連線與命令物件正確釋放。