When we update , the DataSet helps out with the process, 
because it keeps track of multiple versions of rows that 
have been updated. You can use parameters to set the 
CommandText property of DataAdapter's UpdateCommand. The 
SqlParameter class contains a property, called RowVersion, 
that informs the DataSet which version of the value to make 
use of. The DataAdapter judges whether the update worked by 
considering whether the UPDATE statement have an effect on 
zero or a non-zero number of rows. If someone else has 
changed design (or deleted the row), the UPDATE returns "0 
rows affected." If no one else touched the row (that is, 
your optimism was justified), you receive "1 row affected."

One way to work around optimistic concurrency issues in ADO 
is to lock the records your DataSet retrieves as soon as 
the edit operation begins. This strategy is known as 
pessimistic locking. Long-duration locks generally lead to 
poor database performance and contention problems, but in 
situations where an application cannot tolerate having 
records changed while it is updating them, pessimistic 
locking may be necessary.

With ADO, it's relatively easy to use a pessimistic locking 
scheme. In ADO.NET, it's somewhat more difficult to set up, 
but you can still use pessimistic locking in your 
applications. However, just because you can do something 
doesn’t mean you should do it all the time—what I’m about 
to explain should be used only when absolutely necessary.


The basics steps for pessimistic locking are as follows:

1)Create a transaction with an IsolationLevel of 
RepeatableRead.

2)Set the DataAdapter’s SelectCommand property to use the 
transaction you created.

3)Make the changes to the data.

4)Set DataAdapter’s Insert, Update, and Delete command 
properties to use the transaction you created.

5)Call the DataAdapter’s Update method.

6)Commit the transaction.