Multiple tables in DataSet - various questions...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have five related tables in SQL2K, with FK relations between them. I would
like to load all five into a DataSet, read AND update them, then sync the
changes to SQL2K. In effect, I would like to replicate the entire database
into a DataSet (it will always be a small database, so memory isn't an
issue)...

But can a DataAdapter handle multiple tables? It appears that I can use a
batched SELECT for the SelectCommand (SELECT * from Table1, SELECT * FROM
Table2, etc), so maybe one DataAdapter can (perhaps) Fill the DataSet with
all five tables, but what would the update/insert/delete commands look like
for a "single DataAdapter" solution?

On the other hand, if I use a separate DataAdapter for each table, then how
do these multiple data adapters synchronize their activities? (like when I
add a parent row to one table and a child to another table... how do the
DataAdapters know in what sequence they must do inserts to the database, or
is this a non-issue).

And a related question: I'm not getting all the constraints populated into
the DataSet when I Fill from a table. The Primary Key constraint gets
defined, but not a Unique Constraint that's also defined on the table (I have
MissingSchemaAction = AddWithKey)

DT
 
The DataAdapter is intended to handle a table at a time. If you wish to call
a single method when you fill or update the DataSet you should probably
create a component containing all the 5 DataAdapters and publish a Fill and
Update method for the component that subsequently calls all five fill and
update methods (respectively) for the DataAdapters.

Also the DataAdapter does not know about parent-child relationships, so you
should run the update methods in the right order from the lowest child to
the last parent. The last update should be a table without parents.

I think you need to setup the unique contraint manually in the DataSet
designer.
 
If I'm adding a related parent and child row, then running the update methods
from the lowest child to last parent will get an exception on the child,
since the parent row doesn't yet exist. Shouldn't I run the updates from
parent down to lowest child? Or am I really confused? (which certainly could
be the case!)

DT
 
Ehhh, yes, you are right, i was the one switching things around, i
apollogize.

But the idea is that you have to avoid this exceptions manually.
 
Except when you delete parent records. In this case the way i described is
the one to use.
 
Back
Top