Saving TreeView Changes to Database

Question

Hi all,

I have a treeview control in a VB.NET 2005 form that shows item category/subcategory, so root elements are all categories and child elements are all subcategories.   I pull the data off the database using code (no wizards), actually from two different tables.

The user can add/change categories and subcategories.  No deletions are allowed, user can only mark them inactive.  To tie the categories to the subcategories I use a relationship between the two tables of the Access DB.  That all works perfect.

My question is on how should I save the changes back to the two tables.  What would a good practice be ?  I thought I could delete both tables (categories and subcategories) and traverse the treeview to save record by record but I don't think this is the best method.  I'm not using automatic VS 2005 databinding, etc, this is pure code.  I could end up with two empty tables if the system crashes, etc.  Would this be a case for using a transaction ???

Another thought would be to traverse the tree and perform an UPDATE command.  If 0 rows are affected, the item is new and therefore perform an INSERT...  Would this be acceptable in terms of performance ?

I'm letting the user do all the maintenance on the treeview and once she's done I have a SAVE button.  I'm not saving every change she does on the fly.

Any other thoughts ?

Thanks again,

ST

 

Answer 1

Just a thought - since you want to use the SAVE button, you could store a sequence of operations that the user does. You can even be smart here (say the user does an edit, then a delete - you can filter through your sequence of operations and remove extraneous operations). When the user hits save, you should use a transaction and translate your sequence of operations into the appropriate SQL statements.

Answer 2

Thanks Timothy for your reply.  Would you have a better way if I don't have the SAVE button to save all changes at once (at the end)?

I could do with saving each change on the fly if that's a better practice.  If I go with this is there any code our there that shows how to do it ?

Thanks again,

ST

Answer 3

I think it is better to use a save button, because from the user's perspective, it's more clear. The user can make changes, and the commit everything once they have double checked - that seems like a much better user model.

Were you able to implement the save feature by tracking the changes?

Answer 4

Do not forget to save the operations only after they have been completed successfully