Geek Answers Handbook

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

I get to dynamic data sites (remarkably powerful) and enjoy it. However, I have a requirement that I cannot meet. I have a separate category table (Id, Name, ParentId) for creating a hierarchical structure of categories (for example, categories with zero identifiers are the top level, the usual thing). Then I have a product table in which there is a foreign key.

Inside the dynamic data site, a category drop-down list automatically opens for me; however, it simply lists all the categories digitally. What I would like (and what I encoded earlier, preliminary data) is an indented list, a hierarchically ordered list of categories.

Replace existing FilterUserControl filter? Can I override the PopulateListControl method? Does anyone have any LINQ syntax for extracting self-contained hierarchical data?

Just pointers and tips will do, thanks for any help offered.

Yours faithfully,

Mike Kingscott

+5
source share
2 answers

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 can use IndentedNameor create custom formatting based on pseudocolumn LEVEL(it shows the depth of each category)

PS This is an idea to baduse NULLas the parent’s main identifier, since you cannot use the index to access it. Use instead 0.

Update:

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

Oracle, SQL Server NULL , NULL .

+2

SQL Server . , . http://msdn.microsoft.com/en-us/magazine/cc794278.aspx.

, , - . . , . db . , .. , , . , , , /.

+2

More articles:


All Articles