Cannot specify parent/child DataRelation for one to many DataTables



Pasted below is an example of the XML schema I must produce from SQL queries across multiple tables. I can get the data needed and apply it to rows of tables with the names required to match this schema.

Unfortunately since no data is serialized in the Items table I don't have any columns from the data to describe its parent/child relationship. I added a ID row to each of my DataTables and specified the SQL results per row for each table to properly generate the schema for every DataTable in my DataSet. However, this does not work to set a DataRelation between my Items and each Item table. When I attempt to add a DataRelation for the ID columns in each DataTable, as shown in the code snippet below, I receive the following error:


"Cannot add a column named 'Item': a nested table with the same name already belongs to this DataTable."



//Note I had applied the SQL data to a Table named "tempTable" so I could specify the
//serialization here

int x = 0;
foreach (DataRow fieldRow in ds.Tables["tempTable"].AsEnumerable())
{
DataTable Item = new DataTable("Item");
ds.Tables.Add(Item);
Item.Columns.Add("ID");
Item.Columns.Add("LineNumber");
Item.Columns.Add("ItemID");
Item.Columns.Add("UnitPrice");
Item.Columns.Add("Description");
Item.Columns.Add("OrderQty");
Item.Columns.Add("TotalQtyShipped");

DataRow dr = Item.NewRow();
dr["ID"] = 1.ToString();

x++;
dr["LineNumber"] = x;
dr["ItemID"] = fieldRow[0].ToString();
dr["UnitPrice"] = fieldRow[1].ToString();
dr["Description"] = fieldRow[2].ToString();
dr["OrderQty"] = fieldRow[3].ToString();
dr["TotalQtyShipped"] = fieldRow[4].ToString();
Item.Rows.Add(dr);

DataRelation itemRelation = new DataRelation("itemRelation", ds.Tables["Items"].Columns["ID"], Item.Columns["ID"]);

//Error occurs on this line during second iteration
itemRelation.Nested = true;
ds.Relations.Add(itemRelation);
}


//Example XML schema



<?xml version="1.0" standalone="yes"?>
<AS xmlns="mynamespace">
<ASTransmissionHeader>
<ASCount>1</ASBNCount>
<TransID>15</TransID>
</ASTransmissionHeader>
<ASH>
<ASHead>
<Store>Test Store #1</Store>
<PONumber>10000</PONumber>
<OrderDate>12/1/2014 12:00:00 AM</OrderDate>
<ShipDate>12/10/2014 12:00:00 AM</ShipDate>
<InvoiceDate>12/10/2014 12:00:00 AM</InvoiceDate>
<InvoiceNumber>928773</InvoiceNumber>
<InvoiceTotal>523.81</InvoiceTotal>
<DeliveryDate>12/10/2014 12:00:00 AM</DeliveryDate>
<TotalQtyShipped>27.0</TotalQtyShipped>
</ASHead>
<Items>
<Item>
<LineNumber>1</LineNumber>
<ItemID>Y0003</ItemID>
<UnitPrice>0.50</UnitPrice>
<Description>Yellow Yo-Yo</Description>
<OrderQty>200</OrderQty>
<OrderExtTotal>100.00</OrderExtTotal>
</Item>
<Item>
<LineNumber>2</LineNumber>
<ItemID>S5005</ItemID>
<UnitPrice>20.00</UnitPrice>
<Description>Blue Tots Trucks</Description>
<OrderQty>3</OrderQty>
<OrderExtTotal>60.00</OrderExtTotal>
</Item>
</Items>
</ASH>
</AS>

No comments:

Post a Comment