Sql server row lock

I have developed an app with c# where a form displays a few details o开发者_JS百科f a employee in a listview control. When a user clicks on a row, then another form will open and show that employee's record with more detail.

I want it to work so that a user will see the form where employee records are shown in more detail, that that record will be locked in such a way that another user will not be able to see that record until the original user closes the detail form. Please guide me how I can design this type of app with SQL Server lock.

A preferred way would be...

You could add a timestamp column to the table and compare it to the incoming update.

If the user tries to update the data and the timestamp is different, alert the user that the data has changed and refresh the screen.

A non-preferred way would be...

Add a userEditing column to the table, and set it to the user who is working with the row (like a checkout). Hide this row from any user that doesnt have it "checked out", and release it when they are done.

This can become problematic for you in many ways (Joe user locks a row, is out today, and Jane needs it now), but can be the appropriate solution in some cases.

For row lock you could use:

BEGIN TRANSACTION
UPDATE someTable set SomeThing = 'new value' where someID = 1
-- this will lock 'someTable' in affected row as long as transaction alive.

-- in another connection
select * from someTable with (readpast)
-- this will skip locked rows

but keep in mind that this is not a proper way to implement.

• The user might panic and blame system for losing data or even say it's a bug.
• When you lock a row, keep in mind that it will lock until the connection/transaction is timed out or until the user commits that row back.
• No connection pooling.
• Bad idea for web app, due to remote connection.
• Any query involving someTable without a with (readpast) statement will have to wait.

Just let the user 'view' only in editing row. In ASP.NET for caching editing row:

Application[string.Format("{0}.{1}", tableName, primaryKey)] = true;