How to expand all folder structure from DB using tree view controls

Question

I am using tree view controls in my asp.net web application.

I have all the list of folder in my database.

How to bind all the MS SQL DB details with my asp.net controls.

Here, i need some rich UI design in tree view to show client (end user) side.

Pls. help us how to make rich UI design template and binding the data from MS SQL DB..

Answer

Ok, so we will assume you have a simple database table with some folders in it.

Say, like this:

User's image

Ok, so now we need to take above from database, and fill out the tree view.

of course when a user "expands" the treeview, we will list out files (and or folders) in that folder.

So, drop in a treeview, say this markup:

    My Folders

Ok, so now our code to load from database:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then

            LoadDBFolders()

        End If
    End Sub


    Sub LoadDBFolders()

        Dim rstFolders As DataTable
        rstFolders = MyRst("SELECT * FROM tblFolders ORDER BY Folder")

        ' add this list of folders to the tree.

        TreeView1.ExpandImageUrl = "~/Content/blueclose2.png"
        TreeView1.CollapseImageUrl = "~/Content/blueopen2.png"
        TreeView1.NoExpandImageUrl = "~/Content/file2.png"
        For Each OneRow As DataRow In rstFolders.Rows

            ' lets ONLY display the folder name, no path, no matter where folder is
            Dim FoldersPath As String() = Split(OneRow("Folder"), "\")

            Dim strTopFolder As String = FoldersPath(FoldersPath.Length - 1)

            Dim child As New TreeNode
            child.Text = strTopFolder  ' we display JUST the top folder name
            child.Value = OneRow("Folder") ' we Store/save full path name
            child.Expanded = False
            child.PopulateOnDemand = True
            'child.ShowCheckBox = False

            TreeView1.Nodes.Add(child)
        Next


    End Sub

So, that loads up the tree view.

However, now the "more" challenging part. When user clicks on a folder, we need to expand the tree view to the file system.

So, then this bit of code works quite nice:

I drop this code right below above code:

    Public Class MyFolder
        Public MyFiles As FileInfo()
        Public MyFolders As DirectoryInfo()
    End Class

    Public Function GetFiles(sRootFolder As String) As MyFolder

        Dim MyDir As New DirectoryInfo(sRootFolder)
        Dim cMyFolder As New MyFolder

        cMyFolder.MyFolders = MyDir.GetDirectories("*.*", SearchOption.TopDirectoryOnly)
        cMyFolder.MyFiles = MyDir.GetFiles("*.*", SearchOption.TopDirectoryOnly)

        Return cMyFolder

    End Function


    Protected Sub TreeView1_TreeNodeExpanded(sender As Object, e As TreeNodeEventArgs)

        ' user clicked on tree node, get the folder, and fill out files
        ' and sub folders too!!!

        Dim child As TreeNode = e.Node
        Dim dtChild As MyFolder = GetFiles(child.Value) ' get ALL files + folders

        LoadTreeFiles(child.Value, dtChild, child)

    End Sub

    Sub LoadTreeFiles(fRootL As String,
                      lParent As MyFolder,
                      tTreeNode As TreeNode)

        ' add folders if any
        tTreeNode.ChildNodes.Clear()

        For Each sFolder As DirectoryInfo In lParent.MyFolders
            Dim child As New TreeNode
            child.Text = sFolder.Name
            child.Value = sFolder.FullName
            child.Expanded = False
            child.PopulateOnDemand = True
            child.ShowCheckBox = False
            tTreeNode.ChildNodes.Add(child)
        Next
        ' now any files
        For Each sFile As FileInfo In lParent.MyFiles

            Dim child2 As New TreeNode
            child2.Text = sFile.Name
            child2.Value = sFile.FullName
            child2.Expanded = False
            child2.ShowCheckBox = True
            child2.PopulateOnDemand = False
            tTreeNode.ChildNodes.Add(child2)
        Next

    End Sub

And the result is now this:

treedb

Answer

Hi @Gani_tpt,

You didn't provide your database data, I wrote you an example with test data.

The PopulateTreeView method is a recursive function. Inside the Page Load event, the TreeView is populated with the records from the parent table.

Inside the PopulateTreeView method, a loop is executed over the DataTable and if the ParentId is 0 i.e. the node is a parent node, a query is executed over the child table to populate the corresponding child nodes and again the PopulateTreeView method is called.

This process continues until all parent nodes along with their child nodes are added to the TreeView.

protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                DataTable dt = this.GetData("SELECT Id, Name FROM parent");
                this.PopulateTreeView(dt, 0, null);
            }
        }

        private void PopulateTreeView(DataTable dtParent, int parentId, TreeNode treeNode)
        {
            foreach (DataRow row in dtParent.Rows)
            {
                TreeNode child = new TreeNode
                {
                    Text = row["Name"].ToString(),
                    Value = row["Id"].ToString()
                };
                if (parentId == 0)
                {
                    TreeView1.Nodes.Add(child);
                    DataTable dtChild = this.GetData("SELECT Id, Name FROM child WHERE ParentId = " + child.Value);
                    PopulateTreeView(dtChild, int.Parse(child.Value), child);
                }
                else
                {
                    treeNode.ChildNodes.Add(child);
                }
            }
        }

        private DataTable GetData(string query)
        {
            DataTable dt = new DataTable();
            string constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        sda.Fill(dt);
                    }
                }
                return dt;
            }
        }

CREATE TABLE [dbo].[parent ]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(50) NULL
)

CREATE TABLE [dbo].[child]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(50) NULL, 
    [ParentId] INT NULL
)

User's image

Best regards,
Lan Huang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

How to expand all folder structure from DB using tree view controls

2 answers