Fill a strong typed dataset from 2 tables

[Guest]

Hello folks,
i am searching at this forum, but i don't found a solution.
I try to fill two tables of a strong typed dataset by
ONE call of fill-Method of the dataadapter. Is there
any possibily to do this?

Table Customer contents name, titel ... primary key is IDCustomer
Table Adresses contents street, city ... primary key is IDAdress

I have a join table with foreign keys into
Table Adress2Customer content CustomerID and AdressID

I generate a dataadapter with the following select statement

select * from Adress2Customer inner join Adresses
on Adresses.IDAdress = Adress2Customer.AdressID
where Adress2Customer.CustomerID = 1234

to get all adresses from Customer 1234

At the moment I use 2 statements with different select parts
select Adresses.* and select Adress2Customer.*

and two dataadapters to fill my dataset.

OleDbDataAdapter1.Fill(myDataSet, "Adresses")
OleDbDataAdapter1.Fill(myDataSet, "Adress2Customer")

I expect this style of reading dta force two querys running at
the database.
Because of the size of the Tables it is impossible to read all data into
memory to etablish the relationship there.

Is there any possibily to avoid the second database reading?
Is there a way to fill two tables of a dataset from one sql-Statement?

Thanks in advance for any help.
Niels

 

[W.G. Ryan - MVP]

Nieurig:

If the database supports batch queries, then yes. You simply fire both
select statements as one, then use the tableMappings (if the table name in
the select statements are different from the DataSet table names) and
possibly column mappings (again, only if the names in teh query are
different from the DataSet's table names).

When you call Fill on the adapter, it will fill both. However this is only
if your db supports batch updates. Oracle and Sql Server will, Access won't
(at least I don't believe it does).

Just for the record, if you use a DataReader, you can use NextResult() to
get to the next resultset if you ever batch queries and use a reader. The
adapter will handle this for you automatically provided that the table names
are the same and/or you have TableMappings in place.

HTH,

Bill

 

[Guest]

Hi W.G. Ryan,
thanks for your assistance.
i use firebrid and it will support batch querys ...
but i need to run the same query two times only at a batch.

That look suboptimal.
Any ideas?

Niels

 

[W.G. Ryan - MVP]

If you have batch query ability, then a Sql Statement of this ...

Select * from MyFirstTable; Select * From MySecondtable.

call fill on the adapter once with this statement (or whatever separator
Firebird uses). If the table names in the dataSet are MyFirstTable and
MySecondTable then all is good (although if columns don't match, you need to
specify column mappings). Otherwise just specify a TableMapping too - that
should do it for you.

 

[Cor Ligthert [MVP]]

Nieurig,

What is your goal with this. Two seperate fills to fetch the data with two
select clauses including the same where clause will certainly needs less
effort on both sides than a Join statement that has to be processed. The
tables are read row by row using the datareader.

Just my thought,

Cor

 

[Guest]

Hi Cor and the others,
thanks for your comments.

What is your goal with this. Two seperate fills to fetch the data with two
select clauses including the same where clause will certainly needs less
effort on both sides than a Join statement that has to be processed. The
tables are read row by row using the datareader.

No i don't think so.
I need to join the tables at the database because I only know the IDCustomer.
To get the adresses from one Customer i need to join the tabels two times,
first for reading the adresses, second for reading the linktable with the
foreign keys.

Sending two select was only a workaround . Instead of calling the query two
times i like to call it only once --- but need to fill the two tables of my
dataset.

OK. Perhaps i need to write my own code for doing that from a datareader
read the query result in a "normal" dataset an copy the data from it.
Is there any better idea?.

Thanks
Niels

 

[Cor Ligthert [MVP]]

Niels,

You are right, I misunderstood you (and misread). I see still see nothing
wrong in sending two seperated selects instead of 1, however. I assume that
you make it than only more complex on your database server.

You needs at your client side a dataset containing 2 tables. One with client
information and one with its address information. What is wrong with it to
tell the server to give that in two steps. The datareader is as well
processing row by row from the fetched resultset, I assume that the
transport from Server to Client will consume the most time.

Just my thought,

(I am curious, is nieurig a shortcut for nieuwsgierig?)

Cor

 

[Guest]

Hi Cor,

You needs at your client side a dataset containing 2 tables. One with client
information and one with its address information. What is wrong with it to
tell the server to give that in two steps. The datareader is as well
processing row by row from the fetched resultset, I assume that the
transport from Server to Client will consume the most time.

Well I will use this approach and build my own datareader-class to support
this.

(I am curious, is nieurig a shortcut for nieuwsgierig?)

No, it is build from parts of firstname an my familyname --- but is sounds
like "neugierig".

Good night.
Niels

 

[Ivailo]

Thank you for the answer. It is very helpful.
I just want aks you if you know how to use the same with DataSet designer?

Is it possible to add TableAdapter in DataSet designer that loads two tables
from one StoredProc?

Thanks,
Ivailo