• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

tableAdapter Update only SQL table fields that where modified in web page

Hi,

I'm designing a web application where different users could be updating the same records.
I would like to make sure that only the changed fields by one user are updated one by one so there is no concurrency violation.

What is the best method to design this?

Its my first time working with SQL 2005 so i'm not sure about the tableAdapters "optimistic concurrency" option. Will this take care of it by itself?

thank you
0
carlosmonte
Asked:
carlosmonte
  • 2
  • 2
3 Solutions
 
BirkyCommented:
Hi,

Being a web application the edit page could letf open for some time before they decide to edit a record. Thus they may overrite someone elses changes. SQL will protect this from happening when you modify the data directly in the database but chances are you web application only has a connection open when it is accessing the data (either being read or written to) and, kind of, caching the data in the interem.

I would suggest some form of locking of records to ensure that data isn't lost. I have done this a couple of ways but I cannot say which is the best for you. It all depends on how your data is accesssed and by what application. You could have an extra field in your database called "locked" that is set when a user edits a record and released when they are finished, or you might code it all in the application so that it does all the locking (look up semaphores or mutex) or you could design you own. Being a web application you may also want to check out the global.asx file if you are going for the software option.

Hope this helps.
0
 
theplonkCommented:
In some of my projects in the past I've implemented a record version number that is updated every time a record is update, and is checked before any update is applied.

For instance:
The original record id = 1. (generally i would use a guid, but for simplicity i've just used integers)

When person A and person B open up the record with out editing they will get the recid 1.

If person A updates the record, the recid 1 will be checked against the current rec id, which is 1, then the update will be applied and the recid will be changed to 2.

Then if person B tries to update the rec order, it will fail since his recid of 1 doesn't match the new recid of 2.

A simple way would be to create a stored procedure to accept the update parameters and the clients recid, to perform the validation and update the record.
0
 
carlosmonteAuthor Commented:
Birky:

In your suggestion of locking using a field in the DB what happens if the app crashes on a user that is in edit mode in a page. this means the record will stay locked for other users.
how could this be avoided?
0
 
BirkyCommented:
Hi,

If you check out the Global.asax file you can add an event on session end. If you setup an event here to release all the users current locks then you wont have this problem. If the page has an error then yes the record will be locked but only until the users session expires

I have a page that shows all the locked records and allows them to be unlocked but I havent needed to use it yet.

Hope this helps
0
 
carlosmonteAuthor Commented:
these are good comments but i want to research these options first.
Experts, is it ok if i leave it open a little longer? thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now