預存程序優勢回顧
- **效能**:執行計畫快取、減少網路傳輸。
- **安全**:參數化天然防 SQL Injection。
- **維護**:邏輯集中資料庫端,修改不需重新部署應用程式。
建立預存程序範例
CREATE PROCEDURE usp_GetLatestNews
@TopN INT = 10
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (@TopN) n.Id, n.Title, n.CreateTime, c.Name AS CategoryName
FROM News n
INNER JOIN Category c ON n.CategoryId = c.Id
ORDER BY n.CreateTime DESC;
END
統一資料存取介面:同時支援 SQL 文字與預存程序
public static class SqlHelper
{
private static string ConnStr = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
/// 通用查詢:傳入 SQL/預存程序名稱 + CommandType
public static DataTable ExecuteQuery(string commandText, CommandType cmdType, params SqlParameter[] parameters)
{
using (var conn = new SqlConnection(ConnStr))
using (var cmd = new SqlCommand(commandText, conn) { CommandType = cmdType })
{
if (parameters?.Length > 0) cmd.Parameters.AddRange(parameters);
using (var da = new SqlDataAdapter(cmd))
{
var dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
/// 執行非查詢 (Insert/Update/Delete)
public static int ExecuteNonQuery(string commandText, CommandType cmdType, params SqlParameter[] parameters)
{
using (var conn = new SqlConnection(ConnStr))
using (var cmd = new SqlCommand(commandText, conn) { CommandType = cmdType })
{
if (parameters?.Length > 0) cmd.Parameters.AddRange(parameters);
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}
業務層呼叫範例
public class NewsService
{
public DataTable GetLatestNews(int topN = 10)
{
return SqlHelper.ExecuteQuery("usp_GetLatestNews", CommandType.StoredProcedure,
new SqlParameter("@TopN", topN));
}
public DataTable SearchNews(string keyword)
{
return SqlHelper.ExecuteQuery(
"SELECT * FROM News WHERE Title LIKE @kw", CommandType.Text,
new SqlParameter("@kw", $"%{keyword}%"));
}
}
進階:輸出參數與回傳值
CREATE PROCEDURE usp_InsertNews
@Title NVARCHAR(200),
@NewId INT OUTPUT
AS
BEGIN
INSERT INTO News (Title) VALUES (@Title);
SET @NewId = SCOPE_IDENTITY();
RETURN 0; -- 成功
END
// C# 呼叫
var pTitle = new SqlParameter("@Title", "標題");
var pNewId = new SqlParameter("@NewId", SqlDbType.Int) { Direction = ParameterDirection.Output };
var pRet = new SqlParameter("@ReturnValue", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue };
SqlHelper.ExecuteNonQuery("usp_InsertNews", CommandType.StoredProcedure, pTitle, pNewId, pRet);
int newId = (int)pNewId.Value;
int retCode = (int)pRet.Value;
常見錯誤與避坑
| 錯誤 | 原因 | 修正 |
| CommandType 設錯 | 預存程序用 Text、SQL 文字用 StoredProcedure | 嚴格對應 |
| 參數名稱缺少 @ | SqlParameter 建構子需含 @ | new SqlParameter("@Param", val) |
| Output 參數未指定 Direction | 預設 Input | Direction = ParameterDirection.Output |
| 連線未關閉 | 未使用 using / try-finally | 全程 using 區塊 |