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 .

 image


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.

imageHere 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

  image


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.

image



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

15 comments:

Gucci said...

Dear Mohyuddin :
Thx for ur nice article.
I have a question about connectionmanager, I check this in MSDN, its a class belong to Microsoft.Web.Management.Host;
But when I beclare "using Microsoft.Web.Management.Host;"
in MS studio .cs .It's unavailable

The last,I am looking forward to your next one about node drag,drop,delete,creation,etc.

Mahr G. Mohyuddin said...

Hi Gucci,
ConnectionManager is a custom utility class written for handling connection and exectue commands. Its not a built in. You can write your own ADO.NET logic to replace it. Actually to keep things simple i did not went into details of this class. Its very simple to implement. Just go through the article once again, I have made it clear now.

Regards.

Dave said...

Wouldn't LINQ to SQL be a better example?

Unknown said...

thanks! excellent!
I am really looking forward to the example with sorting. Please write that post soon, as I really need some help with sorting and storing the data back in the database. thanks again!

AssassinX189 said...

Can you explain about JsonResult and Json(rootNode)? How do I make them work?

P/S: You should use "& l t ;" and "& g t ;" (no spaces) in your code instead of "<" and ">"

Anonymous said...

Muhyuddin, I enjoyed reading the article. Good job. When can we expect drag drop, create, edit, delete nodes and persist the changes in database. Also, can you show how to right click a node and execute create, delete, update etc from a pop-up menu. Looking forward to it. Thanks for the effort. - Joe

Anonymous said...

Hi,

Great article...Can you provide a clean download...

Anonymous said...

Hi,
Thanks!
I really needed this.

Unknown said...

Hello Mahr,

Can you provide a download link so I can step through the code?

Thanks!!!!

Anonymous said...

Hi how i do this in web form :)

Anonymous said...

Hi,

Is this possible normal asp.net web application. If so can you kindly share it to dharmarao.cse@gmail.com

Anonymous said...

Hi,

I have gone through all the three articles posted on jstree. I am getting an error on on mouse_upevent

tree_component.drag_drop.marker.hide(); Can you kindly suggest on this

Unknown said...

Hi can i have code for the same above demo.

Anonymous said...

Hi,
Is there any chance to get the demo code for this? I'm battling to follow all the different parts that you mention.
Thank you!

IT Tutorials said...


Given so much information in it. its very useful .perfect explanation about Dot net framework.Thanks for your valuable information. dot net training in chennai | dot net training in velachery

Post a Comment