How many way’s are there to implement locking in ADO.NET ? Dot Net Interview Questions

punjabtechnicaluniversity.blogspot.in


Following are the ways to implement locking using ADO.NET :-

1. When we call “Update” method of DataAdapter it handles locking internally.If the Data Set values are mot matching with current data in Database it raises Concurrency exception error.We can easily trap this error using Try..Catch block and raise appropriate error message to the user.

2. Define a Datetime stamp field in the table.When actually you are firing the UPDATE SQL statements compare the current timestamp with one existing in the database.Below is a sample SQL which checks for timestamp before updating and any mismatch in timestamp it will not update the records.This is the best practice used by industries for locking.

Update table1 set field1=@test where LastTimeStamp=@CurrentTimeStamp

3. Check for original values stored in SQL SERVER and actual changed values.In stored procedure check before updating that the old data is same as the current.Example in the below shown SQL before updating field1 we check that is the old field1 value same.If not then some one else has updated and
necessary action has to be taken.

Update table1 set field1=@test where field1 = @oldfield1value

Locking can be handled at ADO.NET side or at SQL SERVER side i.e. in stored procedures.