Data changing while reading multiple tables to a disconnected DataSet.

Apr 2 2008 11:58 AM
I am a newbie when it comes to a concurrency issues for data access.  There is a lot of documentation on handling concurrency when updating disconnected data but what about when you are filling a DataSet with several tables?

Let's take a simple example.  Suppose we have a database with 2 tables: TblFamily which stores info about each family (e.g. family_id, family_name, etc), and TblMember which holds info about each of the members in each of the families (e.g. person_id, person_name, family_id, etc).  No family can have zero members. 

We want to retrieve info on a particular family and its members to a disconnected DataSet. From what I've read, most books/articles advocate filling the DataSet table by table. So,

Step 1: read one record from TblFamily to a DataTable in my DataSet. This info includes the family_id.

Step 2: use the family_id to retrieve a subset from TblMember of the members for just that family, filling a second DataTable in the same DataSet.

But now suppose the family and its members are deleted from the database between Step 1 and 2.  After step 2, my DataSet will be inconsistent.  I have a family with zero members. 

Granted, in this simple example, I could test for such a case. Or perhaps use a join query, which, from what I understand, would be an atomic operation and so preclude the problem from arising (*Is this true?*).

But what about more complicated situations?  In general, what are the common approaches to handle/prevent data changing in related tables, while you fill a DataSet?

Can anyone point me to any books/papers/info on this?

Thanks,

Charles.


Answers (1)