How to update two different tables with DetailsView Update Command

I am developing an ASP.NET application in C#.

I have two Database tables Food(Id, Name, Description) and FoodLog(Id, FoodId, FoodName, FoodDescription, ChangedFields, Date). As the name indicates, FoodLog stores the modification history made by the users to Food records and specifies in the ChangedFields field which fields have been modified compared with the previous "version" of the record.

To display to the user a specific Food record I use the DetailsView control. When the user presses the Update button an UpdateCommand linked to the SQLDataSource is executed. This UpdateCommand just modifies the corresponing Food record.

In order to add a new record to the FoodLog table I need to catch an event fired by the DetailsView upon update. When I catch this event I manually compare the record inserted by the user (not yet committed to the DB) with the one in the Food table matching its Id in order to keep track of the modified fields and store them in the ChangeFields field in the FoodLog table. Now in order to perform this task I can use two events fired by DetailsView

• ItemUpdated: It is launched after the UpdateCommand is executed. This way I lose track of the modified fields because the previous "version" of the record has been already overwritten.

• ItemUpdating: It is launched before the UpdateCommand is executed. This way I have still the old "version" of the record in the DB and I can make the comparison and then dtore in the FoodLog table. However if the开发者_Go百科n the UpdateCommand fails for some reason I have a record in the log that do not correspond to a change on the Food table.

Anybody might help with this problem? Is it possible to write two different SQL expressions in the UpdateCommand?

Thanks

You could use a stored procedure for your updates.

Call the stored procedure first when you store the old values in log table and then update the main table. Use transaction to avoid partial update in case an error.

If you could just strong text use the sp to generate updating and merging and only use an event .NET. If you could just use the sp to generate updating and merging and only use an event .NET invoking the sp