I have a slight (or not so slight) dilemma. It has to do with parent and child keys. Now, the most important thing to remember here is that I want all functionality to work the same whether I use local data or remote data (so I always code to views).

I was working with a treeview control yesterday. I am adding nodes with parent and child relationship. In my table I have a unique record key, integer, (generated by newid()), a unique KEY value which is a character string and used as the key value for the treeview node, the parent key of this node (which is the KEY of the parent record that the current record is a child of. In essence, the same table contains both parents and children. I also have a treelevel field that indicates what level the record is on. This is useful for sorting the data and making sure the parents are created before the children.

The difficulty occurs when new parents and children are added. If my data is remote then I don't have values for the key fields of the new records. Now, normally this wouldn't be a problem. In most cases you have only one parent record and multiple children so after saving the parent record (with remote data to SQL Server I would put the save in a transaction and retrieve the last key with @@IDENTITY) and then add this value to the child record foreign key before saving these records.

However, it is not so simple with multiple parents and children. I either have to make sure I save the records one at a time, retrieve the key and check to see if any other records need this key as the parentkey for child records. I would store a temporary value as the key until I had the one from the database.

Now, I know this can be coded, but it involves very peculiar behaviour with the databehavior class.

With local data I am using the ID table. Maybe my simplest solution is, if using remote data, retrieve a key from a similar ID table at the remote data source and not wait for the key to be generated (like an identity column).

I am wondering if anyone has a more elegant solution that can work with both local and remote data.