Nested Query Results (DataReader woes)

GregStevens

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

kubben

Or option 3 use .net 2.0 where you can have multiple result sets open. I am guessing that probably isn't an option for you. So if you are stuck in .net 1.1 then I would go with option 1. Just have multiple connections. Since web apps use application pools as long as your connection string for each of the connection objects is the same you will use connection pooling. So your connections shouldn't get too out of control. If this data isn't going to change too much and isn't too big, I would think seriously of using caching to help out. Hope that helps. Ben

GregStevens

Thank you for your reply. My main worry is that this process will be called every time each page is loaded by every user. Does opening and closing three simultaneous connections on every page read incur bad performance hits if there are a large number of users? Also, you say "I would think seriously of using caching to help out" but I'm not sure that I understand what you mean. Can you give me a two-sentence summary, or point me in the direction of a website / reference where I could find out more? Thanks again!

kubben

There is a Cache object, that is similar to the Session or Application object. In this case your only options would be application or cache if you want the data to be the same across sessions (users). If the data is completely static and will not change, then you might want to just put it in the Application object. Otherwise you can put it in the Cache. Here is a Microsoft help on cache: http://msdn2.microsoft.com/en-us/library/system.web.caching.cache.add.aspx[^] Hope that helps. Ben

GregStevens

That does help -- thank you very much!

Jesse Squire

You don't mention what version of Oracle you're using, but the newer versions (9i and 10g, for sure... I think 8i) have built-in SQL extensions for dealing with hierarchical data. If you were to take advantage of them, it looks as if you may be able to reduce your processing down to a single query. Here are a couple of resources to get started with:

You may also wish to consider using the System.Data.OracleClient[^] for your connectivity, rather then using raw OleDB. The backing technology is the same, but the Oracle Client has been optimized for Oracle. Oracle also provides its own Oracle Data Provider for .NET[^], if you are more comfortable with that. Finally, for performance and security reasons, you may wish to consider moving your query into the database as a stored procedure. This will allow the DBMS to compile and cache the execution plan for your query, rather then having to figure it out each time you execute the SQL. Hope that helps. :)

--Jesse "... the internet's just a big porn library with some useful articles stuck in