親/子レコードの挿入

ユースケース

レコードを挿入する際、親レコードに依存する子レコードに関する詳細を入力する必要がある状況は度々発生します。

例えばCRM システムを使う場合には、Invoices を入力するには最低1つの明細行が必要です。請求書の行項目は複数のフィールドを持つことができるため、こうしたInvoices の仕様は、データをリレーショナルテーブルとして提供する際に問題となります。データを読み込む際、外部キーで接続されたInvoice とInvoiceLineItem テーブルをモデル化するのは容易です。しかし挿入の際には、CRM システムはInvoice およびInvoiceLineItems を1つの操作で作成する必要があります。

こうした問題を解決するために、CData のツールは親コレクションカラム上に子コレクションカラムを提供します。これらのカラムは、親および子レコード両方の情報を持つINSERT ステートメントを発行する際に使用することができます。

例えば、Invoice テーブルにInvoiceLineItems というシングルカラムがあるとします。挿入の際には、InvoiceLineItems テーブルに挿入する必要があるレコードの詳細を、Invoice レコードのInvoiceLineItems カラムに渡すことができます。

次のセクションでは、この方法を説明します。

親 / 子レコードを挿入する方法

親 / 子レコードを挿入するために、本製品は2つの方法を用意しています。一時テーブルを使った挿入と、XML への集約による挿入です。

一時（#TEMP）テーブル

データを入力する最も簡易な方法は、本製品がメモリに格納する#TEMP テーブル、または一時テーブルを使用することです。

#TEMP テーブルは以下の構文で参照してください。

TableName#TEMP

#TEMP テーブルは接続中はメモリに格納されます。

そのため、これらのテーブルを使用するには挿入の発行間で接続を閉じることはできず、各クエリで異なる接続を使用する環境では一時テーブルは使用できません。

単一の接続内では、テーブルはバルク挿入が成功するまでメモリに残り、成功した時点でメモリから消去されます。

例：

INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Basketball', 10, 9.99)
INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Football', 5, 12.99)

InvoiceLineItems テーブルに書き込みが行われた後で、Invoice テーブルへの挿入中に#TEMP テーブルが参照されます。

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')

内部では本製品が#TEMP テーブルから値を読み込みます。

ReferenceNumber は、どのInvoice に明細が紐付けられているかを特定するのに使用されています。これは、各Invoice に別々の明細が記載されている状況で、#TEMP テーブルにバルク挿入で書き込みが行われ、使用される可能性があるためです。これで#TEMP テーブルをバルク挿入で使用することができます。例：

INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV002', 'Jane Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices SELECT ReferenceNumber, Customer, InvoiceLines FROM Invoices#TEMP

この場合、2つの異なるInvoice を挿入しています。ReferenceNumber によって、どのLines がどのInvoice に入力されるかを決定します。

Note：ここで示したテーブルとカラムは、本製品が一般にどのように動作するかを示す一例です。特定のテーブルおよびカラム名は、本製品内で異なることがあります。

XMLの直接挿入

#TEMP テーブルの代替として、XML を直接使うこともできます。#TEMP テーブルはXML を作成するためには使われないので、同じ接続を使うか挿入後に接続を閉じるかは影響しません。

例：

[
  {
    "Item", "Basketball",
    "Quantity": 10
    "Amount": 9.99
  },
  {
    "Item", "Football",
    "Quantity": 5
    "Amount": 12.99
  }
]

または

<Row>
  <Item>Basketball</Item>
  <Quantity>10</Quantity>
  <Amount>9.99</Amount>
</Row>
<Row>
  <Item>Football</Item>
  <Quantity>5</Quantity>
  <Amount>12.99</Amount>
</Row>

XML はその性質上、親レコードに対して挿入ごとに全体で渡されるため、これらの例ではReferenceNumber が存在しないことに注意してください。行ごとに完全なXML を作成および発行する必要があるので、親レコードに子レコードを紐付け直すものは必要ありません。

次に、値を挿入します。

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '{...}')

または

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '<Row>...</Row>')

Note：本製品はXML / JSON 集約の使用もサポートします。

Executing Deep Inserts with SQL

