Looping nested queries? (DataReader woes)

[Guest]

I am connecting to an Oracle database using an OleDbConnection. I am using
DataReader objects to get query results. However, this limits me to only
having one reader open at a time, which is a problem for one of the
operations I am doing.

I have a table with hierarhical data (only 3 levels deep) that I would like
to parse through in a depth-first search. Each row in the table in the
database has a Name and a ParentName, and I'm doing something like this:

oCmd1 = New OleDbCommand( "Select Name from tblTree where
parentName='_top'", oCxn )
oRdr1 = oCmd1.ExecuteReader()

While oRdr1.Read()
sName1 = oRdr1("Name")
oCmd2 = New OleDbCommand( "Select Name from tblTree where parentName='"
& sName1 & "'", oCxn )
oRdr2 = oCmd2.ExecuteReader()

While oRdr2.Read()
' do the third level, you get the idea
End While
oRdr2.Close()

End While
oRdr1.Close()

The problem is, it won't let me use oRdr2 while oRdr1 is open; and I can't
close oRdr1 because it is still in the middle of cycling through the
top-level items.

SO, my question is this: What is the PREFERRED way of dealing with this?

The options seem to be:

1) create a new DB connection for each level in the hierarchy
2) move to a different kind of object (DataSet?), instead of a DataReader
3) ??????.... some other option I haven't thought of?

I don't know which approach would be best, or if there are other approaches
I'm not thinking of. Any help or advice is greatly appreciated.

Also, I apologize if this question would be better suited to a different
forum. Please just let me know, and I'll take it there.

Thanks!
--- Greg Stevens

 

[Guest]

Hi,
i would suggest you to use dataset and then define parent-child
relationships between tables.

For example:ds.Relations.Add("CustomerOrders",
ds.Tables["customers"].Columns["CustomerID"],
ds.Tables["orders"].Columns["CustomerID"]);

You can search for following terms(creating parent-child master-details
relationships ado.net c#) in any search engine and you will get further
samples/code.

Hope this helps.

 

[Guest]

Read all the data that you need from the datbase using one query, and
put it in a data structure that you can work with, like a list of custom
objects. This way you won't have to make a lot of querires to the database.

With a recursive function you can easily handle the tree without
repeating the code for each level. Example:

public void ReadLevel(List<TreeNode> nodes, string level) {
foreach (TreeNode node in nodes) {
if (node.Parent == level) {
' add the node to the tree:
' ...
' handle children to this node
ReadLevel(nodes, node.Name);
}
}
}

 

[sloan]

I don't think you can do it that way.

I have an example of

Customers and their Orders at my blog on using the
(IDataReader.NextResult()

I haven't done Oracle development in over 3 years, but I believe the
interface definition of
(IDataReader.NextResult()
is there. You might have to figure out how its used exactly.

Anyways....... the example is at:

http://sholliday.spaces.live.com/blog/
5/24/2006
Custom Objects/Collections and Tiered Development

Since I use an IDataReader (the interface) it should matter that I happen to
use Sql Server as the rdbms, and you're using Oracle.