10-2. 返回輸出參數問題想獲取存儲過程里的一個或多個輸出參數的值解決方案假設我們有一個像Figure 10-1所示的，計程車輛與租金收入的模型Figure 10-1.計程車輛與租金收入的模型我們想知道在指定日期里，收入了幾筆租金和金額, 以及車輛的租憑情況. 存儲過程Listing 10-7 就...
10-2. 返回輸出參數
問題
想獲取存儲過程里的一個或多個輸出參數的值
解決方案
假設我們有一個像Figure 10-1所示的，計程車輛與租金收入的模型
Figure 10-1.計程車輛與租金收入的模型
我們想知道在指定日期里，收入了幾筆租金和金額, 以及車輛的租憑情況. 存儲過程Listing 10-7 就是獲取這些信息的.
Listing 10-7. A Stored Procedure for the Vehicles Rented, the Number of Rentals, and the Total Rental Payments
create procedure [chapter10].[GetVehiclesWithRentals]
(@date date,
@TotalRentals int output,
@TotalPayments decimal(18,2) output)
as
begin
select @TotalRentals = COUNT(*), @TotalPayments = SUM(payment)
from chapter10.Rental
where RentalDate = @date
select distinct v.*
from chapter10.Vehicle v join chapter10.Rental r
on v.VehicleId = r.VehicleId
end
為了在模型里使用空上存儲過程，執行以下操作：
1. 右擊模型的設計視圖,選擇“從資料庫更新模型”. 在對話框里
,選擇存儲過程GetVehiclesWithRentals. 點擊“完成”添加存儲過程到模型里。
2. 右擊模型的設計視圖, 選擇“新增”➤“函數導入”. 在“存儲過程/函數名稱（P）：”下拉框里選擇
GetVehiclesWithRentals,在“函數導入名稱（F）：”里輸入GetVehiclesWithRentals (這個就是在模型里生成的方法名稱)。在“返回以下內容的集合”里勾選“實體”，並從下拉列里選擇Vehicle
.點擊“確定”
3.下麵的Listing 10-8 就是使用GetVehiclesWithRentals的方法.
Listing 10-8.通過GetVehiclesWithRentals() 方法使用模型里的GetVehiclesWithRentals存儲過程
staticvoid Main(string[] args)
{
using (var context = newEFRecipesEntities())
{
context.Database.ExecuteSqlCommand("delete from chapter10.Rental");
context.Database.ExecuteSqlCommand("delete from chapter10.Vehicle");
var car1 = newVehicle { Manufacturer = "Toyata", Model = "Camry", Year = 2013 };
var car2 = newVehicle { Manufacturer = "Chevrolet", Model = "Corvette", Year = 2013 };
var r1 = newRental { Vehicle = car1, RentalDate = DateTime.Parse("5/7/2013"), Payment = 59.95m };
var r2 = newRental { Vehicle = car2, RentalDate = DateTime.Parse("5/7/2013"), Payment = 139.95m };
car1.Rentals.Add(r1);
car2.Rentals.Add(r2);
context.Vehicles.Add(car1);
context.Vehicles.Add(car2);
context.SaveChanges();
}
using (var context = newEFRecipesEntities())
{
string reportDate = "5/7/2013";
var totalRentals = newObjectParameter("TotalRentals", typeof(int));
var totalPayments = newObjectParameter("TotalPayments", typeof(decimal));
var vehicles = context.GetVehiclesWithRentals(DateTime.Parse(reportDate),
totalRentals, totalPayments);
Console.WriteLine("Retal Activity for {0}", reportDate);
Console.WriteLine("Vehicles Rented");
foreach (var vehicle in vehicles)
{
Console.WriteLine("{0} {1} {2}", vehicle.Year, vehicle.Manufacturer, vehicle.Model);
}
Console.WriteLine("TotalRentals:{0}", (int)totalRentals.Value);
Console.WriteLine("Total Payments:{0}", ((decimal)totalPayments.Value).ToString("C"));
Console.WriteLine("\npress any key to exit...");
Console.ReadKey();
}
}
以下Listing 10-8是控制台輸出結果:
===================================================================
Rental Activity for 5/7/2013
Vehicles Rented
2013 Toyota Camry
2013 Chevrolet Corvette
Total Rentals: 2
Total Payments: $200.00
===========================================
它是如何工作的？
第2步操作會使用GetVehiclesWithRentals存儲過程更新模型 ,我們也更新了概念模型，讓存儲過程暴露在GetVehiclesWithRentals()上，該方法與存儲過程有著類似的簽名.
有一點請註意：當調用GetVehiclesWithRentals()方法後，它返回的實體集必須先實例化，輸出參數才可以用。這跟ADO.NET里的DataReader相似，DataReader
