How do you save the tree structure of a database table with automatic incremental identifiers using the ADO.NET DataSet and DataAdapter

Question

I have a relational role table, which is a tree structure

ID [INT] AUTO INCREMENT Name [VARCHAR] ParentID [INT] 

I use ADO.NET DataTable and DataAdapter to load and save values ​​in this table. This works if I only create children from existing rows. If I create a child row, then create a child of this child, and then Update, the temporary identifier value generated by the DataTable will go into the ParentID column. I have the following set of data relationships:

 dataset.Relations.Add(New DataRelation("RoleToRole",RoleTable.Columns("ID"), RoleTable.Columns("ParentID"))) 

And when I make new child rows in a DataTable, I call the SetParentRow method

 newRow.SetParentRow(parentRow) 

Is there anything special I need to do to generate identifier generation recursively when I call Update on the DataAdapter?

Answer 1

I do not know, in particular, ADO.net, but most ORMs will not automatically insert the identifier of a new record into relationships. You have to resort to a two-step process:

• create and save parent
• create and save a child with a relation to the parent

The reason this is difficult for ORMs is because you might have circular dependencies, and he would not know which object he needed to create an identifier for the first. Some ORMs are smart enough to understand relationships where there are no such circular dependencies, but most of them are not.

Answer 2

It doesn't matter if you go

 newRow.SetParentRow(parentRow, RoleTable.Relations("RoleToRole")) 

Answer 3

I suggest you add ExternalKeyConstraint, with UpdateRule installed in Cascade.