August 27, 2009 by Christoff Truter C#
Populating a TreeView control in a windows application using SQL and C# is quite straightforward.
Create a self referencing table, lets call it
myTable, quite orginal don't you think? ;)
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 )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
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
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]
protected void PopulateTreeView (TreeNodeCollection parentNode, int parentID, DataTable folders) { foreach (DataRow folder in folders.Rows) { if (Convert.ToInt32(folder["parentID"]) == parentID) { String key = folder["ID"].ToString(); String text = folder["title"].ToString(); TreeNodeCollection newParentNode = parentNode.Add(key, text).Nodes; PopulateTreeView(newParentNode, Convert.ToInt32(folder["ID"]), folders); } } }
Very good December 16, 2019 by mojtaba
Thanks It helps me