C#: How to lock a record in SQL Server?

[Wesam]

Hi guys,

In C# how can I lock a record (on SQL Server) while being processed by my
program so that I prevent other users from accessing it at the same time???

I'd highly appreciate your kind help,

Veszko

 

[Wells Caughey]

The best way that I've found to do this is to first open a transaction on
your connection and set that transaction object on your command objects.
Then in your T-SQL use the XLOCK and READPAST table hints. XLOCK will
exclusively lock every row that you read and READPAST will ignore all rows
that have already been locked so there won't be any blocking. Your query
will look something like:

select col1, col2, col3 from table1 with(xlock, readpast)

The exclusive lock held on the row will be kept until you commit or rollback
the trasaction. Just as a disclaimer, I am new to this too, and if anyone
else sees a problem with my strategy or a way to improve it, please jump in.

I hope this helps,
Wells