在 .NET 應用中透過 ADO.NET 呼叫 SQL Server 預存程序，核心類別為 SqlCommand (CommandType = StoredProcedure)。本文提供完整 ASP.NET WebForms 範例，涵蓋連線字串設定、參數綁定 (輸入/輸出/傳回值)、SqlDataAdapter 填充 DataSet 綁定 GridView、資源釋放模式與最佳實踐。
CREATE PROCEDURE dbo.TestProc
@InputParam NVARCHAR(3000),
@OutputParam INT OUTPUT,
@ReturnStatus INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- 範例邏輯：依輸入參數查詢，回傳結果集與輸出參數
SELECT * FROM SomeTable WHERE Name LIKE @InputParam + '%';
SET @OutputParam = @@ROWCOUNT;
SET @ReturnStatus = 0; -- 0 成功，非 0 為自訂錯誤碼
RETURN @ReturnStatus;
END;<connectionStrings>
<add name="StrCon"
connectionString="Data Source=.;Initial Catalog=MyDB;User ID=app_user;Password=StrongPwd;Encrypt=True;TrustServerCertificate=True"
providerName="System.Data.SqlClient" />
</connectionStrings>using System;
using System.Data;
using System.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
public partial class _Default : Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
BindData();
}
protected void Btn_ok_Click(object sender, EventArgs e)
{
BindData();
}
private void BindData()
{
string connStr = ConfigurationManager.ConnectionStrings["StrCon"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("dbo.TestProc", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// 1. 輸入參數
cmd.Parameters.Add("@InputParam", SqlDbType.NVarChar, 3000).Value = txt_Message.Text.Trim();
// 2. 輸出參數
SqlParameter outParam = cmd.Parameters.Add("@OutputParam", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
// 3. 傳回值參數 (必須為第一個參數或明確指定 Direction=ReturnValue)
SqlParameter retParam = cmd.Parameters.Add("@ReturnStatus", SqlDbType.Int);
retParam.Direction = ParameterDirection.ReturnValue;
try
{
conn.Open();
// 4. 執行並填充 DataSet (適用回傳結果集)
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
ShowList.DataSource = ds;
ShowList.DataBind();
}
// 5. 讀取輸出參數與傳回值 (在 DataAdapter.Fill 完成後讀取)
int rowCount = (int)outParam.Value;
int returnStatus = (int)retParam.Value;
lbl_Info.Text = $"查詢筆數: {rowCount}, 執行狀態: {returnStatus}";
}
catch (SqlException ex)
{
lbl_Info.Text = "資料庫錯誤: " + ex.Message;
// 記錄完整堆疊至 Log 系統
}
} // using 區塊自動釋放連線、命令、DataAdapter
}
}| 需求 | SqlParameter 設定 | 讀取時機 |
|---|---|---|
| 輸入參數 | .Value = val (預設 Direction=Input) | Execute 前 |
| 輸出參數 | Direction = ParameterDirection.Output | Execute 後 |
| 輸入/輸出參數 | Direction = ParameterDirection.InputOutput | Execute 後 |
| 傳回值 (RETURN) | Direction = ParameterDirection.ReturnValue | Execute 後 |
using (SqlDataReader dr = cmd.ExecuteReader())
{
do
{
if (dr.HasRows)
{
// 處理第 N 個結果集
while (dr.Read()) { /* ... */ }
}
} while (dr.NextResult());
}int affected = cmd.ExecuteNonQuery(); // 回傳影響列數
// 輸出參數、傳回值同樣在 ExecuteNonQuery 後讀取using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlTransaction txn = conn.BeginTransaction();
try
{
cmd.Transaction = txn;
// 多個命令...
txn.Commit();
}
catch
{
txn.Rollback();
throw;
}
}await cmd.ExecuteNonQueryAsync();
await cmd.ExecuteReaderAsync();
await cmd.ExecuteScalarAsync();web.config 使用 aspnet_regiis -pef connectionStrings 加密區段。IDisposable 物件 (Connection, Command, DataAdapter, DataReader) 確定釋放。conn.Close() 即歸還池中。cmd.CommandTimeout = 300 (秒)。SqlException，依 ex.Number (如 2627 PK 違規、1205 死結) 給予使用者友善訊息，詳細錯誤寫入 Log (Serilog/NLog/ELMAH)。| 錯誤 | 原因 | 解決 |
|---|---|---|
| Procedure 'TestProc' expects parameter '@InputParam', which was not supplied | 參數名稱拼寫錯誤 / 未加入 Parameters 集合 | 核對預存程序定義與 C# 參數名稱 (含 @) |
| Parameter '@OutputParam' has not been set to a value | 未設定 Direction=Output | 加入 param.Direction = ParameterDirection.Output |
| Connection property has not been initialized | SqlCommand 未指定 Connection | cmd.Connection = conn 或建構子傳入 |
| ExecuteNonQuery requires an open and available Connection | 連線未 Open / 已關閉 | 確認 conn.Open() 在 Execute 前 |
ADO.NET SqlCommand 呼叫預存程序是 .NET 資料存取的基石。掌握 CommandType.StoredProcedure、參數方向設定、SqlDataAdapter/SqlDataReader 選擇、using 資源管理、交易與非同步模式，並落實參數化、連線字串加密、異常分級處理，即能建構安全、高效、可維護的資料存取層。
