Skip to main content

SQL XML output to populate a treeview

In this post I will show how to populate an asp.net tree view from the output of sql stored procedure. The table looks like this:

pageid pagename parentid

--- ---------- -------------

1 N1 0

3 N4 1

4 N10 3

5 N7 3

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();
       }

   }

}

Comments

Post a Comment

Popular posts from this blog