OpenAccess快速入門11
OpenAccess 快速入門將協助我們熟悉 OpenAccess,第十一篇將整合前幾篇的內容,聊聊如何建立新資料的同時,也去更新既有的關聯資料。
前一篇(OpenAccess快速入門10)說明了如何透過 OpenAccess ORM 更新資料庫既有資料,更之前兩篇文章則說明了如何建立新資料和關聯資料,現在我們要做一個整合的範例,也就是新增資料的同時,還更新關聯的既有資料。
真實世界中,最常見的就是建立訂單的同時,更新客戶的連絡資料,所以我們這次的範例,就是建立一筆訂單資料,同時更新一筆客戶的連絡資料。
我們事先準備好一筆客戶資料如下,您可以自行建立一筆新資料或者用 Customers 中的既有資料做練習,但請記得您所要使用的客戶資料之 CustomerId:
我們在 Model 專案中,加入新的 OrdersMgmt.vb 類別,並加入幾個方法:
Public Class OrdersMgmt
Private cxt As SecondModel
Public Sub New()
cxt = New SecondModel()
End Sub
Public Function InitialOrderEntity() As Orders
'注意,此方法其實是假裝前端收集資料的行為
Dim order = New Orders()
With order
.ORDERDATE = DateTime.Now
.FREIGHT = 38.5D
.SHIPCITY = "第九行星"
.SHIPPOSTALCODE = "22099"
End With
Return order
End Function
Public Function InitialCustomerEntity() As Customers
'注意,此方法其實是假裝前端收集資料的行為
Dim customer As Object = GetCustomerLeoShih()
customer.COUNTRY = "銀河太陽系"
customer.CITY = "第九行星"
customer.ADDRESS = "東區第一行政區"
Return customer
End Function
Public Function GetCustomerLeoShih() As Customers
Dim customer = (From x In cxt.Customers
Where x.CUSTOMERID = "LEOS"
Select x).First()
Return customer
End Function
Public Function CreateOrder(order As Orders, customer As Customers) As Orders
order.Customers = customer
cxt.Add(order)
cxt.SaveChanges()
Return order
End Function
End Class
請注意,上述程式碼有兩個函式:InitialCustomerEntity()、InitialOrderEntity(),是用來模擬收集使用者在 UI 所輸入的資料,然後轉成 Customers 和 Orders 實體,完全是因為方便測試和練習。 另外,GetCustomerLeoShih() 函式則是透過 LINQ 取得我們預先設計好的顧客資料,練習時請自行把 CustomerId 改成您所使用的顧客編號。
CreateOrder() 函式是這次練習的核心,但程式碼超少,因為對於這個函式而言,它的責任就是把傳入的 order 和 customer 建立連結並加入物件容器後,調用 SaveChanges() 函式,讓 OpenAccess ORM 幫我們把新建一筆訂單和修改顧客連絡資料回寫在底層資料庫。
我們撰寫單元測試來驗證上述的程式碼:
<TestMethod()> _
Public Sub CreateOrderTest()
Dim target As OrdersMgmt = New OrdersMgmt()
Dim order As Orders = target.InitialOrderEntity()
Dim customer As Customers = target.InitialCustomerEntity()
Dim expected As Orders = Nothing
Dim actual As Orders
actual = target.CreateOrder(order, customer)
Assert.IsNotNull(actual.ORDERID)
Assert.IsNotNull(actual.CUSTOMERID)
End Sub
這次的單元測試,驗證的目標很簡單,就是 OpenAccess 是否有幫我們正確的寫入一筆訂單資料,並回填訂單編號,以及有沒有把顧客編號帶回訂單的 CustomerId 屬性。測試通過後,我們在頁面上展現這個效果。
請在 Web 專案中,加入 CreateOrderAndModifyCustomer.aspx 頁面:
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="CreateOrderAndModifyCustomer.aspx.vb" Inherits="OpenAccessWebApp01.CreateOrderAndModifyCustomer" %>
<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:Button ID="btnQuery" runat="server" Text="查詢顧客 Leo Shih 的地址" />
<br />
<asp:Button ID="btnCreate" runat="server" Text="建立訂單資料" />
<br />
顧客編號:<asp:Label ID="lblCustomerId" runat="server"></asp:Label>
<br />
連絡人:<asp:Label ID="lblContactName" runat="server"></asp:Label>
<br />
國家:<asp:Label ID="lblCountry" runat="server"></asp:Label>
<br />
縣市:<asp:Label ID="lblCity" runat="server"></asp:Label>
<br />
地址:<asp:Label ID="lblAddress" runat="server"></asp:Label>
<br />
訂單編號:<asp:Label ID="lblOrderId" runat="server"></asp:Label>
<br />
運費:<asp:Label ID="lblFreight" runat="server"></asp:Label>
</div>
</form>
CreateOrderAndModifyCustomer.aspx.vb
Imports OpenAccessWebApp01Model
Public Class CreateOrderAndModifyCustomer
Inherits System.Web.UI.Page
Protected Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
Dim bo As OrdersMgmt = New OrdersMgmt()
Dim order As Orders = bo.InitialOrderEntity()
Dim customer As Customers = bo.InitialCustomerEntity()
order = bo.CreateOrder(order, customer)
lblOrderId.Text = order.ORDERID
lblFreight.Text = order.FREIGHT.ToString()
DisplayCustomerInfo()
End Sub
Protected Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
DisplayCustomerInfo()
End Sub
Private Sub DisplayCustomerInfo()
Dim bo As OrdersMgmt = New OrdersMgmt()
Dim customer As Customers = bo.GetCustomerLeoShih()
lblCustomerId.Text = customer.CUSTOMERID
lblContactName.Text = customer.CONTACTNAME
lblCountry.Text = customer.COUNTRY
lblCity.Text = customer.CITY
lblAddress.Text = customer.ADDRESS
End Sub
End Class
執行結果:
PS. 上面這張圖是 gif 動畫,但是在 Chrome 好像不會重覆播放,所以再留個連結,才不用重整畫面:http://i.minus.com/ig4uMx9k6Ob3g.Gif