10-3. 返回結果是一個標量值問題想取得存儲過程返回的一個標量值.解決方案假設我們有如Figure 10-2所示的ATM機和ATM機取款記錄的模型Figure 10-2. 一個ATM機和ATM機取款記錄的模型我們想要用一個存儲過程在指定機器和日期內返回所有的取款總額Listing 10-9 是這個...
10-3. 返回結果是一個標量值
問題
想取得存儲過程返回的一個標量值.
解決方案
假設我們有如Figure 10-2所示的ATM機和ATM機取款記錄的模型
Figure 10-2. 一個ATM機和ATM機取款記錄的模型
我們想要用一個存儲過程在指定機器和日期內返回所有的取款總額
Listing 10-9 是這個存儲過程的代碼
Listing 10-9. GetWithdrawals存儲過程，獲取在指定機器和日期內所有的取款總額
create procedure [Chapter10].[GetWithdrawals]
(@ATMId int, @WithdrawalDate date)
as
begin
select SUM(amount) TotalWithdrawals
from Chapter10.ATMWithdrawal
where ATMId = @ATMId and [date] = @WithdrawalDate
end
接下來就是調用這個存儲過程的方法：
1. 右擊模型的設計視圖,選擇“從資料庫更新模型”.在對話框中“存儲過程和函數”下勾選Chapter10/
GetWithdrawals.點擊“完成”.
2.（ 譯註：我的環境是win10+vs2013+ef6.1.3,是不需要這步的，第1步已經把這步也完成了）右擊模型的設計視圖, 選擇“新增“ ➤ 函數導入. 從“存儲過程/函數名稱”下拉框中選擇GetWithdrawals. 在“函數導入名稱“文本框中輸入：GetWithdrawals. 這個就是在模型中的方法名稱.在“返回以下內容的集合“里勾選“標量”，在下拉框里選擇Decimal.單擊“確定”.
3.接下來用 Listing 10-10所示代碼使用這個GetWithdrawals 存儲過程
Listing 10-10. 用模型GetWithdrawals()方法查詢存儲過程 GetWithdrawals
static void Main(string[] args)
{
DateTime today = DateTime.Parse("5/7/2013");
DateTime yesterday = DateTime.Parse("5/6/2013");
using (var context=new EFRecipesEntities())
{
context.Database.ExecuteSqlCommand("delete from chapter10.ATMWithdrawal");
context.Database.ExecuteSqlCommand("delete from chapter10.ATMMachine");
var atm = new ATMMachine { ATMId = 17, Location = "12th and Main" };
atm.ATMWithdrawals.Add(new ATMWithdrawal{Amount= 20.00m,Date=today});
atm.ATMWithdrawals.Add(new ATMWithdrawal { Amount = 100.00M, Date = today });
atm.ATMWithdrawals.Add(new ATMWithdrawal { Amount = 75.00M, Date = yesterday });
atm.ATMWithdrawals.Add(new ATMWithdrawal { Amount = 50.00M, Date = today });
context.ATMMachines.Add(atm);
context.SaveChanges();
}
using (var context=new EFRecipesEntities())
{
var forToday = context.GetWithdrawals(17, today).FirstOrDefault();
var forYesterday = context.GetWithdrawals(17, yesterday).FirstOrDefault();
var atm = context.ATMMachines.Where(o => o.ATMId == 17).FirstOrDefault();
Console.WriteLine("ATMWithdrawals for ATM at {0} at {1}",atm.ATMId,atm.Location);
Console.WriteLine("\t{0} Total Withdrawn={1}",yesterday.ToShortDateString(),forYesterday.Value.ToString("C"));
Console.WriteLine("\t{0} Total Withdrawn={1}",today.ToShortDateString(),forToday.Value.ToString("C"));
}
Console.WriteLine("\npress any key to exit...");
Console.Read();
}
輸出結果如下麵的 Listing 10-10:
==============================================================
ATM Withdrawals for ATM at 17 at 12th and Main
5/6/2013 Total Withdrawn = $75.00
5/7/2013 Total Withdrawn = $170.00
==============================================================
它是如何工作的？
註意：EF會把從存儲過程取回的標量值認為是一個集合.在我們的例子里,我們的存儲過程返回一個單一的decimal值，我們用FirstOrDefault()方法把它從集合中提取出來
