Recently I was working on one of my projects and the requirement was to generate a treeview from the table. The problem was complex and after some research, I found that we can use asp.net Treeview control. I created a stored procedure and generated necessary XML for treeview. Check out the following code.
| pageid | pagename | parentid |
|---|---|---|
| 1 | N1 | 0 |
| 3 | N4 | 1 |
| 4 | N10 | 3 |
| 5 | N7 | 3 |
Stored Procedure
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [DBO].[gentree]
AS
BEGIN
SET NOCOUNT ON;
WITH TREE
AS (SELECT CAST(1 AS INT) AS LEVEL,
PAGEID,
PAGENAME,
CAST(RIGHT(' '
+ CONVERT(VARCHAR(11),PAGEID),11) AS VARCHAR(120)) AS HIERARCHY
FROM PAGE
WHERE PARENTID = '0'
UNION ALL
SELECT LEVEL
+ 1,
B.PAGEID,
B.PAGENAME,
CAST(A.HIERARCHY
+ '/'
+ RIGHT(' '
+ CONVERT(VARCHAR(11),B.PAGEID),11) AS VARCHAR(120))
FROM TREE A
JOIN PAGE B
ON A.PAGEID = B.PARENTID),
SEQTREE
AS (SELECT LEVEL,
Row_number()
OVER(ORDER BY HIERARCHY) AS SEQ,
PAGEID,
PAGENAME,
HIERARCHY
FROM TREE)
SELECT '<Root>'
+ REPLACE(REPLACE((SELECT '`tree id="'
+ CONVERT(VARCHAR(11),A.PAGEID)
+ '" PageName="'
+ A.PAGENAME
+ '" ~'
+ CASE
WHEN A.LEVEL < Isnull(B.LEVEL,1) THEN ''
ELSE Replicate('`/tree~',1
+ A.LEVEL
- Isnull(B.LEVEL,1))
END
FROM SEQTREE A
LEFT JOIN SEQTREE B
ON A.SEQ
+ 1 = B.SEQ
ORDER BY A.SEQ
for xml path('')
),'`','<'),'~','>')
+ '</Root>' AS MYXML
END
--exec [GenTree]
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TreeView ID="TreeView1" runat="server" DataSourceID="XmlDataSource1" ExpandDepth="0">
<DataBindings>
<asp:TreeNodeBinding DataMember="tree" TextField="PageName" ValueField="id" />
</DataBindings>
</asp:TreeView>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" OnLoad="XmlDataSource1_Load"
XPath="Root/tree"></asp:XmlDataSource>
</div>
</form>
</body>
</html>
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 Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void XmlDataSource1_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
SqlConnection SqlCon = new SqlConnection("server=(local);database=CMMForStarters;uid=sa;pwd=12345");
SqlCon.Open();
SqlCommand SqlCmd = new SqlCommand("GenTree", SqlCon);
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlDataReader SqlDrr = SqlCmd.ExecuteReader();
if (SqlDrr.Read())
{
XmlDataSource1.Data = SqlDrr.GetString(0);
}
SqlDrr.Close();
SqlCon.Close();
SqlCmd.Dispose();
SqlCon.Dispose();
}
}
}