The 本製品 supports OData deep inserts, in which you simultaneously create a base entity and link it to related entities, by specifying navigation properties. To specify ナビゲーションプロパティ for an entity, you may either submit JSON / XML data, or you may create a temporary table for the navigation property and then reference the temporary table in the insert to the base table. Submit the XML / JSON or reference the temporary table in the appropriate navigation property column on the base table. Each navigation property column is prefixed with the word "Linked".

Example: Deep Inserts using XML / JSON

To submit XML or JSON data, simply supply the values for the table the navigation property is referencing in XML or JSON format. If you are familiar with the OData standard, you should not be submitting values in the standard. The XML / JSON used here is simply a means of supplying multiple values ot the CData ADO.NET Provider for OData.

For example, consider the Orders table in Northwind odata.org test service. To create a new Order, you specify the Products ordered, Customer, Employee, and Shipper. To do so, you need to specify the Customer, Order_Details, Shipper, and Employee navigation properties.

Customer: The following XML represents a new Customer:

  <Row>
    <CustomerID>VINET</CustomerID>
	<CompanyName>Vins et alcools Chevalier</CompanyName>
	<ContactName>Paul Henriot</ContactName>
	<ContactTitle>Accounting Manager</ContactTitle>
	<Address>59 rue de l'Abbaye</Address>
	<City>Reims</City>
	<PostalCode>51100</PostalCode>
	<Country>France</Country>
	<Phone>26.47.15.10</Phone>
	<Fax>26.47.15.11</Fax>
  </Row>

Order_Details: The following JSON add two Products to the Order:

  [
    {
	  "ProductID": 72,
	  "UnitPrice": 34.80,
	  "Quantity": 5,
	  "Discount": 0
	},
	{
	  "ProductID": 42,
	  "ProductID": 9.80,
	  "ProductID": 10,
	  "ProductID": 0
	}
  ]

Employee: The following XML specifies the existing Employee:
```
  <Row>
    <EmployeeID>5</EmployeeID>
  </Row>
```
Shipper: The following JSON specifies the existing Shipper:
```
  [
    {
      "ShipperID": 3
    }
  ]
```

In order to execute the insert, simply reference or include as string literals the complete XML / JSON. For example:

INSERT INTO Orders (CustomerID, EmployeeID, ShipVia, ShipName, ShipAddress, ShipCity, ShipPostalCode, ShipCountry, OrderDate, LinkedOrder_Details, LinkedCustomer, LinkedEmployee, LinkedShipper) VALUES ('VINET', 5, 3, 'Paul Henriot', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '07/04/1996', '{ ... }', '<Row>...</Row>', ?, ?)

Example: Deep Inserts using Temporary Tables

If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory. Keeping with the Northwind example, you need to specify the following navigation properties.

Creating Temporary Tables

Insert the related entities into temporary tables that correspond to each navigation property. You can specify an existing entity's primary key or you can insert a new entity.

Customer: The following statement creates a new Customer:

INSERT INTO Customers#TEMP (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country, Phone, Fax) 
VALUES ('VINET', 'Vins et alcools Chevalier', 'Paul Henriot', 'Accounting Manager', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '26.47.15.10', '26.47.15.11')

Order Details: The following statements add two Products to the Order:

INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (72, 34.80, 5, 0)

INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (42, 9.80, 10, 0)

Employee: The following statement specifies the existing Employee:
```
INSERT INTO Employees#TEMP (EmployeeID) 
VALUES (5)
```
Shipper: The following statement specifies the existing Shipper:
```
INSERT INTO Shippers#TEMP (ShipperID) VALUES (3) 
```

The CData ADO.NET Provider for OData will assume that the Shipper and Employee already exist and will only link to the existing references since only the primary keys were specified for either. When more than just the primary key is defined, such as the examples for Customer and Order_Details, the CData ADO.NET Provider for OData will attempt to create new entries - triggering the deep insert.

Inserting the Entity

In the INSERT statement for the base entity, reference the temporary tables in the LinkedOrder_Details, LinkedCustomer, LinkedEmployee, and LinkedShipper columns:

