Possible to toggle Updatable<->Read-Only record

[R Reyes]

Hi,

I'm having an issue in my C#.NET desktop application where two or more
people viewing/editing the same record (from SQL Server) keep overwriting
each others changes.

Is there a setting in SQL Server (or some other easy way) to make it so that
only the first user may update the record and any subsequent users get a
"read-only" version of the record, similar to how it works with Microsoft
Office word and excel documents?

What I was thinking was programming the "Save" button to add a JobInUse=1,
JobID=Some Job ID to the database and then have all users read that table to
see if they can edit the record, however some times people do not close their
programs/computers down properly... If that happened, the record would never
get updated with JobInUse=0 to show they are done working with it so I fear
this solution may not work.

Does anyone have any good solutions for this? Please help and thanks in
advance!

R

 

[Ciaran O''Donnell]

One potential solution to this is to have a version number column of every
table. When you select a record, select the version, when you update a
record, check that the version number is the same as the one you selected
before updating. The only other way to do this is to record that the row is
in use by somebody and the datetime they last accessed it. Then you can time
people out after 10 minutes or so to update the record, or if they login
again (because their app/machine crashed), you could remove their lock. If
the app has the screen open for a long period of time, meaning longer than
your time out, you could have the application upate the last accessed time to
prevent other users thinking they have timed out. It produces more hits to
the db but they should be small and required to get the task done.

It depends on whether you want to make people aware when they open a record
that it is being edited by someone else, or just tell them when they save it.

The only way to make this more secure is to have a piece of middle ware
somewhere track the records in use. Then when a session to the middleware
closes, release all the records marked in use by that user.