Post
Ask Question

Create Dynamic Tree View From Database In ASP.NET MVC 5 C#

In this article, we will create a dynamic tree view menu fetched from the database, using ASP.NET MVC 5, C#, Razor and SQL Server 2014 (using Visual Studio 2013). This article will guide you on how to display parent child tree view dynamically from the database. This is the best, easiest, and fastest way to make tree view. Let's try to populate it.

Step 1

Create a table and insert the records, as per your requirement. The table structure is given below.
Table

Create table query

  1. CREATE TABLE [dbo].[Categories](  
  2.     [CategoryId] [int] primary key identity(1,1),  
  3.     [CategoryName] [varchar](40),  
  4.     [ParentCategoryId] [int],  
  5.     [Remarks] [nvarchar](max),  
  6. )  

Step 2

I have attached sample SQL query file named “categoryDb.sql” to create table and insert the records. Just run the query to make database and insert the records. You can insert records manually also.

Step 3

Create a new project and take Empty ASP.NET MVC Application. Here, I have used MVC 5 project.

 
 
 

Step 4

Add new Class Library Project. The solution is named Business Layer which contains an actual logic and an interaction with the database.

 
 
 

Step 5

Add new class named SqlAccess in BusinessLayer project. The class has a constructor to initialize SqlConnection and a method, which will take query in the string format and return rows in DataTable.

 
  1. #region Declaration  
  2. SqlConnection con;  
  3. DataTable dt;  
  4. #endregion  
  5.  
  6. #region Constructor  
  7. public SqlAccess()  
  8. {  
  9.     con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConStr"]);  
  10. }  
  11. #endregion  
  12.  
  13. #region KeyMethods  
  14. public DataTable GetDataTableFromDb(string query)  
  15. {  
  16.     SqlDataAdapter Adpt = new SqlDataAdapter(query, con);  
  17.     dt = new DataTable();  
  18.     try  
  19.     {  
  20.         Adpt.Fill(dt);  
  21.     }  
  22.     catch (SqlException ex)  
  23.     {  
  24.         dt = null;  
  25.         if (con.State == ConnectionState.Open)  
  26.             con.Close();  
  27.     }  
  28.     finally  
  29.     {  
  30.         if (con != null)  
  31.             if (con.State == ConnectionState.Open) con.Close();  
  32.         Adpt.Dispose();  
  33.     }  
  34.     return dt;  
  35. }  
  36. #endregion  

Step 6

Add another class named HomeBAL in BusinessLayer. This class contains a method that passes query to SqlAccess and returns the DataTable. 

  1. public DataTable GetAllCategories()  
  2. {  
  3.     return new SqlAccess().GetDataTableFromDb("SELECT CategoryId, CategoryName, ParentCategoryId FROM Categories");  
  4. }  
 

Step 7

Now, we need to access BusinessLayer members from our PresentationLayer (MVC Project). So, give the referenc of BusinessLayer to PresentationLayer.

 
 
 
 
Make sure that the BusinessLayer is referenced inside "References" of PresentationLayer.
 
 

Step 8

Add a new class named Category in PresentationLayer, which contains database structure properties. 
 
 
  1. public class Category  
  2. {  
  3.     public int CategoryId { get; set; }  
  4.     public string CategoryName { get; set; }  
  5.     public System.Nullable<int> ParentCategoryId { get; set; }  
  6. }  

Step 9

Add a new class named TreeNode in “PresentationLayer” which contains database properties, and add one more List<> type of property which will hold all the children of the particular category.
 
 
  1. public class TreeNode  
  2. {  
  3.     public int CategoryId { get; set; }  
  4.     public string CategoryName { get; set; }  
  5.   
  6.     public TreeNode ParentCategory { get; set; }  
  7.     public List<TreeNode> Children { get; set; }  
  8. }

Step 10

Add a new Controller named “HomeController” which has one action method (Index) and two other methods.

Index Action Method

  1. public ActionResult Index()  
  2. {  
  3.     ViewBag.Tree = GetAllCategoriesForTree();  
  4.     return View();  
  5. }   
 

Recursive method

  1. private static List<TreeNode> FillRecursive(List<Category> flatObjects, int? parentId = null)  
  2. {  
  3.     return flatObjects.Where(x => x.ParentCategoryId.Equals(parentId)).Select(item => new TreeNode  
  4.     {  
  5.         CategoryName = item.CategoryName,  
  6.         CategoryId = item.CategoryId,  
  7.         Children = FillRecursive(flatObjects, item.CategoryId)  
  8.     }).ToList();  
  9. }  
 

