Hierarchical data in a drop-down list on Dynamic Data site

Question

Hello all,

I'm getting into dynamic data sites (remarkably powerful), and enjoying it. However, I have a requirement that I can't seem to meet. I have a self-joined category table (Id, Name, ParentId) to create a hierarchical category structure (e.g. the categories with null ids are top level, usual sort of thing). I then have a products table that has a foreign key to the category.

Within the dynamic data site, I have the category dropdown list automatically generated; however it just lists all categories in numerical order. What I would like (and what I've coded before, pre-dynamic data) is an indented list, hierarchically arranged list of categories.

Do I replace the existing FilterUserControl? Can I override the PopulateListControl method anywhere? Does anyone have any LINQ syntax for extract self-joined hierarchical data?

Just pointers and advice would do, thank you for any help offered.

Kind regards,

Mike Kingscott

Answer 1

In Oracle:

SELECT  LEVEL, Id, Name, LPAD(' ', LEVEL) || Name AS IndentedName
FROM    Categories
START WITH
        ParentID IS NULL
CONNECT BY
        ParentID = PRIOR Id
ORDER SIBLINGS BY
        Name

You may use IndentedName or make you custom formatting based on LEVEL pseudocolumn (it shows depth of each category)

P.S. It's a bad idea to use NULL as the top parent ID, as you cannot use index to access it. Use a 0 instead.

Update:

In SQL Server:

WITH    q (id, parentid, name, level, bc) AS
        (
        SELECT  id, parentid, name, 1, CAST(ROW_NUMBER() OVER (ORDER BY name) AS VARCHAR(MAX))
        FROM    Categories
        WHERE   ParentID IS NULL
        UNION ALL
        SELECT  c.id, c.parentid, c.name, q.level + 1, q.bc + '.' + CAST(ROW_NUMBER() OVER (ORDER BY c.name) AS VARCHAR(MAX))
        FROM    q
        JOIN    Categories c
        ON      c.parentId = q.id
        )
SELECT  *
FROM    q
ORDER BY
        bc

Unlike Oracle, SQL Server indexes NULL values, so it's possible to use a NULL to mark an ultimate ancestor.

Answer 2

SQL Server has a hierarchical data type. Haven't used it but looks interesting. http://msdn.microsoft.com/en-us/magazine/cc794278.aspx.

What I often use in these situations is a Left Right Tree. These are efficient for querying the data. Most importantly, I have a specific layer for loading and persisting my tree. This would grab all children from the db and load up the tree in memory. The layer would also be responsible for moving nodes within the tree, deleting nodes, ect. Once you have a tree organized in memory you can do everything from there. Hope that helps, let me know if you want some code samples of a generic left/right tree.