I'm at a fork in the road about a programming choice and hope the community can share some wisdom with me.  It simply involves this:

Suppose you have a web app that has a number of reference data editors for the administrators of the app.  Think of an e-commerce site where assorted data has to be edited from time to time:

• Products: Descriptions, prices, photos, etc.
• Customers: Address, phone numbers, e-mail address, etc.

Let's say that an admin goes into one of these editors and makes changes affecting 20 records (some editing, some inserting of new records, some deleting of existing ones).  He hears lightning outside and is concerned that power in his office may be lost, so he presses "Save" on the web app.  He's been taught that this will save his work to date on the server if the power is ever lost but must press "All Done" to finalize the changes.

So he continues his work, affecting another 10 records and then his work is done.  He can then press one of 2 buttons:

• All Done - This will commit all changes he's made in this current session and they'll be permanent.
• Abort - Perhaps he's informed that the changes he was told to make were all wrong and he didn't want any changes committed.

Note: If the power does go off before he presses either then the next time he starts the web app he'll be presented with a message informing him that he had previously saved 20 records but hadn't yet finalized (committed) them.

 

To me, this approach seems like a very Web 2.0 way of doing things and gives the user maximum flexibility and power.  But one of my colleagues has suggested that each & every record should be changed permanently, before proceeding with the next record.

I'm most curious to get your thoughts!

--
Robert Werner
Vancouver, BC

Hi, 

As I see there are positives and negatives of both approaches

 

Updating the records in a batch process.

 

1. Fever updates to the database as a whole set of modifications will be sent in a batch, this will depend of course on how frequently the user will be saving and then committing or aborting the modifications.
2. An option for user to rollback the update or simply abort the modification if the user has not committed all the changes.  This can be done in two stages prior to clicking save or after clicking save but before committing.
3. The problem here is that if there are many users who could potentially be working  with the same set of records then updating them in a batch means that there is a higher percentage of update conflicts than when permanently updating each record individually. 

 

Updating each record permanantly

1. More calls to the database when compared to the previous approach.
2. Lesser chance of an update conflict when compared to the previous approach, however you will still have to write the logic to deal with update conflicts.
3. Every change is committed to the database so if you have to rollback a number of updates then it could be a costly process.
4. This will depend on how you implement the application but lets say if I modify/insert/delete a record I have to wait till the change is committed then that could become irritating when compared to updating a whole batch.  Of course if you do this asynchronously then it wont be too bad.

Hope this helps you to decide the most suitable approach for your Application.

 Thanks

Arjuna. 

Arjuna,

Thank you for your thoughtful response!  I have two questions for you:

1. Regardless of which method is used, would you wrap the whole process within a Transaction?
2. Excuse my limited knowledge of ADO.net 2.0 but . . . suppose we were to go with the Batch update method.  Is there some command or technique that would allow one to quickly update SQL Server with a now updated dataset? 

Robert

Hi Robert,

I am not sure what you mean by whole process, but yes in general I would wrap all database updates in a Transaction.

If you are using an Adapter you have the Update method which processes in a batch mode in .NET 2.0.

Thanks

Arjuna.
 

It sounds like the ASP.Net 2.0 approach to database editing works something like this:

1. Query a database and populate a DataSet using a DataAdapter.
2. Make whatever changes you want to the DataSet - though only doing so via the "approved" methods.
3. When done, simply execute the DataAdapter.Update() method.

 
Would this be correct?

Robert