簡單學習Linq to sql存儲過程
在介紹Linq to sql存儲過程之前，先來介紹一下普通存儲過程，這樣更有助于大家對Linq to sql存儲過程的理解。
首先在查詢分析器運行下面的代碼來創建一個存儲過程：
- create proc sp_singleresultset
- as
- set nocount on
- select * from customers
然后打開IDE的服務器資源管理器，之前我們從表中拖動表到dbml設計視圖，這次我們從存儲過程中找到剛才創建的存儲過程，然后拖動到設計視圖。在方法面板中可以看到已經創建了一個sp_singleresultset的方法。
然后打開Northwind.designer.cs，可以找到下面的代碼：
- [Function(Name="dbo.sp_singleresultset")]
- public ISingleResult sp_singleresultset()
- {
- IExecuteResult result = this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())));
- return ((ISingleResult)(result.ReturnValue));
- }
我們可以發現，IDE為這個Linq to sql存儲過程單獨生成了返回結果集的實體定義，你可能會覺得很奇怪，IDE怎么知道這個存儲過程將會返回哪些數據那?其實，在把存儲過程拖拽入dbml設計視圖的時候，IDE就執行了類似下面的命令：
- SET FMTONLY ON;
- exec Northwind.dbo.sp_singleresultset
- SET FMTONLY OFF;
這樣就可以直接獲取Linq to sql存儲過程返回的元數據而無須執行存儲過程。
其實我們存儲過程返回的就是顧客表的數據，如果你覺得為存儲過程單獨設置結果集實體有些浪費的話可以在存儲過程的屬性窗口中調整返回類型從“自動生成的類型”到Customer，不過以后你只能通過刪除方法面板中的存儲過程，然后重新添加來還原到“自動生成的類型”。下面，我們可以寫如下的Linq to object代碼進行查詢：
var 單結果集存儲過程 =
- from c in ctx.sp_singleresultset()
- where c.CustomerID.StartsWith("A")
- select c;
在這里確實是Linq to object的，因為查詢句法不會被整句翻譯成SQL，而是從Linq to sql存儲過程存儲過程的返回對象中再去對對象進行查詢。SQL代碼如下：
- EXEC @RETURN_VALUE = [dbo].[sp_singleresultset]
- -- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []
Linq to sql存儲過程之帶參數的存儲過程
創建如下存儲過程：
- create proc [dbo].[sp_withparameter]
- @customerid nchar(5),
- @rowcount int output
- as
- set nocount on
- set @rowcount = (select count(*) from customers where
- customerid = @customerid)
使用同樣的方法生成存儲過程方法，然后使用下面的代碼進行測試：
- int? rowcount = -1;
- ctx.sp_withparameter("", ref rowcount);
- Response.Write(rowcount);
- ctx.sp_withparameter("ALFKI", ref rowcount);
- Response.Write(rowcount);
結果輸出了“01”。說明ID為“”的顧客數為0，而ID為“ALFKI”的顧客數為1。Linq to sql存儲過程存儲過程的輸出參數被封裝成了ref參數，對于C#語法來說非常合情合理。SQL代碼如下
- EXEC @RETURN_VALUE = [dbo].[sp_withparameter] @customerid = @p0,
- @rowcount = @p1 OUTPUT
- -- @p0: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) []
- -- @p1: InputOutput Int32 (Size = 0; Prec = 0; Scale = 0) [-1]
- -- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []
Linq to sql存儲過程之帶返回值的存儲過程
再來創建第三個存儲過程：
- create proc [dbo].[sp_withreturnvalue]
- @customerid nchar(5)
- as
- set nocount on
- if exists (select 1 from customers where customerid = @customerid)
- return 101
- else
- return 100
生成方法后，可以通過下面的代碼進行測試：
- Response.Write(ctx.sp_withreturnvalue(""));
- Response.Write(ctx.sp_withreturnvalue("ALFKI"));
運行后程序輸出“100101”
Linq to sql存儲過程之多結果集的存儲過程
再來創建一個多結果集的存儲過程：
- create proc [dbo].[sp_multiresultset]
- as
- set nocount on
- select * from customers
- select * from employees
找到生成的存儲過程方法：
- [Function(Name="dbo.sp_multiresultset")]
- public ISingleResult sp_multiresultset()
- {
- IExecuteResult result = this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())));
- return ((ISingleResult)(result.ReturnValue));
- }
由于現在的VS2008會把多結果集Linq to sql存儲過程識別為單結果集存儲過程(只認識第一個結果集)，我們只能對Linq to sql存儲過程方法多小動手術，修改為：
- [Function(Name="dbo.sp_multiresultset")]
- [ResultType(typeof(Customer))]
- [ResultType(typeof(Employee))]
- public IMultipleResults sp_multiresultset()
- {
- IExecuteResult result = this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())));
- return (IMultipleResults)(result.ReturnValue);
- }
然后使用下面的代碼測試：
- var 多結果集存儲過程 = ctx.sp_multiresultset();
- var Customers = 多結果集存儲過程.GetResult();
- var Employees = 多結果集存儲過程.GetResult();
- GridView1.DataSource = from emp in Employees where
- emp.FirstName.Contains("A") select emp;
- GridView1.DataBind();
- GridView2.DataSource = from c in Customers where
- c.CustomerID.StartsWith("A") select c;
- GridView2.DataBind();
使用Linq to sql存儲過程新增數據
存儲過程除了可以直接調用之外，還可以用于實體的增刪改操作。還記得在《一步一步學Linq to sql(三)：增刪改》中創建的留言簿程序嗎?下面我們就來改造這個程序，使用存儲過程而不是系統生成的SQL實現實體增刪改。首先，我們創建下面的Linq to sql存儲過程。
- 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,'')
然后，打開留言簿dbml，把Linq to sql存儲過程從服務器資源管理器拖拽到設計視圖上。右鍵點擊tbGuestBook實體類，選擇配置行為。如下圖，為插入操作選擇剛才創建的存儲過程方法，并進行參數匹配：
由于我們的Linq to sql存儲過程只接受2個參數，相應修改以下創建留言的按鈕處理事件：
- protected void btn_SendMessage_Click(object sender, EventArgs e)
- {
- tbGuestBook gb = new tbGuestBook();
- gb.UserName = tb_UserName.Text;
- gb.Message = tb_Message.Text;
- ctx.tbGuestBooks.Add(gb);
- ctx.SubmitChanges();
- SetBind();
- }
運行程序后可以發現，在提交修改的時候調用了下面的SQL：
- EXEC @RETURN_VALUE = [dbo].[sendmessage] @username = @p0, @message = @p1
- -- @p0: Input AnsiString (Size = 5; Prec = 0; Scale = 0) [zhuye]
- -- @p1: Input AnsiString (Size = 11; Prec = 0; Scale = 0) [new message]
- -- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []
使用Linq to sql存儲過程刪除數據
創建如下Linq to sql存儲過程：
- create proc delmessage
- @id uniqueidentifier
- as
- delete tbguestbook where id=@id
按照前面的步驟生成Linq to sql存儲過程方法，并為刪除操作執行這個存儲過程方法。在選擇參數的時候我們可以看到，ID分當前值和原始值，我們選擇當前值即可無須改動任何邏輯代碼，進行刪除留言操作后可以跟蹤到下面的SQL：
- EXEC @RETURN_VALUE = [dbo].[delmessage] @id = @p0
- -- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0)
- [9e3c5ee3-2575-458e-899d-4b0bf73e0849]
- -- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []
Linq to sql存儲過程之使用存儲過程更改數據
創建如下存儲過程：
- create proc replymessage
- @id uniqueidentifier,
- @reply varchar(500)
- as
- update tbguestbook set reply=@reply,isreplied=1 where id=@id
由于更新的時候并不會更新主鍵，所以我們可以為兩個參數都指定當前值。回復留言后可以跟蹤到下面的SQL：
- EXEC @RETURN_VALUE = [dbo].[replymessage] @id = @p0, @reply = @p1
- -- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0)
- [67a69d0f-a88b-4b22-8939-fed021eb1cb5]
- -- @p1: Input AnsiString (Size = 6; Prec = 0; Scale = 0) [464456]
- -- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []
假設有這樣一種應用，我們需要修改留言簿中不合法的用戶名：
- create proc modiusername
- @oldusername varchar(50),
- @newusername varchar(50)
- as
- update tbguestbook set username=@newusername where
- username = @oldusername
有個網友起名叫“admin”，我們要把所有這個名字修改為“notadmin”。那么，可以如下圖設置update操作：
然后運行下面的測試代碼：
- var messages = from gb in ctx.tbGuestBooks
- select gb;
- foreach (var gb in messages)
- {
- if(gb.UserName == "admin")
- gb.UserName = "notadmin";
- }
運行程序后能跟蹤到下面的SQL：
- SELECT [t0].[ID], [t0].[UserName], [t0].[PostTime], [t0].[Message],
- [t0].[IsReplied], [t0].[Reply]
- FROM [dbo].[tbGuestBook] AS [t0]
- EXEC @RETURN_VALUE = [dbo].[modiusername] @oldusername =
- @p0, @newusername = @p1
- -- @p0: Input AnsiString (Size = 5; Prec = 0; Scale = 0) [admin]
- -- @p1: Input AnsiString (Size = 8; Prec = 0; Scale = 0) [notadmin]
- -- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []
Linq to sql存儲過程就看到這里了，你應該能明白當前值和原始值的含義了吧。
【編輯推薦】
