Load Data from Database to TreeView Asp.Net – Bootstrap Treeview

Loading Data from Database to TreeView is difficult as compared to Loading Data into a Table.

Let me explain first that why and in what case developers find it difficult.

Above all, we need to understand the structure of the Database table from where we want to load data to a TreeView. Actually, TreeView is the way of defining a hierarchy with the parent-child relation. So in most cases, parents and child are defined in the same table in a way that every node has its parent id which is the unique key from the same table.

This Database Table will help you to better understand the TreeView hierarchy data in the Database Table.

For Binding Data from this Table to TreeView, I’m going to use one of the most popular Bootstrap Treeview Library.

For defining the hierarchical structure required to display data in the tree, it’s important to provide a nested array of JavaScript objects like this.

var tree = [
  {
    text: "Parent 1",
    nodes: [
      {
        text: "Child 1",
        nodes: [
          {
            text: "Grandchild 1"
          },
          {
            text: "Grandchild 2"
          }
        ]
      },
      {
        text: "Child 2"
      }
    ]
  },
  {
    text: "Parent 2"
  },
  {
    text: "Parent 3"
  },
  {
    text: "Parent 4"
  },
  {
    text: "Parent 5"
  }
];

You can easily download the Library and setup but the challenge is to create the above hierarchical structure from the server side.

I’m going to provide you the best solution using recursion technique with Asp.Net for creating the above hierarchical structure using C#.

public string _TreeView()
{
    var dn = db.menu.ToList(); // getting table data from database (menu is the name of table)
    DataSet ds = new DataSet();
    ds = ToDataSet(dn);
    DataTable table = ds.Tables[0];
    DataRow[] parentMenus = table.Select("ParentId = 0");

    var sb = new StringBuilder();
    sb.Append("[");
    string unorderedList = GenerateUL(parentMenus, table, sb);
    return unorderedList+"]";
}

private string GenerateUL(DataRow[] menu, DataTable table, StringBuilder sb)
{
    if (menu.Length > 0)
    {
        foreach (DataRow dr in menu)
        {
            sb.Append("{");
            string pid = dr["Id"].ToString();
            string menuText = dr["name"].ToString();
            sb.Append(String.Format(@" ""text"": ""{0}"",", menuText));
            string url = dr["url"].ToString();
            sb.Append(String.Format(@" ""href"": ""{1}""", url));
            string parentId = dr["parent_id"].ToString();

            DataRow[] subMenu = table.Select(String.Format("ParentId = '{0}'", pid));
            if (subMenu.Length > 0 && !pid.Equals(parentId))
            {
                var subMenuBuilder = new StringBuilder();
                sb.Append(String.Format(@", ""nodes"": ["));
                sb.Append(GenerateUL(subMenu, table, subMenuBuilder));
                sb.Append("]");
            }
            sb.Append("},");
        }
        sb.Remove(sb.Length - 1, 1);
    }
    return sb.ToString();
}

public DataSet ToDataSet<T>(List<T> items)
{
    DataTable dataTable = new DataTable(typeof(T).Name);
    //Get all the properties
    PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in Props)
    {
        //Setting column names as Property names
        dataTable.Columns.Add(prop.Name);
    }
    foreach (T item in items)
    {
        var values = new object[Props.Length];
        for (int i = 0; i < Props.Length; i++)
        {
            values[i] = Props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }
    DataSet ds = new DataSet();
    ds.Tables.Add(dataTable);
    return ds;
}

This Code will generate the required result array. You just need to pass this array to Asp.Net View and assign this to the data field of TreeView.

This Code is for C# you may implement this Algorithm in any platform other than Asp.Net.