Moko,
This is very good question. If you use ADO.NET then Optimitic Locking is
the ONLY choice I recommend using.
Concurrency Management is two fold:
1. Detecting
   a. Primary Key + all the fields
   b. Primary Key only - last wins
   c Primary Key + fields that been updated
   d. Primary Key and Timestamp or Rowversion
2. Resolving
   a. On the Server (Automatic)
   b. On The client (Manual)

ADO.NET provides this functionlity with Data Adapter and Dataset (Data
Mapper Pattern - http://ipattern.com/simpleblog/PermLink.aspx?entryid=14) .
DataSet object can contains Original and Current Version of values per Row,
so conflit resolution will happen on Per row basis.

You can use CommandBuilder or Data Adapter Wizard that will generates SQL
for you with some limits because It does not support BLOB in WHERE clause;
therefore you have to write your own SP or SQL to support  (D) option of
detecting.

I hope this provides you at least with a some guideline.

Maxim

[www.ipattern.com do you?]