Displaying Parent / Child / Grandchild data

I am working on an ASP.NET site to display information about authors and books. I'm working on the Author page right now and I'm having trouble getting it to display the data I want.

I'm trying to do everything with declarative controls and as little code-behind as possible.

I have a datasource on the main page that selects the Author data and provides this to a FormView. Inside the FormView is another datasource that takes the AuthorID as a parameter and selects the list of books, which is provided to a DataList.

Inside the DataList I display some limited Book information. I also want to display some child items of the Book (grandchildren of the author), for example other authors who contributed to that book, or alternative titles. I have tried using datasources in each DataList row with a parameter set to the BookID, and it works just great, but the response time is just not acceptable. Each datasource of possible several dozen is making its own call to the database and it's just too slow.

If I use code I can grab all the data in one operation and use relations in the DataBinding event (I believe) to select the data I want, but this is a bit cumbersome and I expect will cause trouble if I want to eventually use an ObjectDataSource.

Incidentally, on a display-only page I use the XMLDataSource and it works great, but I need read-write access on the Edit page.

Is there a good way to do what I'm trying to do?

Thanks,

Graham

Here's an alternative:

Instead of putting the books in a datalist you could put them in a gridview. Handle the row selected event of the gridview and programatically bind only the 'grandchild' details specific to the selected book.

|||

That's probably what I'll end up doing, but I'm trying to avoid code as much as possible. In code it's pretty simple to avoid many trips to the database when fetching information, at the cost of some added complexity. I'm just looking to see if there's an equivalent way to do that declaratively.

It would be nice if you could bind to a datasource, specify a data member, and also throw in some selection criteria - filter it at the bound control, not at the datasource level. That's similar to what you can do with the XMLDataSource and XPathSelect.

|||

Yeah I see what you're saying.I've never managed to getsomething like that working entirely declaratively though with asp.netcontrols. Even so, if you just do the grandchild stuff programaticallyit should only be a couple of lines of code.

You could take a look at some third party grid controls made by ComponentArt, Infragistics, Telerik etc.

Display the total sum of rows found

I'm sure there is an easy answer to this question, it's just I couldn't find it in any of the books that I own.

If I do a SELECT statement that finds say 1000 rows that have been matched, is it possible to return the sum of rows found, i.e. 1000 and at the same time LIMIT the rows returned to 20.

Basiclly, I want to show the user 20 rows at a time but also the total number of rows found, i.e 1000. I hope I have explained things clear enough.

Many thanks in advance.I don't think it's possible in one statement. You have to run 2 statements:

select count(*)from table;

select * from table where rownum <= 20;

or you can have one additional column in your result filled with the count

select
(select count(*) from table),
t.*
from table t
where rownum <= 20;

or have one additional row, but you have to know how many columns you have in your result. e.g. for 4 columns

selce count(*), count(*), count(*), count(*) from table
union all
select * from table
where rownum <= 20;|||Thank you very much for your prompt reply.

With a little bit of tweaking, I managed to get the following statement to work exactly how I like. Basically, out of a table of 3000 rows, it returns 1300 rows, of which I want to display 20 rows only plus at the end of the table one extra row of the count (in this case 1300).

SELECT product, price from electronics
WHERE sid = '187'
LIMIT 20
UNION ALL
SELECT count(*), count(*) from electronics
WHERE sid = '187'

However, as I want to eventually do a sort on the 1300 rows returned, I think I would like to have an extra column of the count instead of an extra row of the count, i.e. 'product', 'price', 'count'. I know it's inefficient to have an extra colomn all with the same sum but I don't really want to do a sort and then mix the count in with it. A solution to adding an extra column with the count would be much appreciated.

Or, is it possible to have the above working select statement but with the count as the first row, and then somehow extract the first row using perl before I do a sort on the 1300 rows returned.

Thanking you again.|||I'm not sure, but I think, it could work when you first select count(*) then UNION ALL your select ordered by whatever. Do not order final result which includes count.

SELECT count(*), count(*) from electronics
WHERE sid = '187'
UNION ALL
SELECT product, price from electronics
WHERE sid = '187'
ORDER BY price DESC
LIMIT 20 -- I don't know if this goes before ORDER BY|||now I had a chance to test my last suggestion (previous post). You'll have to use something like:

SELECT count(*) product, count(*) price from electronics
WHERE sid = '187'
UNION ALL
SELECT product, price from
(SELECT product, price from electronics
WHERE sid = '187'
ORDER BY price DESC
LIMIT 20 -- I don't know if this goes before ORDER BY
) X;

otherwise most likely your count won't be in first line. the problem is some databases don't allow ORDER BY in views, subqueries, derived tables...|||Again thank you for your replies. Unfortunatly, I can't seem to get your examples to work. I've managed to come up with the statement below which does exactly what I want i.e. with the count as the first row, this is only possible because I've set the 2nd column of the count row to 0.00, and hopefully as there will never be a product whose price is 0.00 then when I do a ASC sort it should always become the first row.

Additionally, as the count is a number and is in the product column (products are normally described in words), a ASC sort should always put that as the first row.

Of course everything falls down if a product is described as a number or it's price is 0.00, rare but could happen.

SELECT product, price from electronics
WHERE sid = '187'
UNION ALL
SELECT count(*), '0.00' from electronics
WHERE sid = '187'
ORDER BY price ASC
LIMIT 20

I'm sure there is a proper solution, just haven't found it yet. What I really want is to have an extra column with the count in it, i.e.

Table has 3000 rows, rows returned from search is 1300

Product Price Count
--------------
JVC AV27D202 125.00 1300
Panasonic CT32SF37 150.00 1300
Pioneer PDP505HD 200.00 1300
Sharp 27NS100 250.00 1300
etc

instead of

Product Price
----------
1300 0.00
JVC AV27D202 125.00
Panasonic CT32SF37 150.00
Pioneer PDP505HD 200.00
Sharp 27NS100 250.00
etc

By the way, my database is MySQL. Many thanks.|||You could use negative price (like -1.00), and NULL description for your count, it should be on the top allways (row solutions).

did you also try something like:

SELECT
product,
price,
(SELECT count(*) from electronics WHERE sid = '187') cnt
from electronics
WHERE sid = '187'
LIMIT 20

this could be your "column" solution.
what DB do you use?|||My database is MySQL. Great idea about the negative price and NULL. I shall use the row solution if nothing else works.

The column solution however doesn't seem to work, although I can see what you are doing. It falls down I think because it doesn't like the syntax "(SELECT count(*) from electronics WHERE sid = '187') cnt" as a means of creating a third column. Both select statements work perfectly on their own but not together.

Many thanks.|||yes, I think problem is: MySQL doesn't support subqueries. Maybe it's not true in newer versions, but I'm sure this feature wasn't supported by some older versions