Binding JsTree with Database in ASP.NET MVC

In my  previous post I demonstrated how to bind jsTree programmatically with a collection/array of objects using JSON. In this post I’ll reuse the same logic to map jsTree node anatomy on server side entity and will populate that entities from database. That , in turn will be converted to JSON and sent to client side to bind with jsTree. I’ll demonstrate this in ASP.NET MVC.

First you need to setup jsTree with required files in your ASP.NET MVC project as shown below .

 

I’ll bind jsTree with a self referencing table for example an Employee, where an employee can also be manager of another employee. To keep it as simplest as possible there are only three fields in the table , ID . Name and ManagerId as shown below.

Here is sample data in the table to be displayed. You can see from the data the table is self referencing i.e.  ManagerId of an employee is ID of another employee

 

With bottom up approach next step is to setup Model with a method that could return a DataSet  to the controller action. I am not using LINQ in this post , I have added a class named jsTreeDAO in the model that uses ConnectionManager, a utility class for data access . I am not showing its definition here. Its quite simple to implement, you can also wirte your own ADO logic in the method.

public static DataSet GetEmployeeHiearchicalInfo()
  {
    DataSet ds = new DataSet();
    try
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = @"WITH Hierarchy(ManagerId, EmpId, level, Name) AS (
            SELECT ManagerId, Id, 0, Name FROM Employee
            where ManagerId Is Null UNION ALL SELECT dt.ManagerId, dt.Id, level + 1, dt.Name FROM Employee 
            dt INNER JOIN hierarchy h ON dt.ManagerId = h.EmpId) 
            select * from Hierarchy";

        cmd.CommandType = CommandType.Text;
        //ConnectionManager is a utility class written for handling connection and exectue commands.
        ConnectionManager.Execute(cmd, ref ds);
    }
    catch (SqlException)
    {
        throw;
    }
    catch (Exception)
    {
        throw;
    }
    return ds;
 }

The important point to note in above method is the SQL query  which uses Common Table Expression (CTE)  a new feature in SQL Server 2005 for building an hierarchal view of  the data. For more details about CTE ,please refer to this article. 

Lets Add a controller Action that  would call the model’s method. You need to add a controller first ,say its  EmployeeController add an Action with name GetEmployeesHiearchicaInfo as shown below.

public JsonResult GetEmployeeInfo()
  {
    DataSet dsNodes = JsTreeDAO.GetEmployeeHiearchicalInfo();
    DataTable tableNodes = dsNodes.Tables[0];
    DataRow parentDr = tableNodes.Rows[0];

    JsTreeNode rootNode = new JsTreeNode();
    rootNode.attributes = new Attributes();
    rootNode.attributes.id = Convert.ToString(parentDr["EmpId"]);
    rootNode.attributes.rel = "root" + Convert.ToString(parentDr["EmpId"]);
    rootNode.data = new Data();
    rootNode.data.title = Convert.ToString(parentDr["Name"]);

    rootNode.attributes.mdata = "{ draggable : true, max_children : 100, max_depth : 100 }";

    PopulateTree(parentDr, rootNode, tableNodes);


    return Json(rootNode);
  }

This method is quite self explanatory. First it gets DataSet having employee info then it sets all info for the root node from the first data row, then it passes both rootNode and parentRow to another method that binds all info recursively for its children. The definition of method is shown below.

public void PopulateTree(DataRow dataRow, JsTreeNode jsTNode, DataTable tableNodes)
{
    jsTNode.children = new List();
    foreach (DataRow dr in tableNodes.Rows)
    {

        if (dr != null)
        {
            if (Convert.ToInt32(dr["level"]) == Convert.ToInt32(dataRow["level"]) + 1
                        && Convert.ToInt32(dr["ManagerId"]) == Convert.ToInt32(dataRow["EmpId"])
                        )
            {

                JsTreeNode cnode = new JsTreeNode();
                cnode.attributes = new Attributes();
                cnode.attributes.id = Convert.ToString(dr["EmpId"]);
                cnode.attributes.rel = "folder" + Convert.ToString(dr["EmpId"]);
                cnode.data = new Data();
                cnode.data.title = Convert.ToString(dr["Name"]);

                cnode.attributes.mdata = "{ draggable : true, max_children : 100, max_depth : 100 }";

                jsTNode.children.Add(cnode);


                PopulateTree(dr, cnode, tableNodes);

            }
        }
    }
}

Now we have to add a view named Employee to display employee information coming from database using jsTree. The View  markup is quite simple just add a div to which you want to attach the jsTree as shown below.

<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>
<asp:Content ID="aboutTitle" ContentPlaceHolderID="TitleContent" runat="server"%>
Employee Graph
</asp:Content>
<asp:Content ID="aboutContent" ContentPlaceHolderID="MainContent" runat="server"%>
<div id="employeeGraph" %>
</div>
</asp:Content%>

Now we have to setup jsTree which will call the controller method asynchronously to load and bind data as shown below.

<script type="text/javascript">
$(function() {
    $("#foldergraph").tree({
        data: {
            type: "json",
            url: "/Home/GetEmployeeInfo",
            async: true
        },
        ui: { theme_name: "classic" },
        rules: {
            metadata: "mdata",
            use_inline: true,
            clickable: ["all"],
            deletable: ["all"],
            renameable: ["all"],
            creatable: ["all"],
            draggable: ["all"],
            dragrules: ["child * child", "child inside root", "tree-drop inside root"],
            createat: ["top"],
            drag_button: "left",
            droppable: ["tree-drop"]
        }

    });
});

</script>

Its also mentioned in my last post that the rules{} configuration is optional, only you have to set data aggregate attributes of the jsTree .

must need to set Async=true for asynchronous JSON data load form server.

Have a look on the output.

In these two  posts i just focused on only jsTree data binding, in upcoming posts I will show you how you can drag drop, create, delete nodes and persist the changes in database

Hope this helps.

Mahr