Multilevel nested product categories display with asp.net and sql server?

I have a product category table with the following fields:

cat_id (PK)

Cat_name

Cat_desc

Parent_Cat_Id

Now when a user wants to add a product he should be able to select multiple categories from a listbox(multiselection enabaled). But to let the user know the hierarchy of the categories, I need to display them in开发者_开发知识库 the following style:

parent category 1

parent category 1->sub category 1

parent category 1->sub category 1->sub-sub category 1

parent category 1->sub category 1->sub-sub category 2

parent category 1->sub category 2

Parent category 2

...

I know that to achieve this I need to use recursive programming. But how can we do it via stored procedures? That would be much more efficient, right?

Thanks in advance for your kind help.

Here's what you want. This stored procedure uses a "CTE" or "common table expression". I'm not a pro at them but they're very usefull. Definatly recommend looking them up.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetRecursiveTest
AS
BEGIN
    ;WITh Tree (cat_id, cat_name, parent_cat_id, level, Sort) AS
    (
        SELECT cat_id, cat_name, parent_cat_id, 0 AS level, 
            CONVERT(varchar(255), cat_name) AS Sort 
        FROM RecursiveTest
        WHERE parent_cat_id = 0

        UNION ALL

    SELECT RT.cat_id, RT.cat_name, RT.parent_cat_id, Parent.level + 1 AS level, 
        CONVERT(varchar(255), Parent.Sort + ' -> ' + RT.cat_name) AS Sort
        FROM RecursiveTest RT
        INNER JOIN Tree as Parent ON Parent.cat_id = RT.parent_cat_id
    )

    SELECT Sort FROM Tree
    ORDER BY Sort
END
GO

Actually, if you change that last select to "selct * from tree" you'll get more data about the hierarchy.

Here's a link to a good resource for CTE's

Are you using Linq?

If you use LINQ i´d suggest adding a new property which just represents the category with all parentcategorys and use the recursion there, so it´s easy for you to access it in asp.net