WFA - 从SQL Server C＃和TreeView

Question

我一直在使用SQL Server和C＃填充在Windows应用程序中TreeView控件：

CREATE TABLE [dbo].[myTable] 
(
    [ID] [int] IDENTITY(1,1) NOT NULL, 
    [title] [varchar](255) NOT NULL, 
    [parentID] [int] NULL, 

    CONSTRAINT [PK_myTable] 
     PRIMARY KEY CLUSTERED ([ID] ASC) 
) 

插入一些分层数据

SET IDENTITY_INSERT myTable ON 
GO 

INSERT INTO myTable(ID, title, parentID) 
VALUES(1, 'Microsoft', NULL) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(2, 'C#', 1) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(3, 'VB.net', 1) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(4, 'Open Source', NULL) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(5, 'Python', 4) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(6, 'Ruby', 4) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(7, 'PHP', 4) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(8, 'Perl', 4) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(9, 'Java', 4) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(10, 'LinQ', 2) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(11, '5.2', 7) 

INSERT INTO myTable(ID, title, parentID) 
VALUES(12, '4.4', 7) 
GO 

SET IDENTITY_INSERT myTable OFF 
GO 

定义根值

CREATE PROCEDURE viewMyTable 
AS 
BEGIN 
    SELECT ID, title, ISNULL(parentID, 0) AS parentID 
    FROM myTable 
END 

添加同桌关键contraint。

ALTER TABLE [dbo].[myTable] WITH CHECK 
    ADD CONSTRAINT [FK_myTable_myTable] 
    FOREIGN KEY([parentID]) REFERENCES [dbo].[myTable] ([ID]) 
GO 

ALTER TABLE [dbo].[myTable] CHECK CONSTRAINT [FK_myTable_myTable] 

而树视图中没有任何东西。谁能帮我？

enter image description here

enter image description here

Answer 1

希望这有助于你。

WITH f(ID, Title, Level, Sort) 
AS(
    SELECT ID, Title, 0 AS Level, ROW_NUMBER() OVER(ORDER BY ID) AS Sort 
    FROM myTable 
    WHERE parentID IS NULL 

    UNION ALL 

    SELECT B.ID, B.Title, A.Level + 1, A.Sort 
    FROM f A, myTable B 
    WHERE A.Id = B.parentID 
) 
SELECT * 
FROM f 
ORDER BY Sort, Level