OpenAccess ORM for Oracle 實戰演練 Day 11

DAY 11

OpenAccess ORM for Oracle 實戰演練系列第 7 篇

oracle vb linq orm openaccess

smartleos

2013-10-03 10:39:06

1978 瀏覽

分享至

OpenAccess快速入門07
OpenAccess 快速入門將協助我們熟悉 OpenAccess，第七篇我們將延續前兩篇關聯查詢的內容，繼續說明常見的關聯查詢方式。
在快速入門 05 中，我們從 Employees 出發，取得關聯的 Orders 資料，現在反過來，從 Orders 實體出發，取得關聯的 Employees 資料。一樣在 VerifyRLinq.vb 中，加入新的 GetEmployeeByOrder 函式：

Public Function GetEmployeeByOrder() As IList(Of OrderSummary)
    Dim orderData = From x In cxt.Orders
                  Where x.CUSTOMERID = "MAGAA"
                  Select New OrderSummary() With {.OrderId = x.ORDERID.ToString(),
                                                  .CustomerId = x.CUSTOMERID,
                                                  .EmployeeId = x.Employees.EMPLOYEEID,
                                                  .EmployeeName = GetFullName(x.Employees.FIRSTNAME, x.Employees.LASTNAME)
                                                 }
    Return orderData.ToList()
End Function

上述 LINQ，我們從 Orders 出發，為方便測試，所以直接 HardCode 一個顧客編號 MAGAA，然後取回一個 List(Of OrderSummay)，把這個顧客編號的所有訂單相關聯的員工資料整理出來。

接下來，在 OpenAccessWebApp01 專案中，我們加入一個新頁面 DisplayEmployeeFilterByOrders.aspx，很簡單拉一個 GridView，然後在 Page_Load 中呼叫上述的函式，將取回的資料繫結、顯示：

DisplayEmployeeFilterByOrders.aspx

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="DisplayEmployeeFilterByOrders.aspx.vb" Inherits="OpenAccessWebApp01.DisplayEmployeeFilterByOrders" %>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>


    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        </div>
    </form>

DisplayEmployeeFilterByOrders.aspx.vb

Imports OpenAccessWebApp01Model

Public Class DisplayEmployeeFilterByOrders
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim obj As New VerifyRLinq()
        Dim result = obj.GetEmployeeByOrder()
        GridView1.DataSource = result
        GridView1.DataBind()
    End Sub

End Class

同場加映：使用 Join 取回未設定關聯的實體
實務上，資料表之間可能根本沒有設定關聯，在這種情況下，該如何執行關聯式查詢？這個議題和 ORM 架構無關，當然就和 OpenAccess 無關，不過實務上這種狀況很多，所以來個同場加映，透過 LINQ Join 的方式，查詢沒有設定關聯之兩個實體資料。

為方便大家了解，一樣以 Employees 和 Orders 實體為例，但我們不要透過導覽屬性查詢，改用 Join 的方式處理。在 VerifyRLinq 中加入 GetEmployeeByOrderUsingJoin 函式：

Public Function GetEmployeeByOrderUsingJoin() As IList(Of OrderSummary)
    Dim orderData = From x In cxt.Orders
                    Join e In cxt.Employees On
                    x.EMPLOYEEID Equals e.EMPLOYEEID
                    Where x.CUSTOMERID = "MAGAA"
                    Select New OrderSummary() With {.OrderId = x.ORDERID.ToString(),
                                                    .CustomerId = x.CUSTOMERID,
                                                    .EmployeeId = x.Employees.EMPLOYEEID,
                                                    .EmployeeName = GetFullName(x.Employees.FIRSTNAME, x.Employees.LASTNAME)
                                                   }
    Return orderData.ToList()
End Function

DisplayEmployeeFilterByOrders.aspx

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="DisplayEmployeeFilterByOrders.aspx.vb" Inherits="OpenAccessWebApp01.DisplayEmployeeFilterByOrders" %>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>


    <form id="form1" runat="server">
        <div>
            使用導覽屬性：<br />
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        </div>
        <br />
        <div>
            使用 Join 語法：<br />
            <asp:GridView ID="GridView2" runat="server"></asp:GridView>
        </div>
    </form>

DisplayEmployeeFilterByOrders.aspx.vb

Imports OpenAccessWebApp01Model

Public Class DisplayEmployeeFilterByOrders
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim obj As New VerifyRLinq()
        BindGridView(obj.GetEmployeeByOrder(), GridView1)
        BindGridView(obj.GetEmployeeByOrderUsingJoin(), GridView2)
    End Sub

    Private Shared Sub BindGridView(ByVal src As IList(Of OrderSummary), ByVal gv As GridView)
        gv.DataSource = src
        gv.DataBind()
    End Sub
End Class

結果如下：