ASP.NET MVC Bind Or Populate jsTree From SQL Server Database

Hello Everyone,

Normally when working with real world information, we tend to have projects or tasks that will show employees with their supervisors or managers viewed in hierarchical structure. If the given project is a web application, the jsTree.js fits for this requirement. This article will show you how to populate a jsTree from SQL Server database table called ContosoRetailsDW which is a Microsoft sample database. It already has a table specifically DimEmployee that we can play with. To begin with lets define the models used. The JsTreeModel class contains the information used by the jsTree while the clsResourceManagers class receives the result from the query.

public class EmpManagerTreeViewModel
{
	public clsResourceManagers ResourceManager { get; set; }
	public JsTreeModel EmployeeTreeModel { get; set; }

	public EmpManagerTreeViewModel()
	{
		ResourceManager = new clsResourceManagers();
		EmployeeTreeModel = new JsTreeModel();
	}
}

public class clsResourceManagers
{
	public int EmployeeID { get; set; }

	public string EmployeeName { get; set; }

	public int ManagerID { get; set; }

	public int StaffLevel { get; set; }

	public clsResourceManagers()
	{
		EmployeeID = 0;
		EmployeeName = string.Empty;
		ManagerID = 0;
		StaffLevel = 0;
	}
}

public class JsTreeModel
{
	public string text { get; set; }

	public string id { get; set; }

	public int level { get; set; }

	public List<JsTreeModel> children { get; set; }

	public JsTreeModel()
	{
		text = string.Empty;
		id = string.Empty;
		level = 0;
		children = new List<JsTreeModel>();
	}
}

The clsQuery class below will fetch the data from ContosoRetailsDW DimEmployee and transform it into a tree model structure used by the jsTree control.

public class clsQuery
{
	public string ConnStr { get; set; }

	public clsQuery()
	{
		ConnStr = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
	}

	public void GetEmployeesAndManagers(ref JsTreeModel treeModel)
	{
		SqlConnection conn;
		SqlCommand command;
		StringBuilder Query;
		List<clsResourceManagers> lstResourceManagers;

		conn = new SqlConnection(ConnStr);
		Query = new StringBuilder();
		lstResourceManagers = new List<clsResourceManagers>();

		Query.AppendLine(" WITH Hierarchy ");
		Query.AppendLine("  AS (");
		Query.AppendLine("      SELECT ");
		Query.AppendLine("          ISNULL(ParentEmployeeKey, 0) As ParentEmployeeKey, ");
		Query.AppendLine("          EmployeeKey, ");
		Query.AppendLine("          0 as StaffLevel, ");
		Query.AppendLine("          (FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName) As [Name] ");
		Query.AppendLine("      FROM DimEmployee");
		Query.AppendLine("      WHERE ParentEmployeeKey Is Null ");
		Query.AppendLine("      UNION ALL");
		Query.AppendLine("      SELECT ");
		Query.AppendLine("          ISNULL(Emp2.ParentEmployeeKey, 0) As ParentEmployeeKey, ");
		Query.AppendLine("          Emp2.EmployeeKey,");
		Query.AppendLine("          StaffLevel + 1,");
		Query.AppendLine("          (Emp2.FirstName + ' ' + ISNULL(Emp2.MiddleName, '') + ' ' + Emp2.LastName) As [Name]");
		Query.AppendLine("      FROM DimEmployee Emp2");
		Query.AppendLine("      INNER JOIN Hierarchy h ON Emp2.ParentEmployeeKey = h.EmployeeKey");
		Query.AppendLine(") ");
		Query.AppendLine("SELECT * FROM Hierarchy ");

		conn.Open();
		command = new SqlCommand(Query.ToString(), conn);

		using (SqlDataReader reader = command.ExecuteReader())
		{
			while (reader.Read())
			{
				lstResourceManagers.Add(new clsResourceManagers()
				{
					ManagerID = reader.GetInt32(0),
					EmployeeID = reader.GetInt32(1),
					StaffLevel = reader.GetInt32(2),
					EmployeeName = reader.GetString(3),
				});
			}
		}

		treeModel.id = lstResourceManagers[0].EmployeeID.ToString();
		treeModel.text = lstResourceManagers[0].EmployeeName;
		treeModel.level = lstResourceManagers[0].StaffLevel;

		PopulateTree(lstResourceManagers[0], treeModel, lstResourceManagers);
	}

	private void PopulateTree(clsResourceManagers resourceObject, JsTreeModel jsTreeModel, List<clsResourceManagers> lstResourceManagers)
	{
		jsTreeModel.children = new List<JsTreeModel>();

		for (int i = 0; i < lstResourceManagers.Count; i++)
		{
			if (lstResourceManagers[i] != null)
			{
				if ((lstResourceManagers[i].StaffLevel == resourceObject.StaffLevel + 1)
				   && (lstResourceManagers[i].ManagerID == resourceObject.EmployeeID))
				{
					JsTreeModel childNode = new JsTreeModel();
					childNode.id = lstResourceManagers[i].EmployeeID.ToString();
					childNode.text = lstResourceManagers[i].EmployeeName;
					childNode.level = lstResourceManagers[i].StaffLevel;

					jsTreeModel.children.Add(childNode);

					PopulateTree(lstResourceManagers[i], childNode, lstResourceManagers);
				}
			}
		}
	}
}

The controller action calls the GetEmployeesAndManagers() method from clsQuery class and stores the result into the viewmodel object that will be passed to the view page.

public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            EmpManagerTreeViewModel model;
            clsQuery clsQuery1;
            JsTreeModel treeModel; 

            clsQuery1 = new clsQuery();
            treeModel = new JsTreeModel();
            model = new EmpManagerTreeViewModel();

            clsQuery1.GetEmployeesAndManagers(ref treeModel);
            model.EmployeeTreeModel = treeModel;

            return View(model);
        }
    }

The view will encode the model into JSON format and will be assigned to the data property of the jsTree control.

@model JsTree.Models.EmpManagerTreeViewModel
<div class="row">
    <hr />
</div>
<div class="row">
    <div class="col-md-12">
        <div class="col-md-6">
            <div id="divEmployeeManagertree">
                <div id="jsEmployeeManagertree">
                </div>
            </div>
        </div>
        <div class="col-md-6">
        </div>
    </div>
</div>
@section Scripts{
    <script src="~/Scripts/jsTree3/jstree.js"></script>
    <script type="text/javascript">
        var jSEmployeeTreeData =  @Html.Raw(Json.Encode(Model.EmployeeTreeModel));

        $(document).ready(function () {
            $('#jsEmployeeManagertree').jstree({
            'checkbox': {
                keep_selected_style: false,
                three_state: false,
                cascade: 'down'
            },
            'core': {
                'themes': {
                    'icons': false
                },
                'data': jSEmployeeTreeData
            },
            'plugins': ["defaults", "checkbox"],
            'expand_selected_onload': true
            }).bind("select_node.jstree", function (e, data) {
                if (data.event) {
                    data.instance.select_node(data.node);
                    data.instance.toggle_node(data.node);
                }
            }).bind('deselect_node.jstree', function (e, data) {
                if (data.event) {
                    data.instance.deselect_node(data.node);
                }
            });
        });
    </script>
}

Given that everything is setup correctly without errors and you have added the necessary jsTree css and JavaScript files in your project, you should see the jsTree populated with employees and their manager from the database.

Cheers!