Q: About Loading disconnected DataSets with Join statement...?

[Sky]

Hello:
Have a nagging question about creating disconnected DataSet queries...

If you have a table called INodes of 10,000 records, and a Table called
Clients of again 10,000 and a table called EditLog of 40,000 records...

And if you could, via a DataReader join these and get back a recordset of
about 50 records only...how are you suppossed to offer this in disconnected
manner?

In beginner books I see that I can still load one DataSet.Table with the
join query -- and Count =50, fine, but that doesn't give me the features of
a disconnected updating, when someone creates a new record (which has to
trigger updates/appends of all 3 tables) since it is really one table.

Then the other method is to load all 3 tables into separate tables in a
dataset, and create Relationships between them... but that seems nuts if we
are talking about an in-memory DataSet. This would mean I have 60,000
records to add/update to from a pocketpc?!!!

Any help on doing this would be -- a godsend! Thanks.
Sky

 

[Miha Markic [MVP C#]]

Hi,

Hello:
Have a nagging question about creating disconnected DataSet queries...

If you have a table called INodes of 10,000 records, and a Table called
Clients of again 10,000 and a table called EditLog of 40,000 records...

And if you could, via a DataReader join these and get back a recordset of
about 50 records only...how are you suppossed to offer this in disconnected
manner?

In beginner books I see that I can still load one DataSet.Table with the
join query -- and Count =50, fine, but that doesn't give me the features of
a disconnected updating, when someone creates a new record (which has to
trigger updates/appends of all 3 tables) since it is really one table.

Then the other method is to load all 3 tables into separate tables in a
dataset, and create Relationships between them... but that seems nuts if we
are talking about an in-memory DataSet. This would mean I have 60,000
records to add/update to from a pocketpc?!!!

You can easily limit the records number in select sql command or in Fill
method (via a parameter).
So you might load only those records, into three separate tables, that you
need.

 

[William \(Bill\) Vaughn]

The ability to fetch two, three or more tables and "join" them on the client
in a disconnected (updatable) DataSet is interesting, but only when there
are not many rows to pull to the client AND there are few if any additional
users that would cause update collisions and other locking issues.
For "real world" production databases most folks take another approach. One
approach is to JOIN the data on the server (as you have done) and use a
DataAdapter to execute the query and populate a DataSet table. This is
usually done with a parameterized SP to improve performance and focus the
joined rowset. Once the data has arrived, you'll find it's tough to update
the specific tables using the DataAdapter Update method because you'll have
to write a focused (one table) UpdateCommand command to target one specific
table at a time. This means you'll have to roll your own Commands to perform
the changes. This is not that hard as you're basically setting up calls to
SPs that do the actual work (server-side).

Another approach is to create one DataAdapter for each target table and
program these to handle the UPDATE (INSERT and DELETE) operations on the
specific target tables. This way you can use a single DataAdapter to fetch
the rows and populate the "N" DataTables as necessary. At this point you
program ADO.NET to identify the relationships between the tables so you can
navigate and expose parent/child functionality. When it comes time to
Update, you "just" (I hate that word) execute the DataAdapter Update method
for the parent and child tables (based on their relationships) in sequence.

I think you'll find that the more advanced books (mine included) discuss
this issue in more depth.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William Ryan [eMVP]]

Sky:

1) You would only have to update however many records were changed
regardless of the number of records that you have in the DataTables. Assume
you had 1 million in each table and only made one change/or added one
record...all that would be updated is that one record.
2) Regardless of what method you choose, if you have 60,000 records in your
DB, that's not going to be pretty run on a PPC. Moreoever, it's unlikely
that they'd ever need 60,000 records, but again the problem is that while
they don't need but a subset of them, you don't know which subset they'll
need in advance.

3) From the sounds of it though, you are only dealing with a subset of 50
in most cases? Is that correct? If so, you could query against the parent
most table first, bringing (50 or however many records you needed assuming
it's less than 50k). So let's say you have 50 . You could then fire a
query on the other two tables, filtering only the records that match these
50 records. This would give you markedly smaller record sizes (Unless you
have 1000 child records per parent). All you need for a datarelation to
work is the same DataColumn type and the existence of a parent record for
each child. So if you only have say 30 records for each parent record and
you pull over 50 parents, you could filter the subsequent queries and then
use the DataRelation and only have 1,500 records..which still isn't the
lightest thing in the world but much more reasonable.

Another thing...if you do use a Join and a datareader, you're pulling back
more data than necessary in most every instance b/c of the redundancy. So
if you can filter the parents in this manner to use only 50 records, the
same can be done with a datarelation and a properly constructed filter for
queries 2 and 3.

HTH,

Bill