Foreign Key self-join on a DataTable

[Ev]

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

[Johnny]

it looks like there are two problems

AutoIncrement columns aren't automatically updated by the database (as far
as I'm aware). To solve this problem, you should include a SELECT statement
in your INSERT & UPDATE commands to get the updated row.

The foreign key should work fine. When adding a new row to the DataTable,
make sure you call the SetParentRow method and pass the parent row You
shouldn't need to set the accept/reject rule. The fk will manage that for you.

HTH

"Ev" wrote:

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

[Ev]

I tried those suggestions and was able to everything to work.

Thanks for the help.

You sure are smart!

"Johnny" wrote:

it looks like there are two problems

AutoIncrement columns aren't automatically updated by the database (as far
as I'm aware). To solve this problem, you should include a SELECT statement
in your INSERT & UPDATE commands to get the updated row.

The foreign key should work fine. When adding a new row to the DataTable,
make sure you call the SetParentRow method and pass the parent row You
shouldn't need to set the accept/reject rule. The fk will manage that for you.

HTH

"Ev" wrote:

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