在一次的呼叫下進行一對多資料表存取 | 2019 鐵人賽

在資料庫設計中經常會碰到的情境 新增/維護主資料與關連資料

建立一筆訂單

新增訂購人/出貨資訊（一）與商品清單（多）

建立使用者

新增使用者（一）與隸屬群組（多）

建立商品資料

新增商品資料（一）與所屬分類（多）

若使用 EntityFramework 的話可能會撰寫程式碼如下

using(DbContext _context = new DbContext())
{
 int _identity = Order.Create( ... );
 OrderDetail.Create(_identity, items);
 
 ...
 
 _context.SaveChange();
}

若今天要由資料庫人員建立物件給 .NET 開發人員呼叫的話 就會遇到下列問題

如何在一次的 StoredProc 呼叫中存取一對多的資料內容

使用「使用者定義資料表參數」函數 User-Defined Table-Valued Parameter 就可在 StoreProc 物件中依次傳遞多筆資料列

範例程式碼片段如下

CREATE TYPE [Order].[OrderItems]
 AS TABLE
(
 [ProductId]  INT,
 [SellPrice]  SMALLMONEY,
 [Quantity]  DECIMAL(13,3)
)

CREATE PROC [Order].[AddOrder]
 @OrderName  NVARCHAR(50)
 @Items   [Order].[OrderItems] READONLY
AS
 ...
 --實作儲存訂單的方法
 
 INSERT INTO [Order].[OrderDetails] (
  ... Columns ...
 ) SELECT 
  ... Columns
 FROM @Items
 ...
GO

請注意 REAONLY 代表在 StoredProc 片段中此參數內容是無法變更的

此為必須設定之項目 ADO.NET 需要將 DataTable 物件儲存 SqlParameter 物件中才可進行呼叫

透過此方式 .NET 開發人員就可以透過呼叫 StoredProc 新增一對多關係資料 資料庫開發人員也可以透過修改此 StoredProc 進行日後維護作業

參考資料

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017