學習 Linq to SQL (五)：預存程序完整應用 — 查詢、參數、多結果集、CUD 操作

1. 一般預存程序

在 DBML 設計面拖入 SP，VS 執行 SET FMTONLY ON; EXEC sp_name; SET FMTONLY OFF; 取得中繼資料，自動產生結果實體 (sp_nameResult)。可在屬性視窗將回傳類型改為現有實體 (如 Customer)。

var q = from c in ctx.sp_singleresultset() where c.CustomerID.StartsWith("A") select c;
// 為 Linq to Objects (在記憶體中篩選)，SQL 僅 EXEC sp

2. 含參數/輸出參數/回傳值

// SP: sp_withparameter @customerid nchar(5), @rowcount int output
int? rc = -1;
ctx.sp_withparameter("", ref rc); // rc=0
ctx.sp_withparameter("ALFKI", ref rc); // rc=1
// 輸出參數對應 ref 參數

// SP: sp_withreturnvalue @customerid nchar(5) -> return 100/101
int ret = ctx.sp_withreturnvalue("ALFKI"); // 101

3. 多結果集

VS 2008 僅識別第一個結果集，需手動修改 Designer.cs：

[Function(Name="dbo.sp_multiresultset")]
[ResultType(typeof(Customer))]
[ResultType(typeof(Employee))]
public IMultipleResults sp_multiresultset() {
  var result = this.ExecuteMethodCall(this, MethodInfo.GetCurrentMethod());
  return (IMultipleResults)result.ReturnValue;
}
// 使用
var multi = ctx.sp_multiresultset();
var emps = multi.GetResult<Employee>();
var custs = multi.GetResult<Customer>();

4. 以預存程序執行 CUD (建立/更新/刪除)

在 DBML 實體類別右鍵 設定行為，對應 Insert/Update/Delete 至 SP，並對應參數 (目前值/原始值)。

Insert 範例

CREATE PROC sendmessage @username varchar(50), @message varchar(500) AS
INSERT INTO tbguestbook (id,username,posttime,message,isreplied,reply) VALUES (newid(),@username,getdate(),@message,0,'')
// 參數對應：username→UserName (Current), message→Message (Current)

Delete 範例

CREATE PROC delmessage @id uniqueidentifier AS DELETE tbguestbook WHERE id=@id
// 參數對應：id→ID (Current)

Update 範例

CREATE PROC replymessage @id uniqueidentifier, @reply varchar(500) AS
UPDATE tbguestbook SET reply=@reply, isreplied=1 WHERE id=@id
// 參數對應：id→ID (Current), reply→Reply (Current)

批次更新範例

CREATE PROC modiusername @oldusername varchar(50), @newusername varchar(50) AS
UPDATE tbguestbook SET username=@newusername WHERE username=@oldusername
// 參數對應：oldusername→UserName (Original), newusername→UserName (Current)

邏輯碼僅修改實體屬性再 SubmitChanges()，自動呼叫對應 SP。

關鍵概念：目前值 vs 原始值

• Current：實體目前屬性值 (修改後)。
• Original：實體載入時的原始值 (修改前)。
• Insert 只有 Current；Delete 通常用 Current (或 Original 均可)；Update 視業務需求決定哪欄位用 Original 做 WHERE 條件。