萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

ASP.NET 以參數化方式呼叫預存程序並統一查詢介面

預存程序優勢回顧

  • **效能**:執行計畫快取、減少網路傳輸。
  • **安全**:參數化天然防 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預設 InputDirection = ParameterDirection.Output
連線未關閉未使用 using / try-finally全程 using 區塊
copyright © 萬盛學電腦網 all rights reserved