ADO/ADO.NET - Prevent more user from edit in same record proplem

Asked By ahmed elbarbary on 25-Feb-15 03:17 PM
suppose i need to make update the employee as following  
CREATE PROC UPDATE_EMPLOYEE
(
@EmployeeID INT,
@EmployeeName NVARCHAR(50)
)
AS
update Employee set EmployeeName=@EmployeeName where EmployeeID=@EmployeeID
Suppose I have two useres A,B
A modified or edit using stored procedure above employee no 5000 field EmployeeName
B need to modify employee no 5000 using stored procedure above field EmployeeName
i need to prevent user B To modify same record using time stamp How to do that from c# code and sql server and what modification in stored procedure to accept timestamp
EmployeeTable
EmployeeID
EmployeeName
TimeStampEmp
Meaning allow to another user after specified time
Robbe Morris replied to ahmed elbarbary on 26-Feb-15 08:38 AM
You added the timestamp taken when the record was queried and add it to the input parameters of this stored procedure.  In the WHERE clause, if the timestamp on the record is greater than the timestamp passed in, don't permit the update.
ahmed elbarbary replied to Robbe Morris on 26-Feb-15 12:49 PM
I do stored procedure as following
ALTER PROCEDURE [dbo].[UpdateEmployee]
@EmployeeName nvarchar(50),
@OldTimeStamp timestamp
AS
BEGIN
 update Employee set EmployeeName=@EmployeeName where CurrentTimestamp=@OldTimeStamp
 if(@@rowcount=0)
 begin
  raiserror('There are another one changed the record')
 end
END
IF possible what the code i write in c# interface that represent stored procedure
Robbe Morris replied to ahmed elbarbary on 26-Feb-15 01:02 PM
I think timestamp is a byte[] in C#.  If you do some research on sql server concurrency and timestamp you'll see there are some complications with this sort of thing.  In particular, the conversion of the byte array to and from .NET code back into sql server.  I've avoided using timestamp for concurrency for just this reason.  The most accurate solution I've used over the years is a UniqueIdentifier type in SQL Server.  If the passed in Guid doesn't match, no update is done.  And, I always assign the record a new uniqueidentifier upon changes.  More manual work than a timestamp but less hassle in the application.