iT邦幫忙

DAY 11
3

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

OpenAccess ORM for Oracle 實戰演練 Day 11

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

結果如下:


上一篇
OpenAccess ORM for Oracle 實戰演練 Day 10
下一篇
OpenAccess ORM for Oracle 實戰演練 Day 12
系列文
OpenAccess ORM for Oracle 實戰演練26

尚未有邦友留言

立即登入留言