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 StevensHi,
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.
--
If my answer helped you,then please do press Yes below.
Thanks and Regards.
Manish Bafna.
MCP and MCTS.
"Greg Stevens" wrote:
Quote:
Originally Posted by
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
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<TreeNodenodes, 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);
}
}
}
Greg Stevens wrote:
Quote:
Originally Posted by
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
--
G?ran Andersson
_____
http://www.guffa.com
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.
"Greg Stevens" <GregStevensCA@.gmail.comwrote in message
news:2E05CBCD-0F6B-444D-BF5D-8DA24C5895B3@.microsoft.com...
Quote:
Originally Posted by
I am connecting to an Oracle database using an OleDbConnection. I am
using
Quote:
Originally Posted by
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
Quote:
Originally Posted by
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='"
Quote:
Originally Posted by
& 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
Quote:
Originally Posted by
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
0 comments:
Post a Comment