為什麼使用預存程序?
- 效能:執行計畫快取、減少網路傳輸
- 安全:防 SQL 注入、權限細粒度控制
- 維護:邏輯集中資料庫端、應用程式解耦
完整程式碼範例
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
public partial class RegisterPage : Page {
protected void btnRegister_Click(object sender, EventArgs e) {
string connStr = "Server=(local);Database=db_10;Uid=sa;Pwd=;";
using (SqlConnection conn = new SqlConnection(connStr)) {
conn.Open();
using (SqlCommand cmd = new SqlCommand("procInsertEmployee", conn)) {
cmd.CommandType = CommandType.StoredProcedure;
// 參數陣列:對應預存程序參數順序與型別
SqlParameter[] prams = {
new SqlParameter("@員工編號", SqlDbType.VarChar, 50),
new SqlParameter("@員工姓名", SqlDbType.VarChar, 50),
new SqlParameter("@身份證號", SqlDbType.VarChar, 50),
new SqlParameter("@聯系電話", SqlDbType.VarChar, 50)
};
// 賦值
prams[0].Value = txtEmpNo.Text.Trim();
prams[1].Value = txtName.Text.Trim();
prams[2].Value = txtIdNo.Text.Trim();
prams[3].Value = txtPhone.Text.Trim();
// 批次加入參數集合
foreach (SqlParameter p in prams) cmd.Parameters.Add(p);
int rows = cmd.ExecuteNonQuery();
if (rows > 0) {
ClientScript.RegisterStartupScript(GetType(), "ok", "alert('註冊成功!');", true);
}
}
}
}
}
關鍵點解析
CommandType.StoredProcedure:告知 ADO.NET 執行的是預存程序而非 T-SQL 字串
SqlParameter 明確指定 ParameterName、SqlDbType、Size,避免隱含轉換
- 參數名稱需與預存程序定義完全一致(含 @ 前綴)
using 區塊確保連線與命令物件正確釋放(連線池回收)
ExecuteNonQuery 回傳影響列數,可用於判斷是否成功
預存程序範例 (SQL Server)
CREATE PROCEDURE procInsertEmployee
@員工編號 VARCHAR(50),
@員工姓名 VARCHAR(50),
@身份證號 VARCHAR(50),
@聯系電話 VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Employees (EmpNo, EmpName, IdNo, Phone)
VALUES (@員工編號, @員工姓名, @身份證號, @聯系電話);
END
進階技巧
- 輸出參數:
Direction = ParameterDirection.Output,執行後讀取 param.Value
- 回傳值:
Direction = ParameterDirection.ReturnValue
- Table-Valued Parameters (TVP):批次傳入資料表 (
SqlDbType.Structured)
- 非同步執行:
await cmd.ExecuteNonQueryAsync() (需 .NET 4.5+)
- 參數化查詢防注入:即使不使用預存程序,也應一律使用 SqlParameter
常見錯誤避坑
| 錯誤 | 後果 | 修正 |
| 參數名稱拼寫錯誤 | 執行時例外 "Procedure expects parameter..." | 對照預存程序定義,使用複製貼上 |
| 未指定 SqlDbType/Size | 隱含轉換導致索引失效、截斷資料 | 明確指定型別與長度 |
| 連線字串硬編碼 | 部署困難、安全風險 | 存於 Web.config <connectionStrings> |
| 未使用 using/try-finally | 連線洩漏、連線池耗盡 | 務必包裝於 using 區塊 |