次の方法で共有

WPF-EDM: Binding to Stored Procedures

  • [アーティクル]

Using Stored Procedures to Convert Database Data

When building WPF database applications, you might not want to display the raw data retrieved from data tables directly. Instead, you want to convert the raw data to make it more meaningful to display. You could convert the data in your WPF applications, or you could implement the converting logic in a stored procedure and bind to it in your application. Converting data using stored procedures provides benefits like:

· Modular programming

Stored procedures are stored together with data tables in the database. They could be shared by multiple applications and could be modified independently, which makes the converting logic more maintainable.

Also, stored procedures simplify application programming. Stored procedures might operate on multiple data tables. But application programmers don’t need to care about these details, they just need to concern the inputs and outputs.

· Faster execution

Stored procedures could be parsed and optimized when they are first executed, and a complied version of the stored procedures remains in memory cache for later use, which results in much faster execution.

Combining stored procedures with data binding and rich tooling support of visual studio 2010, you could easily build applications to transform and display database data. As an example, we will implement a master-detail view application to list total count of all products that a customer orders.

 

Sample Application - Data Model

The data model used by the sample application is shown like below. Four data tables are used here. Each customer could have multiple orders, and each order could contain multiple products.

clip_image002

In this application, we don’t want to display all details of a customer’s orders. Instead, we just want to display which products a customer buys, and for each product, what quantity does the customer buys in total. For this, we need to create a stored procedure, and store it in the database. Suppose the name is 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

 

Sample Application – Step by Step

1. Open Visual Studio 2010, create a new C# or VB WPF application named “BindToSP”.

2. Add a new EDM project item (name it Northwind.edmx). In the wizard, choose “Generate from database”, select table “Customer” and stored procedure “CustOrderHist”, and click “Finish”.

3. In the “Model Browser” window of the EDM designer, double click “CustOrderHist” to open the “Add Function Import” dialog

4. In the dialog, click “Get Column Information” to get output columns of the stored procedure, and then click “Create New Complex Type” to generate a class (say, CustOrderHist_Result) corresponding to the returned columns.

clip_image004

Open Northwind.Designer.cs, you will see a new function is added to NorthwindEntities class:

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. Open MainWindow.xaml, use “Data -> Show Data Sources” to open the Data Source Window, add a new generic object data source, and choose the complex type generated at the step above.

clip_image006

The Data Source Window will be like below. Both Customers and CustOrderHist_Result are bound to DataGrid by default.

clip_image008

6. Drag drop Customers and CustOrderHist_Result from the Data Source Window to MainWindow.xaml designer.

Two CollectionViewSource instances will be added to the window’s resources, one for Customer and one for CustOrderHist_Result:

    <Window.Resources>

        <CollectionViewSource x:Key="customersViewSource" d:DesignSource="{d:DesignInstance my:Customer, CreateList=True}" />

        <CollectionViewSource x:Key="custOrderHist_ResultViewSource" d:DesignSource="{d:DesignInstance my:CustOrderHist_Result, CreateList=True}" />

    </Window.Resources>

 

And two DataGrid are created, one bound to “customerViewSource”, and the other bound to “custOrderHist_ResultViewSource”.

Also, related procedure codes are generated to load data into the CollectionViewSource instances:

C#

private System.Data.Objects.ObjectQuery<Customer> GetCustomersQuery(NorthwindEntities northwindEntities)

    {

    // Auto generated code

       System.Data.Objects.ObjectQuery<BindToSP.Customer> customersQuery = northwindEntities.Customers;

       // Returns an ObjectQuery.

       return customersQuery;

    }

 

    private void Window_Loaded(object sender, RoutedEventArgs e)

    {

    BindToSP.NorthwindEntities northwindEntities = new BindToSP.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<BindToSP.Customer> customersQuery = this.GetCustomersQuery(northwindEntities);

customersViewSource.Source =

       customersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly);

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]

    }

 

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. Add a hander to the customer DataGrid’s SelectionChanged event, modify and add the code below:

C#

private void Window_Loaded(object sender, RoutedEventArgs e)

   {

   this.northwindEntities = new BindToSP.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<BindToSP.Customer> customersQuery = this.GetCustomersQuery(northwindEntities);

customersViewSource.Source =

customersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly);

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 = 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. Run the application, select any customer in the customers DataGrid, the custOrderHist DataGrid will refresh to display product details of the selected customer:

clip_image010

 

Conclusion

You could notice that although four tables are involved in the scenario, only one Customer table is imported in the EDM. The stored procedure could be authored by another specialized database programmer at the database side, and the application programmer even does not have to be aware of all other three tables! This greatly reduces the complexity of the application and makes it more maintainable.

 

That is all. Enjoy it!

Comments

  • Anonymous
    March 05, 2011
    Hi, your solution was very helpful. I have simplified it a bit. orderCollection is not needed and ustomersDataGrid_SelectionChanged is simplified:        private void customersDataGrid_SelectionChanged(object sender,SelectionChangedEventArgs e)        {            custOrderHist_ResultDataGrid.ItemsSource = northwindEntities.CustOrderHist((this.customersDataGrid.SelectedItem as Customer).CustomerID);        } BEST REGARDS