VB.NET DataAdapter question

  • Thread starter Thread starter Eric A. Johnson
  • Start date Start date
E

Eric A. Johnson

I'm using ADO style. I have an Access database with four tables that I will
want to do queries on. I will want to do queries on all four tables, at
least individually, and possibly at the same time (using "INNER JOIN"). My
question: Is it better to have four individual DataAdapters, one for each
table, with the DataSet linking to a different one for each query; one
single DataAdapter, that would connect to all four tables at once; or some
kind of combination of the two? Any tips? As you can tell, I'm rather a
newbie at databases.

Thanks,
Eric
 
Hi,

I hope that I understood you question correctly, If no please correct
me, so here is the story:
one
single DataAdapter, that would connect to all four tables at once;
Click to expand...

If you have a dataset with several tables, you can't do an inner join
in the query and let the adapter Fill method to distribute the results
between the tables, you probably ask why can't I ? Because the
DataAdapeter treats the data retruned from the db as a single table so
it wont distribute it between diffrent tables.
Is it better to have four individual DataAdapters, one for each
table, with the DataSet linking to a different one for each query;
Click to expand...

Probably this is the way to go, at least it will work correctly.

Hope it helped.
 
Eric,

Will you be so kind next time not to multipost. Crossposting to more
relevant newsgroups in one time is not any problem at all.

Than at least I would have seen in the language.vb newsgroup the answer from
Alex and not have taken time to explained that part anymore.

Thanks in advance.

Cor
 
Eric:

The answer to your question lies in part on the type of databases you are
using. From my understanding, Microsoft Access does not support batching
whereas sql server and oracle for example do. So if Access is the database,
then I don't think it's an option. Assuming that such option was available.
For select statement, then there is benefit to batching the select
statements. You will need to possibly set TableMappings and columnmappings
if they don't match (the names) but if tablenames and colum names match,
then there is no issue. However if you only had one adapter for everything,
then you'd need to change the update/insert/delete commands for each update
which is not easy on the eyes to read. So all in all, having separate
adapters is best. Also, because of relations, the order in which you update
is most important for you do not wnat to commit constraint violations. If
you use datarelation in dataset between table, then constraints can be
enforced client side which is much good.

One thing you do not want to do though is join the table at server IF you
have to update. For read only situations, it is ok, but if you must update,
then it can be as they say, nightmare. Why? Because you can not use
commandbuilder , which is not all that good. You can not use configuration
wizard - which only matters if you don't have real data layer. But doing
what they call 'rolling your own' update logic is very very hard with joined
table - whereas doing it with separate tables and separate adapters is
simply matter of calling update on each adapter and passing in each
datatable to appropriate adapter.

For me, I would create four adapters, one dataset and four tables with
relations between them. Then, when update comes, start with parent most
table, call update on it and so forth down the ranks through children -
unless not all tables are related, if they aren't, then order of update does
not make difference for them.
 
Back
Top