Populating DropDownTree with SQL Data

[Mathew]

I have a table in SQL that contains the breakdown of the hierarchy list. The table has three columns

1. ID - the id of the item

2. Item Name - the name of the item

3. ParentID - the parent id of the item

The table hierarchy goes down 5 levels (please see attached example.png)

I am reading the SQL data in C# and then passing the data to jQuery to then populate the drop down tree but I'm having trouble figuring out how to create the data structure to be passed back to jQuery.

Any ideas or suggestions would be greatly appreciated

 

[Mathew]

Forgot to mention: the Parent ID column contains the value from the ID column for the parent option. 

[Tsvetomir]

Hi Mathew,

To resolves this issue, in the Action of the .Read method you will have to pass a Json object and a controller. You can refer to the following code snippet which achieves that:

public JsonResult GetEmployees(int? id)
{
    NorthwindEntities dataContext = new NorthwindEntities();

    var employees = from e in dataContext.Employees
                    where (id.HasValue ? e.ReportsTo == id : e.ReportsTo == null)
                    select new
                    {
                        id = e.EmployeeID,
                        Name = e.FirstName + " " + e.LastName,
                        hasChildren = e.Employees1.Any()
                    };

    return Json(employees, JsonRequestBehavior.AllowGet);
}

You can check out the demo which demonstrates how to bind to a remote data source here: DropDownTree / Binding to remote data. 

Give this suggestions a try and feel free to let us know if this resolves the binding issue.

Regards,
Tsvetomir
Progress Telerik

[Mathew]

Hi Tsvetomir, 

I am using the DropDownTree in the KendoUI for jQuery package. Is there a similar solution for the KendoUI for jQuery DropDownTree?

[Tsvetomir]

Hi Mathew,

When using Kendo for jQuery you can set up the read method. It makes a request to the data base where you should pass the url from where the data has to be taken. You can also modify the model in the schema to resemble your specific scenario.You can only alternate the script functions in the View as following: 

<script>

    var data = new kendo.data.HierarchicalDataSource({

        schema: {

            model: {

                EmployeeID: "EmployeeID",

                hasChildren: "hasChildren"

            }

        },

        transport: {

            read: {

                url: '@Url.Action("GetEmployees", "Employees")',

                dataType: "json",

            }

        }

    });

 

    $(document).ready(function () {

        $("#dropdowntree").kendoDropDownTree({

            dataTextField: "Name",

            dataValueField: "EmployeeID",

            dataSource: data

        });

    });

</script>



Here is an example how you can parse the Data from the DataBase in the Controller and return a Json object:

public JsonResult GetEmployees(int? id)
{
    NorthwindEntities dataContext = new NorthwindEntities();

    var employees = from e in dataContext.Employees
                    where (id.HasValue ? e.ReportsTo == id : e.ReportsTo == null)
                    select new
                    {
                        id = e.EmployeeID,
                        Name = e.FirstName + " " + e.LastName,
                        hasChildren = e.Employees1.Any()
                    };

    return Json(employees, JsonRequestBehavior.AllowGet);
}

You can customize the model NorthwindEntities, which is actually an Entity Data Model, to meet your requirements.

Kind regards,
Tsvetomir
Progress Telerik