INSERT INTO Orders (CustomerID, EmployeeID, ShipVia, ShipName, ShipAddress, ShipCity, ShipPostalCode, ShipCountry, OrderDate, LinkedOrder_Details, LinkedCustomer, LinkedEmployee, LinkedShipper) VALUES ('VINET', 5, 3, 'Paul Henriot', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '07/04/1996', 'Order_Details#TEMP', 'Customers#TEMP', 'Employees#TEMP', 'Shippers#TEMP')

Code Example

Below is the complete code to create the new Order:

ODataConnection odconn = new ODataConnection("URL=http://services.odata.org/V4/Northwind/Northwind.svc");
odconn.Open();

ODataCommand odcmd = new ODataCommand("INSERT INTO Customers#TEMP (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country, Phone, Fax) VALUES ('VINET', 'Vins et alcools Chevalier', 'Paul Henriot', 'Accounting Manager', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '26.47.15.10', '26.47.15.11')", odconn);
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (72, 34.80, 5, 0)";
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Order_Details#TEMP (ProductID, UnitPrice, Quantity, Discount) VALUES (42, 9.80, 10, 0)";
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Employees#TEMP (EmployeeID) VALUES (5)";
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Shippers#TEMP (ShipperID) VALUES (3)";
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Orders (CustomerID, EmployeeID, ShipVia, ShipName, ShipAddress, ShipCity, ShipPostalCode, ShipCountry, OrderDate, LinkedOrder_Details, LinkedCustomer, LinkedEmployee, LinkedShipper) VALUES ('VINET', 5, 3, 'Paul Henriot', '59 rue de l''Abbaye', 'Reims', '51100', 'France', '07/04/1996', 'Order_Details#TEMP', 'Customers#TEMP', 'Employees#TEMP', 'Shippers#TEMP')";
odcmd.ExecuteNonQuery();

odconn.Close();

Example: Bulk Inserts using Temporary Tables

If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory. Use IncludeReferenceColumn connection property to add a input only Reference column to properly associate children during a deep insert with the same parent. Keeping with the Northwind example, you need to specify the following navigation properties.

Creating Temporary Tables

Insert the related entities into temporary tables that correspond to each navigation property. You can specify an existing entity's primary key or you can insert a new entity.

Categories: The following statements adds to Categories child table:

INSERT INTO Categories#TEMP (ParentReference, ID, Name) VALUES (100, 4, 'DVD')

INSERT INTO Categories#TEMP (ParentReference, ID, Name) VALUES (100, 5, 'BluRay')

INSERT INTO Categories#TEMP (ParentReference, ID, Name) VALUES (200, 6, 'Radio')

Products: The following statements adds to Product parent table:

INSERT INTO Products#TEMP (ID, ParentReference, price, CategoriesAggregate) VALUES (20, 100, 45, 'Categories#TEMP')

INSERT INTO Products#TEMP (ID, ParentReference, price, CategoriesAggregate) VALUES (21, 200, 25, 'Categories#TEMP')

Inserting the Entity

In the INSERT statement for the base entity, reference the temporary table in the LinkedCategories column:

INSERT INTO Products (ID, ParentReference, price, LinkedCategories) SELECT ID, ParentReference, price, CategoriesAggregate FROM Products#TEMP

Code Example

Below is the complete code to insert into Products:

ODataConnection odconn = new ODataConnection("URL=http://services.odata.org/V4/Northwind/Northwind.svc;IncludeReferenceColumn=true;");
odconn.Open();

ODataCommand odcmd = new ODataCommand("INSERT INTO Categories#TEMP (ParentReference, ID, Name) VALUES (100, 4, 'DVD')", odconn);
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Categories#TEMP (ParentReference, ID, Name) VALUES (100, 5, 'BluRay')";
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Categories#TEMP (ParentReference, ID, Name) VALUES (200, 6, 'Radio')";
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Products#TEMP (ID, ParentReference, price, CategoriesAggregate) VALUES (20, 100, 45, 'Categories#TEMP')";
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Products#TEMP (ID, ParentReference, price, CategoriesAggregate) VALUES (21, 200, 25, 'Categories#TEMP')";
odcmd.ExecuteNonQuery();

odcmd.CommandText = "INSERT INTO Products (ID, ParentReference, price, LinkedCategories) SELECT ID, ParentReference, price, CategoriesAggregate FROM Products#TEMP";
odcmd.ExecuteNonQuery();

odconn.Close();