LINQ to SQL 存儲程序 CRUD 完整實戰教學

LINQ to SQL 對存儲程序提供一級支援，能將資料庫端的 T-SQL 邏輯無縫映射為資料內容 (DataContext) 的強型別方法。本文以經典 Northwind 資料庫 Customers 表為例，示範查詢、刪除、更新、新增四大操作的存儲程序撰寫、DBML 映射與呼叫全流程。

資料庫端：四大存儲程序定義

1. 查詢客戶 selCustomers

CREATE PROCEDURE [dbo].[selCustomers]
    @ncCustomerID NVARCHAR(5) = NULL,
    @nvcCountry   NVARCHAR(15) = NULL
AS
BEGIN
    IF @ncCustomerID IS NULL OR @ncCustomerID = '' SET @ncCustomerID = NULL;
    IF @nvcCountry   IS NULL OR @nvcCountry   = '' SET @nvcCountry   = NULL;

    SELECT * FROM Customers
    WHERE CustomerID LIKE ISNULL(@ncCustomerID, '%')
      AND Country    LIKE ISNULL(@nvcCountry, '%');
    RETURN;
END

2. 刪除客戶 delCustomers

CREATE PROCEDURE [dbo].[delCustomers]
    @ncCustomerID NCHAR(5)
AS
BEGIN
    DELETE FROM Customers WHERE CustomerID = @ncCustomerID;
    RETURN;
END

3. 更新客戶 updCustomers

CREATE PROCEDURE [dbo].[updCustomers]
    @ncCustomerID    NCHAR(5),
    @nvcCompanyName  NVARCHAR(40),
    @nvcAddress      NVARCHAR(60),
    @intReturnValue  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Customers
    SET CompanyName = @nvcCompanyName,
        Address     = @nvcAddress
    WHERE CustomerID = @ncCustomerID;

    IF @@ERROR <> 0
    BEGIN
        SET @intReturnValue = -1; -- 更新失敗
        RETURN;
    END
    RETURN;
END

4. 新增客戶 insCustomers (修正原文重複錯誤)

CREATE PROCEDURE [dbo].[insCustomers]
    @ncCustomerID    NCHAR(5),
    @nvcCompanyName  NVARCHAR(40),
    @nvcAddress      NVARCHAR(60),
    @intReturnValue  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Customers (CustomerID, CompanyName, Address)
    VALUES (@ncCustomerID, @nvcCompanyName, @nvcAddress);

    IF @@ERROR <> 0
    BEGIN
        SET @intReturnValue = -1;
        RETURN;
    END
    SET @intReturnValue = 0; -- 成功
    RETURN;
END

⚠️ 原文第 4 節誤將 updCustomers 重複貼上，此處已更正為正確的 INSERT 邏輯。

Visual Studio DBML 設計器操作步驟

1. 專案新增 LINQ to SQL 類別，命名 Northwind.dbml。
2. 開啟 伺服器總管 → 連接 Northwind 資料庫 → 展開 預存程序 資料夾。
3. 將上述四個存儲程序 **拖曳至 DBML 設計介面右側 (方法窗格)**。
4. 在屬性視窗修改方法名稱為語意化名稱：
   • selCustomers → GetCustomers
   • delCustomers → DeleteCustomers
   • updCustomers → UpdateCustomers
   • insCustomers → InsertCustomers
5. 儲存 DBML，設計器自動產生 NorthwindDataContext 及對應方法簽章。

自動產生的方法簽章範例 (節錄)

[Function(Name="dbo.selCustomers")]
public ISingleResult<Customer> GetCustomers(
    [Parameter(DbType="NVarChar(5)")] System.Nullable<string> ncCustomerID,
    [Parameter(DbType="NVarChar(15)")] System.Nullable<string> nvcCountry)
{
    IExecuteResult result = this.ExecuteMethodCall(this,
        ((MethodInfo)(MethodInfo.GetCurrentMethod())),
        ncCustomerID, nvcCountry);
    return ((ISingleResult<Customer>)(result.ReturnValue));
}

C# 呼叫端程式碼範例

using (var ctx = new NorthwindDataContext())
{
    // 1. 查詢：支援選填參數
    var customers = ctx.GetCustomers("ALFKI", null).ToList();

    // 2. 刪除
    int? delResult = 0;
    ctx.DeleteCustomers("TEST01", ref delResult);

    // 3. 更新
    int? updResult = 0;
    ctx.UpdateCustomers("ALFKI", "New Company", "New Address", ref updResult);
    if (updResult == -1) Console.WriteLine("更新失敗");

    // 4. 新增
    int? insResult = 0;
    ctx.InsertCustomers("NEW01", "New Corp", "Taipei", ref insResult);
    if (insResult == 0) Console.WriteLine("新增成功");
}

進階技巧與注意事項

• 回傳值 vs OUTPUT 參數：SQL Server 存儲程序可用 RETURN @val 回傳整數狀態碼，LINQ to SQL 設計器預設映射為 ISingleResult<T>.ReturnValue；若需複雜回傳建議改用 OUTPUT 參數。
• 多結果集：單一存儲程序若回傳多個 SELECT，需在方法上標註 [ResultType(typeof(Type1))], [ResultType(typeof(Type2))] 並回傳 IMultipleResults，呼叫端依序 GetResult<T1>(), GetResult<T2>() 取得。
• 並行衝突：UpdateCustomers 等寫入操作若啟用樂觀並行 (UpdateCheck=Always)，需處理 ChangeConflictException。
• 交易整合：多個存儲程序呼叫需在同一交易中，可使用 TransactionScope 或 ctx.Transaction 手動控制。

總結

LINQ to SQL 將存儲程序視為第一級公民，透過 DBML 設計器實現「資料庫端 T-SQL ↔ C# 強型別方法」的雙向同步。掌握參數映射、回傳值處理、多結果集與交易整合技巧，即可在保有資料庫端效能優勢的同時，享受編譯時期型別檢查與 IntelliSense 的開發體驗。