record locking with DBF (visual basic) table in ADO OLEDB in VB.NET

[JohnR]

Hi, in VB.NET I'm accessing a DBF file using ADO with an OLEDB connection
with the Visual Foxpro OLEDB driver (VFPODBC.DLL). Read, write, update,
delete all work fine using a dataset.
Is there any automatic way to handle concurrent updates to the same record
by different users. How would it work? If I had to do it manually I'd use
a "last updated" timestamp and do something like this:

step 1. Read record without lock
step 2. modify record
step 3. read same record with lock
step 4. compare last updated timestamps
step 5. If they match then write the record and release lock
step 6. if they don't match then release record lock and disp error msg to
user

If I did this manually, I still don't know how to do a "read with record
lock" to protect the record during that small window between steps 3 and 5.

Right now I'm using a dataset to do the update/delete/insert operations then
updating the database with a DataAdapter.Update method. I'm open to using
another technique if it's required to protect the records from concurrent
updates.

I heard about something called "pessimistic locking" and "optimistic
locking" (or something like
that). Would that apply to my case? If so how would it work?

I can't imagine this is an unusual requirement... I've never had a problem
figuring out
record locking in any other programming environment... .NET is very
powerful, but
it seems like some of what should be easy things, are not very obvious!

Thanks, John

 

[JohnR]

Sorry for the duplicate post.... got an I/O error on the first one and
thought it didn't get thru

 

[Cindy Winegarden]

Hi John,

I don't have time to play with this right now, but you can wrap your update
in a Transaction and within the Transaction you could do all your checking.
I answered a question about Transactions recently; you can read it at
http://groups-beta.google.com/group..._frm/thread/be1e5a8924f9247b/c7946cd42abf9a96.

As for optimistic and pessimistic locking, if you're referring to those
terms in Visual FoxPro, then pessimistic locking locks a record from the
time you access it until the time you release it, and optimistic locking
only locks the record when it is actually being written. In this case, if
another user has modified the record between the time you accessed it and
the time you actually wrote your changes, then "the last guy wins" as far as
which updates are retained in the end.

The VFP documentation says that the VFP OLE DB data provider supports the
standard DBPROP_LOCKMODE and DBPROP_LOCKMODES properties. Sorry to say I
haven't ever worked with those.

This is an interesting question - now you've got me curious to learn exactly
how it all works.

--
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
(e-mail address removed) www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

 

[JohnR]

Hi Cindy,

Thanks for pointing me in the right direction. My goal for next week is
to study the OLEDB documentation. Right now, i've been using oledb on the
highest level and it worked well, I just didn't get into the guts of it....
now I will....

John

 

[JohnR]

Hi Cindy,

You have been so helpful, I wanted to make sure you had the information I
uncovered in my research. As I mentioned previously, I wanted to stay with
the vanilla ADO.NET techniques so that they would work with basically any
database I chose to use... so the specific VFP lock commands would not work
for me. Following is the information I posted to another user I was
conversing with...

Well, I think I've got it all figured out...After much research and reading
of MSDN articles, I have determined the following:

1. there is NO automatic concurrancy checking. the closest visual studio
comes to it is if you use the data adapter wizard to "generate sql
statements" and, under the advanced tab, you select "use optimistic
concurrancy". What this does is simply generate a UPDATE SQL statement that
compares the prior value of all the fields to the current value in the WHERE
clause. If any field has changed the update does not occur (ie: zero records
are updated). This corresponds to the "whole record" method of concurrancy
checking. The other method is to compare last updated timestamp, or some
sort of version number for the record. (ie each time you update a record you
either update the timestamp, or increment the version by 1). The important
thing to realize is that this is basically all a manual method of doing
concurrancy checking. If you don't use the wizard (I never do) then you have
to code some sort of concurrancy check into your update statement manually.

2. there is an exception called "DBConcurrencyException" which, by it's
name, seems to indicate that it will catch concurrency violations... WRONG.
Reading the documentation for it, this exception will be thrown "whenever 0
records are affected" by an sql statement (ie an update statement). Reading
the documentation reveals that "when 0 records are affected it is PROBABLY
due to a concurrency violation" (which YOU have coded in your WHERE clause).

So, the bottom line is this... if you want to catch a concurrency violation
you have to code it yourself using your own methodology. The only tool
ADO.NET gives you is to throw a DBConcurrencyException when 0 records are
affected by an SQL statement. Everything else is up to you to code. Now, I'm
not saying that this is necessarily bad, I'm just saying that it would have
been nice is MS didn't dance around the issue and make it PERFECTLY CLEAR
that there is no automatic concurrency check.

I believe the above statements are correct to the best of my knowledge. I
provide no warrantee, guarantee, or represent that I have any brains at all.

Regards,
John