ASP.NET LINQ to SQL 操作儲存程序完整範例 (2)

實務專案常使用儲存程序封裝資料庫邏輯。本文示範如何在 LINQ to SQL 中對應並呼叫儲存程序，完成 Employees 資料表的 CRUD 操作。

資料庫準備：Northwind Employees 相關儲存程序

-- (1) 查詢所有員工
CREATE PROCEDURE [dbo].[Employees_GetAll]
AS
SELECT * FROM Employees ORDER BY EmployeeID;

-- (2) 依 ID 查詢單筆
CREATE PROCEDURE [dbo].[Employees_GetByID]
    @ID int
AS
SELECT * FROM Employees WHERE EmployeeID = @ID;

-- (3) 新增員工
CREATE PROCEDURE [dbo].[Employees_Insert]
    @FirstName nvarchar(20),
    @LastName nvarchar(20)
AS
INSERT INTO Employees (FirstName, LastName) VALUES (@FirstName, @LastName);

-- (4) 更新員工
CREATE PROCEDURE [dbo].[Employees_Update]
    @ID int,
    @FirstName nvarchar(20),
    @LastName nvarchar(20)
AS
UPDATE Employees SET FirstName = @FirstName, LastName = @LastName WHERE EmployeeID = @ID;

DBML 設計工具對應儲存程序

1. 開啟 Northwind.dbml 設計介面
2. 伺服器總管展開「儲存程序」節點
3. 將四支 SP 拖曳至設計面右側方法窗格
4. 儲存後，NorthwindDataContext 自動新增對應方法：
   Employees_GetAll()、Employees_GetByID(int)、Employees_Insert(string, string)、Employees_Update(int, string, string)

呼叫儲存程序的 C# 程式碼

using (var db = new NorthwindDataContext()) {
    // 1. 查詢所有 - 回傳 ISingleResult
    var allEmps = db.Employees_GetAll().ToList();
    
    // 2. 查詢單筆 - 回傳 ISingleResult
    var emp = db.Employees_GetByID(1).FirstOrDefault();
    
    // 3. 新增 - 回傳 int (影響列數)
    int inserted = db.Employees_Insert("Jane", "Smith");
    
    // 4. 更新 - 回傳 int
    int updated = db.Employees_Update(1, "Jane", "Doe");
}

自訂回傳型別（進階）

預設產生的 Result 類別名稱較長（如 Employees_GetAllResult）。可在 DBML 設計面方法屬性視窗修改 Return Type 為自訂實體類別（需欄位一致），或選擇 None 僅執行不回傳。

輸出參數與回傳值處理

-- 帶 OUTPUT 參數的儲存程序範例
CREATE PROCEDURE [dbo].[Employees_InsertWithID]
    @FirstName nvarchar(20),
    @LastName nvarchar(20),
    @NewID int OUTPUT
AS
INSERT INTO Employees (FirstName, LastName) VALUES (@FirstName, @LastName);
SET @NewID = SCOPE_IDENTITY();

拖曳至 DBML 後，產生的方法簽名為：

int Employees_InsertWithID(string firstName, string lastName, ref int newID)

呼叫方式：

int newId = 0;
db.Employees_InsertWithID("Bob", "Wilson", ref newId);
// newId 即為資料庫自動產生的 EmployeeID

交易控制

using (var db = new NorthwindDataContext()) {
    db.Connection.Open();
    var tx = db.Connection.BeginTransaction();
    db.Transaction = tx;
    try {
        db.Employees_Insert("A", "B");
        db.Employees_Update(2, "C", "D");
        tx.Commit();
    } catch {
        tx.Rollback();
        throw;
    }
}

效能與除錯提示

• 啟用 db.Log = Console.Out 觀察產生的 SQL
• 儲存程序回傳多結果集時，使用 IMultipleResults 依序取得
• 避免在迴圈中逐筆呼叫 SP，改用表值參數批次處理