How would I know when to insert, delete or update based off a C# list?

Question

Let's say that I have three tables "Person", "Area", and "Person_Area". Each person can work in many areas and each area can have many people in them. "person_Area" is a bridge table that contains person_id and area_id

In my code I have two asp list boxes located on a person form. List box one contains all available areas and list box two can be populated with areas from list box one based on user selection. The areas that get put into list box two are the areas that a person is in.

This is all fine and easy, but when I decide to save to my database I am unsure how to Insert, Update, Delete the "person_Area" table. I don't want to reinsert an area for a person if it is already in the table and if the user removes an area from list box two how does the code know to delete it when I post back to the server?

Would it be reasonable to just delete all records in the "Person_Area" table for the specific person and then re-add all the current user selections? or is there a better option? I am stumped.

Answer 1

No reason to delete and re-insert. To much work when you don't even know for sure if any of those rows have changed.

There are two reasonable options, in my opinion:

1. Track "changes" to the UI (added an area, removed an area), then execute the same actions (delete row, add row) against the database. (You'll have to account for the case where someone adds an area, then removes it before hitting SAVE, or vice versa. Just cancel your internal flag, don't do an INSERT then a DELETE)

2. Just query the person_area table for that user in some logical order (the same way the listbox is ordered). Then walk through the rows in the recordset and listbox one at a time. If the database has a row that's not in the listbox, perform a DELETE. If the listbox has an entry that's not in the database, perform an INSERT.

The second sound pretty straight-forward to me, and is probably the first approach I would take.

Answer 2

If you are using a database system that has the MERGE command, this would be a good approach. The MERGE command can handle the INSERT, UPDATE and DELETE actions in a single command based on the comparison of your list with the contents of the table.