【转】一步一步学Linq to sql（五）：存储过程
普通存儲過程
?
?????? 首先在查詢分析器運(yùn)行下面的代碼來創(chuàng)建一個存儲過程：
| create proc sp_singleresultset as set nocount on select * from customers? |
?????? 然后打開IDE的服務(wù)器資源管理器，之前我們從表中拖動表到dbml設(shè)計(jì)視圖，這次我們從存儲過程中找到剛才創(chuàng)建的存儲過程，然后拖動到設(shè)計(jì)視圖。在方法面板中可以看到已經(jīng)創(chuàng)建了一個sp_singleresultset的方法，如下圖：
??
?????? 然后打開Northwind.designer.cs，可以找到下面的代碼：
| ??? [Function(Name="dbo.sp_singleresultset")] ??? public ISingleResult<sp_singleresultsetResult> sp_singleresultset() ??? { ??????? IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); ??????? return ((ISingleResult<sp_singleresultsetResult>)(result.ReturnValue)); ??? } |
?????? 我們可以發(fā)現(xiàn)，IDE為這個存儲過程單獨(dú)生成了返回結(jié)果集的實(shí)體定義，你可能會覺得很奇怪，IDE怎么知道這個存儲過程將會返回哪些數(shù)據(jù)那？其實(shí)，在把存儲過程拖拽入dbml設(shè)計(jì)視圖的時候，IDE就執(zhí)行了類似下面的命令：
| SET FMTONLY ON; exec Northwind.dbo.sp_singleresultset SET FMTONLY OFF; |
?????? 這樣就可以直接獲取存儲過程返回的元數(shù)據(jù)而無須執(zhí)行存儲過程。
?????? 其實(shí)我們存儲過程返回的就是顧客表的數(shù)據(jù)，如果你覺得為存儲過程單獨(dú)設(shè)置結(jié)果集實(shí)體有些浪費(fèi)的話可以在存儲過程的屬性窗口中調(diào)整返回類型從“自動生成的類型”到Customer，不過以后你只能通過刪除方法面板中的存儲過程，然后重新添加來還原到“自動生成的類型”。下面，我們可以寫如下的Linq to object代碼進(jìn)行查詢：
| ??????? var 單結(jié)果集存儲過程 = ??????????? from c in ctx.sp_singleresultset() ??????????? where c.CustomerID.StartsWith("A") ??????????? select c; |
?????? 在這里確實(shí)是Linq to object的，因?yàn)椴樵兙浞ú粫徽浞g成SQL，而是從存儲過程的返回對象中再去對對象進(jìn)行查詢。SQL代碼如下：
| EXEC @RETURN_VALUE = [dbo].[sp_singleresultset] -- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) [] |
??????
帶參數(shù)的存儲過程
?
?????? 創(chuàng)建如下存儲過程：
| create proc [dbo].[sp_withparameter] @customerid nchar(5), @rowcount int output as set nocount on set @rowcount = (select count(*) from customers where customerid = @customerid) |
?????? 使用同樣的方法生成存儲過程方法，然后使用下面的代碼進(jìn)行測試：
| ??????? int? rowcount = -1; ??????? ctx.sp_withparameter("", ref rowcount); ??????? Response.Write(rowcount); ??????? ctx.sp_withparameter("ALFKI", ref rowcount); ??????? Response.Write(rowcount); |
?????? 結(jié)果輸出了“01”。說明ID為“”的顧客數(shù)為0，而ID為“ALFKI”的顧客數(shù)為1。存儲過程的輸出參數(shù)被封裝成了ref參數(shù)，對于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) [] |
?
帶返回值的存儲過程
?
?????? 再來創(chuàng)建第三個存儲過程：
| 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 |
?????? 生成方法后，可以通過下面的代碼進(jìn)行測試：
| ??????? Response.Write(ctx.sp_withreturnvalue("")); ??????? Response.Write(ctx.sp_withreturnvalue("ALFKI")); |
?????? 運(yùn)行后程序輸出“100101”
?
多結(jié)果集的存儲過程
?
?????? 再來創(chuàng)建一個多結(jié)果集的存儲過程：
| create proc [dbo].[sp_multiresultset] as set nocount on select * from customers select * from employees |
?????? 找到生成的存儲過程方法：
| [Function(Name="dbo.sp_multiresultset")] ??? public ISingleResult<sp_multiresultsetResult> sp_multiresultset() ??? { ??????? IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); ??????? return ((ISingleResult<sp_multiresultsetResult>)(result.ReturnValue)); ??? } |
?????? 由于現(xiàn)在的VS2008會把多結(jié)果集存儲過程識別為單結(jié)果集存儲過程（只認(rèn)識第一個結(jié)果集），我們只能對存儲過程方法多小動手術(shù)，修改為：
| ??? [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 多結(jié)果集存儲過程 = ctx.sp_multiresultset(); ??????? var Customers = 多結(jié)果集存儲過程.GetResult<Customer>(); ????? ??var Employees = 多結(jié)果集存儲過程.GetResult<Employee>(); ??????? 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(); |
??????
使用存儲過程新增數(shù)據(jù)
?
?????? 存儲過程除了可以直接調(diào)用之外，還可以用于實(shí)體的增刪改操作。還記得在《一步一步學(xué)Linq to sql（三）：增刪改》中創(chuàng)建的留言簿程序嗎？下面我們就來改造這個程序，使用存儲過程而不是系統(tǒng)生成的SQL實(shí)現(xiàn)實(shí)體增刪改。首先，我們創(chuàng)建下面的存儲過程
| 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，把存儲過程從服務(wù)器資源管理器拖拽到設(shè)計(jì)視圖上。右鍵點(diǎn)擊tbGuestBook實(shí)體類，選擇配置行為。如下圖，為插入操作選擇剛才創(chuàng)建的存儲過程方法，并進(jìn)行參數(shù)匹配：
?
?????? 由于我們的存儲過程只接受2個參數(shù)，相應(yīng)修改以下創(chuàng)建留言的按鈕處理事件：
| ??? 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(); ??? } |
?????? 運(yùn)行程序后可以發(fā)現(xiàn)，在提交修改的時候調(diào)用了下面的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) [] |
?
使用存儲過程刪除數(shù)據(jù)
?
?????? 創(chuàng)建如下存儲過程：
| create proc delmessage @id uniqueidentifier as delete tbguestbook where id=@id |
?????? 按照前面的步驟生成存儲過程方法，并為刪除操作執(zhí)行這個存儲過程方法。在選擇參數(shù)的時候我們可以看到，ID分當(dāng)前值和原始值，我們選擇當(dāng)前值即可，如下圖：
?
?????? 無須改動任何邏輯代碼，進(jìn)行刪除留言操作后可以跟蹤到下面的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) [] |
?
使用存儲過程更改數(shù)據(jù)
?
?????? 創(chuàng)建如下存儲過程：
| create proc replymessage @id uniqueidentifier, @reply varchar(500) as update tbguestbook set reply=@reply,isreplied=1 where id=@id |
?????? 由于更新的時候并不會更新主鍵，所以我們可以為兩個參數(shù)都指定當(dāng)前值。回復(fù)留言后可以跟蹤到下面的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) [] |
?????? 假設(shè)有這樣一種應(yīng)用，我們需要修改留言簿中不合法的用戶名：
| create proc modiusername @oldusername varchar(50), @newusername varchar(50) as update tbguestbook set username=@newusername where username = @oldusername |
?????? 有個網(wǎng)友起名叫“admin”，我們要把所有這個名字修改為“notadmin”。那么，可以如下圖設(shè)置update操作：
?
?????? 然后運(yùn)行下面的測試代碼：
| ??????? var messages = from gb in ctx.tbGuestBooks ?????????????????????? select gb; ??????? foreach (var gb in messages) ??????? { ??????????? if(gb.UserName == "admin") ??????????????? gb.UserName = "notadmin"; ??????? } |
?????? 運(yùn)行程序后能跟蹤到下面的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) [] |
?????? 到這里，你應(yīng)該能明白當(dāng)前值和原始值的含義了吧。
轉(zhuǎn)載于:https://www.cnblogs.com/h20064528/archive/2012/09/26/2704474.html
總結(jié)
以上是生活随笔為你收集整理的【转】一步一步学Linq to sql（五）：存储过程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 转：Jeff Dean的Stanford
- 下一篇： 虚拟化系列-Windows server