[原文作者]:Gavin Fu
[原文链接]:WPF-EDM: Binding to Stored Procedures
使用存储过程转换数据库数据
在建立WPF 数据库应用程序时,你可能不希望直接显示从数据表中得到的原始数据。相反,你希望将原始数据转换成更有意义的信息来显示。你可以在你的WPF应用程序中转换数据,或者你可以在存储过程中实现转换的逻辑并将其绑定到你的应用程序中。使用存储过程转换数据提供了以下几点好处:
- 模块化编程
存储过程和数据表同时存储在数据库中。他们能被多个应用程序共享,而且可以被独立修改,这使得数据转换逻辑更容易维护。
同时,存储过程可以简化应用程序的编写。存储过程可能会对多个数据表进行操作。 但编写应用程序的程序员们并不需要关心这些细节,他们只需关注输入和输出。
- 更快地执行
在存储过程第一次执行时,它会被解析和优化,并且编译好的存储过程会驻留在内存缓存中供以后使用,这样执行起来会快得多。
存储过程结合数据绑定以及Visual Studio 2010丰富的工具支持,你可以轻松地建立应用程序来传递并显示数据库数据。我们会实现一个列出客户订购的所有产品总数的主从信息视图应用程序作为示例。
示例应用程序 – 数据模型
示例应用程序所使用的数据模型如下所示。在这里会用到四个数据表。每个客户可以有多个订单,每个订单会包含多个产品。
在这个应用程序中,我们不想显示客户订单的所有详细信息。相反,我们只想显示客户所购买的产品,以及每样产品客户购买的总数。 为此,我们要创建存储过程,并将其存储在数据库中。 假设存储过程的名称是CustOrderHist:
ALTER PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
示例应用程序 – 存储过程绑定步骤
1. 打开Visual Studio 2010,创建新的C#或VB WPF应用程序,命名为“BindToSP”。
2. 添加新的EDM项 (命名为:Northwind.edmx)。 在向导中选择“从数据库生成”,选择“Customer”表和存储过程“CustOrderHist”,然后单击“完成”。
3. 在EDM设计器的“实体模型浏览器”窗口中,双击“CustOrderHist”打开“添加函数导入”对话框。
4. 在对话框中,点击“获取列信息”获取存储过程输出的列,然后点击“创建新的复杂类型”生成一个类(命名为CustOrderHist_Result)这个类相当于返回的列。
打开 Northwind.Designer.cs,你会看到一个新的函数加入到NorthwindEntities类中:
C#
public ObjectResult<CustOrderHist_Result> CustOrderHist(global::System.String customerID)
VB
public Function CustOrderHist(customerID As Global.System.String) As ObjectResult(Of CustOrderHist_Result)
5. 打开 MainWindow.xaml,在主菜单栏使用"数据-> 显示数据源"打开数据源窗口,添加一个新的对象数据源,并在“数据源配置向导”对话框中选择在上面的步骤生成的复杂类型CustOrderHist_Result。
数据源窗口如下图所示。Customers和CustOrderHist_Result默认绑定到DataGrid控件上。
6. 从数据源窗口拖放Customers和CustOrderHist_Result到MainWindow.xaml设计器中。
两个CollectionViewSource实例将被添加到窗口的资源中,一个是Customer的,另一个是 CustOrderHist_Result的:
<Window.Resources>
<CollectionViewSource x:Key="custOrderHist_ResultViewSource" d:DesignSource="{d:DesignInstance my:CustOrderHist_Result, CreateList=True}" />
<CollectionViewSource x:Key="customersViewSource" d:DesignSource="{d:DesignInstance my:Customer, CreateList=True}" />
</Window.Resources>
两个DataGrid会被创建,一个绑定到"customerViewSource"和另一个绑定到“custOrderHist_ResultViewSource”。
此外,加载数据到CollectionViewSource实例的相关代码也会自动生成:
C#
private System.Data.Objects.ObjectQuery<Customer> GetCustomersQuery(NorthwindEntities northwindEntities)
{
// Auto generated code
System.Data.Objects.ObjectQuery<BindToSP1.Customer> customersQuery = northwindEntities.Customers;
// Returns an ObjectQuery.
return customersQuery;
}
private void Window_Loaded(obj
ect sender, RoutedEventArgs e)
{
System.Windows.Data.CollectionViewSource custOrderHist_ResultViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("custOrderHist_ResultViewSource")));
// Load data by setting the CollectionViewSource.Source property:
// custOrderHist_ResultViewSource.Source = [generic data source]
BindToSP1.NorthwindEntities northwindEntities = new BindToSP1.NorthwindEntities();
// Load data into Customers. You can modify this code as needed.
System.Windows.Data.CollectionViewSource customersViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("customersViewSource")));
System.Data.Objects.ObjectQuery<BindToSP1.Customer> customersQuery = this.GetCustomersQuery(northwindEntities);
customersViewSource.Source = customersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly);
}
VB
Private Function GetCustomersQuery(ByVal NorthwindEntities As BindingToSP.NorthwindEntities) As System.Data.Objects.ObjectQuery(Of BindingToSP.Customer)
Dim CustomersQuery As System.Data.Objects.ObjectQuery(Of BindingToSP.Customer) = NorthwindEntities.Customers
‘Returns an ObjectQuery.
Return CustomersQuery
End Function
Private Sub Window_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.Loaded
Dim NorthwindEntities As BindingToSP.NorthwindEntities = New BindingToSP.NorthwindEntities()
‘Load data into Customers. You can modify this code as needed.
Dim CustomersViewSource As System.Windows.Data.CollectionViewSource = CType(Me.FindResource("CustomersViewSource"), System.Windows.Data.CollectionViewSource)
Dim CustomersQuery As System.Data.Objects.ObjectQuery(Of BindingToSP.Customer) = Me.GetCustomersQuery(NorthwindEntities)
CustomersViewSource.Source = CustomersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly)
Dim CustOrderHist_ResultViewSource As System.Windows.Data.CollectionViewSource = CType(Me.FindResource("CustOrderHist_ResultViewSource"), System.Windows.Data.CollectionViewSource)
‘Load data by setting the CollectionViewSource.Source property:
‘CustOrderHist_ResultViewSource.Source = [generic data source]
End Sub
7. 在custormer DataGrid控件的SelectionChanged事件加入一些代码,修改及添加的代码如高亮处所示:
C#
private void Window_Loaded(object sender, RoutedEventArgs e)
{
this.northwindEntities = new BindToSP1.NorthwindEntities();
System.Windows.Data.CollectionViewSource custOrderHist_ResultViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("custOrderHist_ResultViewSource")));
// Load data by setting the CollectionViewSource.Source property:
// custOrderHist_ResultViewSource.Source = [generic data source]
BindToSP1.NorthwindEntities northwindEntities = new BindToSP1.NorthwindEntities();
// Load data into Customers. You can modify this code as needed.
System.Windows.Data.CollectionViewSource customersViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("customersViewSource")));
System.Data.Objects.ObjectQuery<BindToSP1.Customer> customersQuery = this.GetCustomersQuery(northwindEntities);
customersViewSource.Source = customersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly);
// Load data by setting the CollectionViewSource.Source property:
custOrderHist_ResultViewSource.Source = this.orderCollection;
}
private ObservableCollection<CustOrderHist_Result> orderCollection = new ObservableCollection<CustOrderHist_Result>();
private NorthwindEntities northwindEntities;
// This is the added handler of customer DataGrid’s SelectionChanged event
private void customersDataGrid_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
Customer selectedCustomer = this.customersDataGrid.SelectedItem as Customer;
if (selectedCustomer != null)
{
orderCollection.Clear();
foreach (CustOrderHist_Result result in this.northwindEntities.CustOrderHist(selectedCustomer.CustomerID))
{
orderCollection.Add(result);
}
}
}
VB
Private Sub Window_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.Loaded
Me.NorthwindEntities = New BindingToSP.NorthwindEntities()
‘Load data into Customers. You can modify this code as needed.
Dim CustomersViewSource As System.Windows.Data.CollectionViewSource = CType(Me.FindResource("CustomersViewSource"), System.Windows.Data.CollectionViewSource)
Dim CustomersQuery As System.Data.Objects.ObjectQuery(Of BindingToSP.Customer) = Me.GetCustomersQuery(NorthwindEntities)
CustomersViewSource.Source = CustomersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly)
Dim CustOrderHist_ResultViewSource As System.Windows.Data.CollectionViewSource = CType(Me.FindResource("CustOrderHist_ResultViewSource"), System.Windows.Data.CollectionViewSource)
‘Load data by setting the CollectionViewSource.Source property:
CustOrderHist_ResultViewSource.Source = Me.orderCollection
End Sub
Private orderCollection As ObservableCollection(Of CustOrderHist_Result) = New ObservableCollection(Of CustOrderHist_Result)
Private NorthwindEntities As NorthwindEntities
‘This is the added handler of customer DataGrid’s SelectionChanged event
Private Sub CustomersDataGrid_SelectionChanged(ByVal sender As System.Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs)
Dim selectedCustomer As Customer = CType(Me.CustomersDataGrid.SelectedItem, Customer)
If selectedCustomer IsNot Nothing Then
orderCollection.Clear()
For Each result As CustOrderHist_Result In Me.NorthwindEntities.CustOrderHist(selectedCustomer.CustomerID)
orderCollection.Add(result)
Next
End If
End Sub
8. 运行该应用程序,选择custormer数据表中的任意客户,custOrderHist数据表会刷新并显示选中客户所定购产品的详细信息:
结论
你可能注意到尽管四个表都包含在之前的步骤中,但只有Customer表被导入到EDM中。存储过程可以由专门的数据库程序员在数据库上创建,这样应用程序的程序员甚至不会注意到有另外的三个表!这大大减少了应用程序的复杂性,并使它更容易维护。
就是这些了。享受它吧!





