G
Guest
I have a database table in SQL Server that has a self join. In C# I have a
DataTable with a self-join. I have defined a foreign key constraint on the
DataTable for the self join. The AcceptRejectRule is set to Cascade
Table Structure:
OrderID (Identity field)
OrderDesc
OrderParent (this is the self-join - it is a foreign key to OrderID)
In my code I add several rows to the table, createing a hierarchy of orders
e.g.
1000 (OrderParent = NULL
- 1001 (OrderParent = 1000)
- 1002 (OrderParent = 1000)
The OrderID field of the DataTable is set as an AutoIncrement column with
the seed starting at -1. When I insert the records in the database, it
should, in theory, insert Order 1000, get the idenity of the new row, update
the identity in the row, and then update the OrderParent in all child rows.
When I call the DataAdapter Update() method, only the top level rows in the
hierarchy (i.e. Order 1000) are inserted into the table. None of the other
rows are inserted (i.e Order 1001, 1002 aren't in the db). It appears that
the Update() doesn't even try to add those rows. After the Update() method I
checked the rows in the table and they all still have their initial OrderID
and OrderParent values so it doesn't look like the field is being updated
with the idenity value from SQL Server.
If don't set the AcceptRejectRule property, it will attempt to insert the
child rows but since the OrderParent hasn't been updated there is a foreign
key violation in SQL Server.
Any help would be appreciated
DataTable with a self-join. I have defined a foreign key constraint on the
DataTable for the self join. The AcceptRejectRule is set to Cascade
Table Structure:
OrderID (Identity field)
OrderDesc
OrderParent (this is the self-join - it is a foreign key to OrderID)
In my code I add several rows to the table, createing a hierarchy of orders
e.g.
1000 (OrderParent = NULL
- 1001 (OrderParent = 1000)
- 1002 (OrderParent = 1000)
The OrderID field of the DataTable is set as an AutoIncrement column with
the seed starting at -1. When I insert the records in the database, it
should, in theory, insert Order 1000, get the idenity of the new row, update
the identity in the row, and then update the OrderParent in all child rows.
When I call the DataAdapter Update() method, only the top level rows in the
hierarchy (i.e. Order 1000) are inserted into the table. None of the other
rows are inserted (i.e Order 1001, 1002 aren't in the db). It appears that
the Update() doesn't even try to add those rows. After the Update() method I
checked the rows in the table and they all still have their initial OrderID
and OrderParent values so it doesn't look like the field is being updated
with the idenity value from SQL Server.
If don't set the AcceptRejectRule property, it will attempt to insert the
child rows but since the OrderParent hasn't been updated there is a foreign
key violation in SQL Server.
Any help would be appreciated