Best Practice Updating DB Records

Question

Say you retrieve 100 records, and display them on a page. The user only updates 2 of the records on the page. Now you want to update only the two records, and not the other 98. Is it best to have one submit on the page, then somehow know which 2 are updated, then send only those two to the db for an update? What does the "somehow" look like? Or, would you have an update-submit button for each row, and have it only update the record its tied to?

Answer 1

One submit button. I could foresee case I might use more then one, but in the general case just one. (Note, this looks like a web page question to me, so I'm answering with that assumption.)

There are 3 ways that come to mind which you could handle the tracking changes:

JavaScript: Put a onChange() function on the controls that update a hidden field. If that hidden has a value, then update the associated record. Requires JS on the browser, and doesn't tell you which fields to update, just which records.

Lots of form fields: Put a hidden field out with each control and compare them all when they come back. This would be ugly, but it would allow you to know which fields to update (not just the record). It would also allow you to know if someone undid a change that started.

Sessions: You could place the original values in session variables, then do the comparison when the values come back. This would be a little more elegant then lots of hidden fields, and less open to people playing with the posted back data (since you should never trust anything that comes back, even in hidden fields). Requires cookies on the browser and sessions on the server technology.

Answer 2

Ajax it using jQuery or some other JavaScript library and put and update button on each row.

Answer 3

There are many answers to this question and to some extent they depend upon your development tools and the "feel" of the site.

If you were implementing Ajax calls to do the updates on a line by line basis then this would logically seem right to have a button per line and then update it with an Ajax call when a line was changed.

This is also just the scenario that disconnected data sets were designed to solve and ADO.net handles these very well.

So as ever, the answer is "It Depends!"

Answer 4

You can use JavaScript to mark each field as changed when a user changes an input field. Create a hidden fields that has the id of the row you are updating, and dirty flag. (like is_dirty_$id) In JavaScript, create an onChange handler that sets the hidden field as dirty. when any input is changed.

Alternatively, you can create hidden fields for each real field you display. the hidden field would contain the initial values. check each field on the server side to determine what has changed.

You probably want to store a last_modified date as a hidden field for each record. This way if another user updates the same record, you can display an error message saying "this record has been updated by another user" or similar.

Answer 5

Of course there are different ways you could do this. In general, you can save yourself some trouble and server-side processing by using Javascript to assemble your POST data for only the records that have changed. Two thoughts on how this might work:

1) Go the ajax route and do live-editing. So records are presented in a table and appear to be non-editable. When a user clicks a particular row, that row becomes editable by using Javascript to create the appropriate html form on the fly. Then have either a submit button or some other handler (say, moving focus to another table row) which will trigger the POST which updates the DB (asynchronously via your preferred ajax method). Happily the mainstream Javascript frameworks can help a lot in this area.

2) Checkboxes - whenever a row is edited, its checkbox becomes checked. When the submit button is clicked, use javascript to post the POST data by grabbing everything in row whose checkbox is checked. A user can un-check a box to cancel changes to that row before submitting.