Better way to build results? (Relative newbie)

[Terp]

Hello,
Undoubtedly this answer is in front of my nose, but I can't figure it out or
find it anywhere.

Using VB.NET, Access database.

I have successfully built a series of nested queries that return results
from three interrelated tables, reading the results into datareaders. I can
iterate through the three datareaders and build the results that I want, but
I suspect there are more efficient ways to do what I need. The only way I
have been able to get this to work is by establishing one connection for the
first query, and then in a nested loop keep making connections to satisfy
the second query, and within that loop there is another loop that keeps
making connections to satisfy the third query. This works fine on my very
small test database, but I know users will have much larger ones. I can't
use UNION, because the tables have differing numbers of fields.

Let's say I have three tables: Neighborhoods, Houses, and Residents (not my
real database!). Neighborhood.nID = Houses.nID and Houses.hID =
Residents.hID. The trick is that I might not have houses for every
neighborhood, and I might not have residents for every house. But I need to
get all the neighborhoods, even the ones without houses, and I need to get
all of the houses, even the ones without residents. Conversely, every
resident has to have a house, every house has to have a neighborhood.

So my simplified code is:
Establish first connection
Get all the neighborhoods
Read the first neighborhood
Build a node for a TreeView
Establish/reestablish second connection
Get all the houses in that neighborhood
Read the first house
Build a node for a TreeView
Establish/reestablish third connection
Get all the residents in that house
Read the first resident
Build a node for a TreeView
Loop
Close third connection when we've found all the residents for
that house
Loop
Close second connection when we've found all the houses for that
neighborhood
Loop
Close first connection when we've found all the neighborhoods

Any and all advice gratefully received.

Thanks,
Chris

 

[Guest]

Hi

why don't you use views in Database to get the required result and simply
run a single statement in the front end to grab the result.

cheers
binod

 

[David Browne]

Hello,
Undoubtedly this answer is in front of my nose, but I can't figure it out
or
find it anywhere.

Direct your nose to System.Data.DataSet. And preferably use a stongly typed
DataSet.

This is an in-memory set of related DataTables.

You can load up all of your Neighborhoods, Houses and Residents and then
navigate among and between them at your leisure.

http://msdn.microsoft.com/msdnmag/issues/04/12/DataPoints/default.aspx

http://msdn.microsoft.com/library/d...ref/html/frlrfSystemDataDataSetClassTopic.asp

http://msdn.microsoft.com/library/d...tml/cpconusingannotationswithtypeddataset.asp

http://www.ondotnet.com/pub/a/dotnet/2003/03/31/typeddatasetannotations.html

David

David

 

[Terp]

Thanks, folks. I'll look into those suggestions. After I posted my plea
yesterday, I stumbled on a way that is either stupendously stupid or quite
simple, I don't know which. I realized that I could use LEFT OUTER JOIN on
the ID fields to get everything from every table (which is what I need to
build the TreeView). Some records have just a neighborhood, some a
neighborhood and a house, some neighborhood, house, and a resident. There is
one record for each resident, which may also have a unique house definition
and a unique neighborhood definition. The recordset is 40 fields wide. I can
parse each record to figure out if I have a new neighborhood and/or a new
house and/or a new document. It's one quick call to the database. No messy
datasets or other things I don't know about yet. I have this working with my
sample database, which covers all the possibilities.

So is this approach stupendously stupid or just nice and simple?

Thanks,
Terp