CREATE TABLE [dbo].[SAMPLECATEGORIES]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[parentid] [int] NULL,
[title] [nvarchar] (255) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
)
-------------------------------------------------------------------------------------------
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @ptrval binary(16)
BEGIN TRANSACTION
ALTER TABLE [dbo].[SAMPLECATEGORIES] DROP CONSTRAINT [FK_SAMPLECATEGORIES_SAMPLECATEGORIES]
SET IDENTITY_INSERT [dbo].[SAMPLECATEGORIES] ON
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (1, NULL, N'Category 1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (2, NULL, N'Category 2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (3, NULL, N'Category 3')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (4, 1, N'Category 1.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (5, 1, N'Category 1.2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (6, 2, N'Category 2.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (7, 2, N'Category 2.2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (8, 2, N'Category 2.3')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (9, 7, N'Category 2.2.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (10, 7, N'Category 2.2.2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (11, 10, N'Category 2.2.2.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (12, 6, N'Category 2.1.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (13, 3, N'Category 3.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (14, 7, N'Category 2.2.3')
SET IDENTITY_INSERT [dbo].[SAMPLECATEGORIES] OFF
ALTER TABLE [dbo].[SAMPLECATEGORIES] ADD CONSTRAINT [FK_SAMPLECATEGORIES_SAMPLECATEGORIES] FOREIGN KEY ([parentid]) REFERENCES [dbo].[SAMPLECATEGORIES] ([id])
COMMIT TRANSACTION
------------------------------------------------------------------------------------------------------
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [id]
,[parentid]
,[title]
FROM [master].[dbo].[SAMPLECATEGORIES]
Menu3.aspx
------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Menu3.aspx.cs" Inherits="Menu3" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TreeView
ID="TreeView1"
ExpandDepth="0"
PopulateNodesFromClient="true"
ShowLines="true"
ShowExpandCollapse="true"
runat="server"
OnTreeNodePopulate="TreeView1_TreeNodePopulate" />
</div>
</form>
</body>
</html>
Menu3.aspx.cs
---------------
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class Menu3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
PopulateRootLevel();
}
private void PopulateRootLevel()
{
SqlConnection objConn = new SqlConnection(@"Data Source=truefaster\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
SqlCommand objCommand = new SqlCommand(@"select id,title,(select count(*) FROM SampleCategories WHERE parentid=sc.id) childnodecount FROM SampleCategories sc where parentID IS NULL", objConn);
SqlDataAdapter da = new SqlDataAdapter(objCommand);
DataTable dt = new DataTable();
da.Fill(dt);
PopulateNodes(dt, TreeView1.Nodes);
}
private void PopulateSubLevel(int parentid, TreeNode parentNode)
{
SqlConnection objConn = new SqlConnection(@"Data Source=truefaster\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
SqlCommand objCommand = new SqlCommand(@"select id,title,(select count(*) FROM SampleCategories WHERE parentid=sc.id) childnodecount FROM SampleCategories sc where parentID=@parentID", objConn);
objCommand.Parameters.Add("@parentID", SqlDbType.Int).Value = parentid;
SqlDataAdapter da = new SqlDataAdapter(objCommand);
DataTable dt = new DataTable();
da.Fill(dt);
PopulateNodes(dt, parentNode.ChildNodes);
}
protected void TreeView1_TreeNodePopulate(object sender, TreeNodeEventArgs e)
{
PopulateSubLevel(Int32.Parse(e.Node.Value), e.Node);
}
private void PopulateNodes(DataTable dt, TreeNodeCollection nodes)
{
foreach (DataRow dr in dt.Rows)
{
TreeNode tn = new TreeNode();
tn.Text = dr["title"].ToString();
tn.Value = dr["id"].ToString();
nodes.Add(tn);
//If node has child nodes, then enable on-demand populating
tn.PopulateOnDemand = ((int)(dr["childnodecount"]) > 0);
}
}
}
output:
