Hi all,

I am trying to convert an existing application from ASP to ASP.NET. In a number of places GetRows() is used to gather information from a database. Using Loops and Nested Loops data is presented.

My question: Using a code-behind page, how would I go about "iterating" through a database of categories where the category is first displayed on one line and its associated child categories on the line beneath.

Ex:
Parent
Child1, Child2, Child3

The DB is set up to have the Parent ID associated to all of its children.

I am sorry for the rather obscure and all-encompassing question. I have searched the net for a few hours trying to find a solution and came up empty.

Regards,
Eric

Your fruitless search is understandable as most ASP.NET applications focus on databinding for such iterations thru datasets. You should look into this concept as this is more optimized and does all the iterating for you. But if you do want to iterate thru the rows on your own, here's what you need to do...

When you want to read some data in a forward-only, read-only mode (this is fastest and usually all you need to do) you can use a DataReader object. You need to use a data connection object and a command object (which you use depends on the data provider: MSSQL, ODBC, etc.). You can execute the query and get back a DataReader. You then use a loop like this to iterate thru the returned data:

While DataReader.Read()
   'Do what you need here
   'Access the columns with the DataReader object
Loop

This is the basic idea.

A more advanced concept is to use a DataSet and nested bindable controls. The DataSet class allows you to have several tables in it. These tables can be related in much the same way your data is related within the database. Once you have a related set of tables, you can utilize data binding to create repeatable templates that will do all the grouping that you had to do in ASP manually. You can use one of the data listing controls to list your Parent rows, and within it you can have another listing controls that lists the child rows. You tell the inner listing control to use a subset of the data for its datasource. This datasource is established by the relationship between the current parent item and the matching children in a similar fashion to a database query.

Peter
------------------------------------------------------
Work smarter, not harder.

Peter,

Thank you for the reply. I have seen some information regarding Datasets, and it seems each post I read always comes back to it being a resource intensive operation. Is this is only method available, that you know of, in ASP.NET to iterate through a set of data that is cross-referenced on other data?

Eric

The DataReader is (I am pretty sure) the simplest data interface class you get with ADO.NET. It's readonly and forward only. It's "connected" unlike a dataset, so you have to read from the database, do something with the data and close the database connection. It's very efficient for "firehose" data operations like what you need.

If you get a "flat" result set like this:

Parent1 Child1
Parent1 Child2
Parent2 Child3
Parent2 Child4
Parent3 Child5
Parent3 Child6

then you need to manually code the tests where you figure out when you have reached a new group (i.e. a new parent). This is how we've had to do it in ASP in order to eliminate making additional calls to the database for the "children" rows and it worked just fine. The dataset eliminates the need for this extra logic. All the testing and checking and additional variables can usually be boiled down to one or two lines of code which end up being the equivalent of "select this item's child rows".

.NET is much more streamlined and what might be considered a more resource intensive process might not actually be because all the code is compiled and the objects used are faster. If you got down to the nitty gritty numbers, you might actually find that doing all those string comparisons to determine your grouping might actually be more resource intensive than using the native objects and functionality of related dataset tables. Plus, using the dataset allows you a tremendous amount of flexibility with what types of controls you use on the UI side of the page. I have seen WAY too many cases where people have not taken advantage of the ASP.NET server controls and their databinding capabilities and the end result is just the same spaghetti code that we are all to familiar with from ASP. Particularly when it comes to building UI elements. If you have to manually go thru that flat data to determine the "next group" you usually also need to build all the UI elements on the fly, which can get very messy very quickly with anything but a very simple UI.

Take some time to learn a little bit about the data listing controls (DataGrid, DataList, Repeater) and how they can use databinding. I know there are some threads on these forums that have some discussions and code examples of this. I think you'll find very quickly that the "old way" is very cumbersome and more difficult to work with.

Peter
------------------------------------------------------
Work smarter, not harder.