>I was curious to know, how would one perform Pessamistic Table (Not Record) Locking using ADO.Net?
>
>I have searched high and low on the internet for some examples and I am coming up empty handed.
>
>Reason:
> I have an Client/Server application, where when a Client loads, it checks a table in the database to determine if an update to some data is needed. If so, it goes ahead and begins modifying another table in the database. Problem is that if two or more Clients load simultaneously, all the clients involved could be performing the same routine on the same Table/Records, which I am assuming might cause some problems. So I was hoping to find a way to immediatly "lock" a table so that only one Client is allowed to do the update, and all other clients seeing the lock, realize that they don't need to perform the update. Then the other Clients just wait until the lock is release before proceeding to read the table's data.
>
>Help....please...

Ben,

The techniques available depend on the database provider. ADO.NET is designed to let the database control it's own locks. From your description of the problem, it appears that the entire process can be encapsulated in a single stored procedure/function. If you declaratively begin and commit a transaction inside of the sp/function, the problem will be solved.

If you absolutely have to do it in multiple client-side steps, you can begin and commit a transaction in ADO.NET with:

Dim myConnection As SqlConnection = New SqlConnection(.......)
myConnection.Open()
Dim t As SqlTransaction = myConnection.BeginTransaction()

' Do database updates here

t.Commit()
myConnection.Close()

Keep in mind that a transaction may or may not prevent a user from viewing the data depending on the locking hints in the SELECT that they are running, but updates cannot happen to the same rows while the transaction is open.

Perhaps the problem is better solved by creating a static/shared class that performs the routine. Add a property called IsUpdating and check it before allowing the update to proceed. With this method you don't have to worry about database locking at all.