Blog Post by Murat Yaşar
Posted on Sunday, May 01, 2011 1:24:18 PM and it has been read 8817 times since then.
Populate Nested TreeView In Asp.Net Using Common Table Expression In SQL Server
I needed a way of doing nested tree in one of my asp.net project a couple of months ago.
After searching on the internet I came up with a solution which I will give you the details of it in the following lines.
The magic actually happens at the database level and the result is being sent down to the application from stored procedure.
In stored procedure I used Common Table Expression which you can find some information about this at http://msdn.microsoft.com/en-us/library/ms186243.aspx .
In the code we just do the necessary coding and we get the final result. By using this structure, you can create n depth treeview and populate it from a SQL Server database.
I did not take into account all exception handling. I also did not pay attention CSS. You can apply your own logic and use it however suits your need.
This logic can also be used for other hierarchical solutions as well. It depends how you design and imagine your application.
I am not going to explain every code and T-SQL I have written here. They are self-explanatory by themselves but in case you need clarification, you can comment or send an e-mail by using contact me page.
I do not claim that this is the best way of doing this kind of job. It might be helpful to someone though, that is why I am sharing it in here.
You can find all downloadable files at the end of this article. I also included table data as insert statements.
This sample has been written in the following system environments.
Windows 7 Ultimate 64 Bit
Visual Studio 2010 (Framework 4.0)
SQL Server 2008
The following script creates the database in our SQL server that we will use in this sample application.
USE [master]
GO
/****** Object: Database [NestedTreeSample] Script Date: 05/01/2011 15:57:20 ******/
CREATE DATABASE [NestedTreeSample] ON PRIMARY
( NAME = N'NestedTreeSample', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\NestedTreeSample.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'NestedTreeSample_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\NestedTreeSample_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [NestedTreeSample] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [NestedTreeSample].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [NestedTreeSample] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [NestedTreeSample] SET ANSI_NULLS OFF
GO
ALTER DATABASE [NestedTreeSample] SET ANSI_PADDING OFF
GO
ALTER DATABASE [NestedTreeSample] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [NestedTreeSample] SET ARITHABORT OFF
GO
ALTER DATABASE [NestedTreeSample] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [NestedTreeSample] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [NestedTreeSample] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [NestedTreeSample] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [NestedTreeSample] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [NestedTreeSample] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [NestedTreeSample] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [NestedTreeSample] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [NestedTreeSample] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [NestedTreeSample] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [NestedTreeSample] SET DISABLE_BROKER
GO
ALTER DATABASE [NestedTreeSample] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [NestedTreeSample] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [NestedTreeSample] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [NestedTreeSample] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [NestedTreeSample] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [NestedTreeSample] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [NestedTreeSample] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [NestedTreeSample] SET READ_WRITE
GO
ALTER DATABASE [NestedTreeSample] SET RECOVERY SIMPLE
GO
ALTER DATABASE [NestedTreeSample] SET MULTI_USER
GO
ALTER DATABASE [NestedTreeSample] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [NestedTreeSample] SET DB_CHAINING OFF
GO
The following script creates the table that we will use in this sample application.
USE [NestedTreeSample]
GO
/****** Object: Table [dbo].[Products] Script Date: 05/01/2011 15:58:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[ProductID] [bigint] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](100) NULL,
[ParentProductID] [bigint] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The following script creates the stored procedure that we will use in this sample application.
USE [NestedTreeSample]
GO
/****** Object: StoredProcedure [dbo].[Products_GetList] Script Date: 05/01/2011 15:59:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <murat yasar="">
-- Create date: <01st of May, 2011>
-- Description: <it has="" been="" created="" for="" selecting="" records="" in="" products="" table.="">
-- =============================================
CREATE PROCEDURE [dbo].[Products_GetList]
AS
BEGIN -- Main Begin
SET NOCOUNT ON;
BEGIN TRY
;WITH HIERARCHY(ProductName, ParentProductID, ProductID, FullyQualifiedName, Level)
AS
(
SELECT E.ProductName, E.ParentProductID, E.ProductID, CAST('.' + E.ProductName + '.' as NVarchar(MAX)), 0
FROM Products E
WHERE E.ParentProductID = 0
UNION ALL
SELECT E.ProductName, E.ParentProductID, E.ProductID, H.FullyQualifiedName + '.' + E.ProductName + '.', H.Level+1
FROM Products E
INNER JOIN HIERARCHY H on H.ProductID = E.ParentProductID
)
SELECT H.ProductName, H.ParentProductID, H.ProductID, H.FullyQualifiedName, H.Level
from HIERARCHY H
order by H.FullyQualifiedName
END TRY
BEGIN CATCH
DECLARE @ErrorProcedure nvarchar(300);
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorNumber int;
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @ErrorLine int;
SELECT @ErrorProcedure = ERROR_PROCEDURE(), @ErrorMessage = ERROR_MESSAGE(),
@ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE();
-- We got an error.
-- We can store this error information in a table.
END CATCH;
SET NOCOUNT OFF;
END -- Main Begin
GO
The following script inserts the sample data into table that we will use in this sample application.
SET IDENTITY_INSERT [dbo].[Products] ON
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (1, N'Computers', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (2, N'Notebooks', 1)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (3, N'Desktops', 1)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (4, N'Notebook', 2)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (5, N'Netbook', 2)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (6, N'Telephones', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (7, N'Mobile', 6)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (8, N'Land Line', 6)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (9, N'Digital Music Player', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (10, N'MP3 Players', 9)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (11, N'MP4 Players', 9)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (12, N'Office Technologies', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (13, N'Fax', 12)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (14, N'Calculator', 12)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (15, N'Barcode Reader', 12)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (16, N'Paper Cut Machines', 12)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (17, N'PDA SmartPhone', 6)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (18, N'Wireless Telephone', 6)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (19, N'Display & Sound System', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (20, N'Home Theater System', 19)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (21, N'DVD-DIVX Player', 19)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (22, N'Music Set', 19)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (23, N'Satellite Receiver', 19)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (24, N'Speaker Set', 19)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (25, N'Sound System Amplificator', 19)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (26, N'Music System Amplificator', 19)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (27, N'Music Tools & Components', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (28, N'Percussion', 27)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (29, N'Piano', 27)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (30, N'Guitar', 27)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (31, N'Computer Hardware', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (32, N'VGA Kart', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (33, N'Mainboard', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (34, N'Case', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (35, N'Portable Drive Case', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (36, N'Sound Card', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (37, N'TV Card', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (38, N'Harddisk', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (39, N'Portable Disk', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (40, N'Solid State Disk', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (41, N'CPU', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (42, N'RAM', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (43, N'Cooler', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (44, N'Power Supply', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (45, N'Optical Drive', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (46, N'Cooler Controller', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (47, N'Player', 31)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (48, N'Printers', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (49, N'Ink-Jet', 48)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (50, N'Laser', 48)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (51, N'Thermal-Photograph', 48)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (52, N'Dot Matrix', 48)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (53, N'Telephone Accessories', 6)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (54, N'HeadPhone', 53)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (55, N'Radiation Blocker', 53)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (56, N'Charger', 53)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (57, N'Battery', 53)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (58, N'Battery Tools', 53)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (59, N'Health Products', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (60, N'Thermometer', 59)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (61, N'Blood Pressure', 59)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (62, N'Platform Scale', 59)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (63, N'Toothbrush', 59)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (64, N'Computer Game & Software', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (65, N'Education', 64)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (66, N'Security', 64)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (67, N'Operating System', 64)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (68, N'Office Applications', 64)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (69, N'Game', 64)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (70, N'Home Appliance', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (71, N'Refrigerator', 70)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (72, N'Washing Machine', 70)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (73, N'Dish Washer', 70)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (74, N'Oven', 70)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (79, N'Entry1', 0)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (80, N'Entry2', 79)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (81, N'Entry3', 80)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (82, N'Entry4', 81)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (83, N'Entry5', 82)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (84, N'Entry6', 83)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (87, N'Entry7', 86)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (90, N'Entry8', 79)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (91, N'Entry9', 5)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (96, N'Entry10', 91)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (97, N'Entry11', 96)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (98, N'Entry12', 97)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (99, N'Entry13', 98)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (100, N'Entry14', 98)
INSERT [dbo].[Products] ([ProductID], [ProductName], [ParentProductID]) VALUES (101, N'Entry15', 98)
SET IDENTITY_INSERT [dbo].[Products] OFF
The following shows the html part of this sample application.
<div style="width: auto; text-align: left;">
<asp:treeview id="TreeViewProducts" runat="server" imageset="Faq" onselectednodechanged="TreeViewProducts_SelectedNodeChanged">
<hovernodestyle backcolor="#CCCCCC" borderstyle="Solid" borderwidth="1px" bordercolor="#888888">
<nodestyle forecolor="Black" font-names="Verdana" font-size="10pt" horizontalpadding="2px" verticalpadding="2px" width="100%" height="25px">
<selectednodestyle verticalpadding="2px" horizontalpadding="2px" forecolor="Maroon" font-bold="true">
</selectednodestyle></nodestyle></hovernodestyle></asp:treeview>
<br>
<asp:label id="labelCurrentPath" runat="server" forecolor="Green" font-bold="true"></asp:label>
</div>
The following lines just create connection to the database and get the result data from the stored procedure and adds those result as nodes into treeview.
private void GetTreeNodesFromDatabase()
{
DataTable ComingTable = GetProductList();
if (ComingTable != null)
{
if (ComingTable.Rows.Count > 0)
{
List<treeviewclass> list = new List<treeviewclass>();
foreach (DataRow dr in ComingTable.Rows)
{
list.Add(new TreeviewClass()
{
ParentProductID = Convert.ToInt64(dr["ParentProductID"].ToString()),
ProductID = Convert.ToInt64(dr["ProductID"].ToString()),
ProductName = dr["ProductName"].ToString(),
FullyQualifiedName = dr["FullyQualifiedName"].ToString()
});
}
BindTree(list, null);
}
}
}
private DataTable GetProductList()
{
DataTable MyDataTable = null;
using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
try
{
MyDataTable = new DataTable();
SqlDataAdapter ourAdapter = new SqlDataAdapter();
ourAdapter.SelectCommand = new SqlCommand(@"Products_GetList", connection);
ourAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
ourAdapter.Fill(MyDataTable);
}
catch (Exception ex)
{
Trace.Write("The following error occurred while trying to get product list from database.." + System.Environment.NewLine + ex.Message);
}
}
return MyDataTable;
}
private void BindTree(IEnumerable<treeviewclass> list, TreeNode parentNode)
{
var nodes = list.Where(x => parentNode == null ? x.ParentProductID == 0 : x.ParentProductID == int.Parse(parentNode.Value));
foreach (var node in nodes)
{
TreeNode newNode = new TreeNode(node.ProductName.ToUpper(System.Globalization.CultureInfo.CreateSpecificCulture("en-US")), node.ProductID.ToString());
if (node != null && node.FullyQualifiedName != null)
{
newNode.ToolTip = node.FullyQualifiedName.Replace("..", ">").Replace(".", ">").TrimStart('>').TrimEnd('>').Replace(">", " > ").ToUpper(System.Globalization.CultureInfo.CreateSpecificCulture("en-US"));
}
else
{
newNode.ToolTip = "";
}
if (parentNode == null)
{
TreeViewProducts.Nodes.Add(newNode);
}
else
{
parentNode.ChildNodes.Add(newNode);
}
TreeViewProducts.CollapseAll();
BindTree(list, newNode);
}
}
protected void TreeViewProducts_SelectedNodeChanged(object sender, EventArgs e)
{
labelCurrentPath.Text = TreeViewProducts.SelectedNode.ToolTip;
}
When we run the solution file, we will get the result similar to the following screen capture.
Have a great day.
- Downloadable File/s - |
|||
|---|---|---|---|
| File Name | File Size | Description | |
| createdatabasescript.txt | 2.615 KB | To create sample database use this script. | Download |
| createproductstable.txt | 560 Byte | To create sample table use this script. | Download |
| createstoredprocedure.txt | 1.688 KB | To create sample stored procedure use this script. | Download |
| nestedtree.zip | 36.321 KB | This is the Visual Studio 2010 solution file. | Download |
| tabledataasinsertstatements.txt | 9.178 KB | To insert sample data into table use this script. | Download |
(In order to use this feature, you have to register.)