[食譜好菜] 用 Dapper 取得一對一關係、一對多關係及多型資料結構的資料都只需要一次 Query

  • 7012
  • 0
  • C#
  • 2022-07-19

關聯式資料庫的資料都是以表格型式呈現為主,而物件導向世界的資料型式是階層式的,面對這兩種資料呈現的型式,程式設計師在資料表的設計上著實燒腦,過去很多教授 ADO.NET 的書籍範例只會教用 DataSet、DataTable、SqlDataReader 來處理從資料庫取得的資料,如果我們直接照著用,當所面臨的需求不再如同書本範例簡單的時候,程式寫起來挺痛苦的,而我們也沒辦法享受到物件導向設計帶給我們的好處,如果我們在工作上還是需要自己下 SQL 語句,Dapper 會是我們的好幫手。

我們都知道到資料庫取資料最好一次要,要到的資料最好也是剛剛好,我們直接從範例來看從資料庫到類別這之間資料的對應,如果遇到有一對一關係、一對多關係及多型的時候,Dapper 怎麼來幫我們從資料庫取資料時做到「一次要,要到的資料剛剛好。」

情境說明

我們假想的情境裡面有四張 Table,分別是 ProductCustomerOrderOrderProduct,這四張 Table 的說明如下:

  • Product:商品資料表,用 Type 欄位來區別是哪一類的商品。
  • Customer:客戶資料表
  • Order:訂單資料表
  • OrderProduct:訂單中商品清單

一次 Query 取得一對一關係的資料

在程式裡面我們也建立相同的類別名稱來對應 Order 及 Customer 資料表,而一張訂單只會被一位客戶所擁有,因此從資料庫取得訂單資訊的時候也一起把客戶資訊抓出來,SQL 語句這樣下我想應該沒什麼問題。

但是,我們設計的類別是長這個樣子,Order 類別裡面有 Customer 的屬性,這欄位的對應要怎麼弄?

在 Dapper 裡面有一個 Multi-Mapping 的 Query 方式可以幫助我們方便對應,程式就這樣寫。

Query 方法的部分,我們必須按照我們 SELECT 欄位的順序告訴 Dapper 說依序對應的類別是哪些?以及最後要回傳的類別是哪一個?依這個例子,我們 SELECT 出來的欄位分別依序是對應到 Order、Customer 類別,最後要回傳的類別是 Order。

中間有一段 Lambda Expression 是要我們指定物件與物件之間的關係,還有一個 splitOn 參數是要告訴 Dapper 說要從哪一個欄位開始分界,預設值是 Id,有多個分界欄位時用逗號隔開。

執行結果如下,輕鬆就幫我們處理好對應關係。

 

一次 Query 取得一對多關係的資料

如果我們想要在取得訂單資訊時,除了客戶資訊之外也一同取得客戶所訂購的商品資訊,那麼 SQL 語句這樣寫應該沒啥毛病。

在 Order 類別裡面也多了 Products 屬性,這種一對多關係的欄位要怎麼對應?

我們先要改寫一下 SQL 語句,因為一對多關係的資料呈現在一個二維的表格上會多不少重複的資料,我們把它拆成兩個表格來呈現,避免多傳輸一些重複性的資料。

這時候我們可以利用 Dapper 中 Multi-Result 的 Query 方式,程式碼如下。

QueryMultiple 方法會回傳多個查詢結果回來,之後我們必須依序使用 Read 方法將結果讀取出來,如果其中有需要 Multi-Mapping 的,操作方式是一樣的。

來看看執行結果,相當方便。

多型要如何對應?

至於多型 Dapper 也沒讓我們失望,它有 Multi-Type 的 Query 方式,但是我們需要多做一些事,直接來看範例。

我們所假定的情境中,Product 資料表實際存放了 CellPhoneBook 這兩類的商品,其中 ScreenSize 欄位是屬於 CellPhone 商品的,Pages 欄位則是屬於 Book 商品的,我們將 Product 改成抽象類別,並分別建立 CellPhone、Book 類別繼承 Product 用來對應。

我們要從 Product 資料表中撈出所有種類的商品,SQL 語句這樣寫大概沒什麼問題。

但是要分別對應到 CellPhone、Book 類別並且指定到一個 Product 集合,要怎麼做?我參考 Dapper Tutorial 寫了一個擴充方法 - PolymorphicQuery

它的做法就是透過識別欄位去產生相對應的 RowParser 用來做派生類別的欄位對應,而我們需要做的事情就是把識別欄位的值做成一個 RowParser 對應表,讓 Dapper 能夠為每個 Row 做正確的欄位對應,PolymorphicQuery 使用上就跟 Query 方法一樣。

執行結果

相關資源

C# 指南
ASP.NET 教學
ASP.NET MVC 指引
Azure SQL Database 教學
SQL Server 教學
Xamarin.Forms 教學