Get all categories from database and generate html string,

  1. public string GetAllCategoriesForTree()  
  2. {  
  3.     List<Category> categories = new List<Category>();  
  4.     DataTable dt = new HomeBAL().GetAllCategories();  
  5.   
  6.     if (dt != null && dt.Rows.Count > 0)  
  7.     {  
  8.         foreach (DataRow row in dt.Rows)  
  9.         {  
  10.             categories.Add(  
  11.                 new Category  
  12.                 {  
  13.                     CategoryId = Convert.ToInt32(row["CategoryId"]),  
  14.                     CategoryName = row["CategoryName"].ToString(),  
  15.                     ParentCategoryId = (Convert.ToInt32(row["ParentCategoryId"]) != 0) ? Convert.ToInt32(row["ParentCategoryId"]) : (int?) null  
  16.                 });  
  17.         }  
  18.   
  19.         List<TreeNode> headerTree = FillRecursive(categories, null);  
  20.  
  21.         #region BindingHeaderMenus  
  22.   
  23.         string root_li = string.Empty;  
  24.         string down1_names = string.Empty;  
  25.         string down2_names = string.Empty;  
  26.   
  27.         foreach (var item in headerTree)  
  28.         {  
  29.             root_li += "<li class=\"dropdown mega-menu-fullwidth\">"  
  30.                         + "<a href=\"/Product/ListProduct?cat=" + item.CategoryId + "\" class=\"dropdown-toggle\" data-hover=\"dropdown\" data-toggle=\"dropdown\">" + item.CategoryName + "</a>";  
  31.   
  32.             down1_names = "";  
  33.             foreach (var down1 in item.Children)  
  34.             {  
  35.                 down2_names = "";  
  36.                 foreach (var down2 in down1.Children)  
  37.                 {  
  38.                     down2_names += "<li><a href=\"/Product/ListProduct?cat=" + down2.CategoryId + "\">" + down2.CategoryName + "</a></li>";  
  39.                 }  
  40.                 down1_names += "<div class=\"col-md-2 col-sm-6\">"  
  41.                                 + "<h3 class=\"mega-menu-heading\"><a href=\"/Product/ListProduct?cat=" + down1.CategoryId + "\">" + down1.CategoryName + "</a></h3>"  
  42.                                 + "<ul class=\"list-unstyled style-list\">"  
  43.                                 + down2_names  
  44.                                 + "</ul>"  
  45.                                 + "</div>";  
  46.             }  
  47.             root_li += "<ul class=\"dropdown-menu\">"  
  48.                         + "<li>"  
  49.                             + "<div class=\"mega-menu-content\">"  
  50.                                 + "<div class=\"container\">"  
  51.                                     + "<div class=\"row\">"  
  52.                                         + down1_names  
  53.                                     + "</div>"  
  54.                                 + "</div>"  
  55.                             + "<div>"  
  56.                         + "</li>"  
  57.                         + "</ul>"  
  58.                     + "</li>";  
  59.         }  
  60.         #endregion  
  61.   
  62.         return "<ul class=\"nav navbar-nav\">" + root_li + "</ul>";  
  63.     }  
  64.     return "Record Not Found!!";  
  65. }  
 

Step 11

Add new View named “Index.cshtml” which contains our webpage's body and displays the viewbag return from Controller. 
 
 
 
 
 
  
  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     <meta name="viewport" content="width=device-width" />  
  5.     <title>Dynamic Tree</title>  
  6. </head>  
  7. <body>  
  8.     <div class="header-v5 header-static">  
  9.   
  10.         <!-- Navbar -->  
  11.         <div class="navbar navbar-default mega-menu" role="navigation">  
  12.             <div class="container">  
  13.                 <div class="collapse navbar-collapse navbar-responsive-collapse">  
  14.                     @Html.Raw(ViewBag.Tree);  
  15.                 </div>  
  16.             </div>  
  17.         </div>  
  18.         <!-- End Navbar -->  
  19.     </div>  
  20. </body>  
  21. </html>  

Step 12

If you run this action, your tree is ready but without design, as shown below.
 

Step 13

Put the CSS content inside project and include it in page. You can see successfully generated TreeView like below. 
 
 

Final Output
 
Thank you developers… I have attached the full project with this article. Please comment if you encounter any issues here.

Note

Here, I am showing you two queries which are getting all categories from db.

Simple Parent Child

  1. SELECT  
  2.     CategoryId,  
  3.     CategoryName,  
  4.     ParentCategoryId  
  5. FROM  
  6.     Categories  
 

If you want TreeView from SQL query,
  1. SELECT  
  2.     CASE   
  3.         WHEN  
  4.             down1.CategoryName is null   
  5.         THEN   
  6.             root.CategoryId   
  7.         WHEN   
  8.             down2.CategoryName is null  
  9.         THEN  
  10.             down1.CategoryId  
  11.         WHEN  
  12.             down3.CategoryName is null  
  13.         THEN  
  14.             down2.CategoryId  
  15.         ELSE   
  16.             0   
  17.     END AS CategoryId  
  18.     ,root.CategoryName as root_name  
  19.     ,ISNULL(down1.CategoryName,'') as down1_name  
  20.     ,ISNULL(down2.CategoryName,'') as down2_name  
  21.     ,ISNULL(down3.CategoryName,'') as down3_name  
  22. FROM Categories as root  
  23. LEFT OUTER  
  24.     JOIN Categories AS down1  
  25.     ON down1.ParentCategoryId = root.CategoryId  
  26. LEFT OUTER  
  27.     JOIN Categories AS down2  
  28.     ON down2.ParentCategoryId = down1.CategoryId  
  29. LEFT OUTER  
  30.     JOIN Categories AS down3  
  31.     ON down3.ParentCategoryId = down2.CategoryId  
  32. WHERE root.ParentCategoryId = 0  
  33. ORDER   
  34.     BY root_name   
  35.     , down1_name   
  36.     , down2_name   
  37.     , down3_name  
 

Hope you like it. Thank you for reading. Like and share, and keep learning.


Similar Articles