Bind SQL datasource to treegrid.

[Alex Jermy]

Hi,

I have a treegrid that I would like to bind a SQL datasource to. From the documentation it is not clear how to bind to a SQL datasource because in the example, the data is hardcoded. 

How is it possible to specify the column heads and then the records to expand.

For example, I want to display column headers representing data values - 'Job', 'Budget', 'Contract Type' etc. I then want to have departments down the left, but expandable to display each department's 'jobs', 'budgets per job', and 'contract type per job' etc. What is the best way to do this in code behind?

Thanks,

Alex

[Jesus Arockia Sankaran S]

Hi Alex, 

 

Thank you for contacting Syncfusion support.Query1: I have a treegrid that I would like to bind a SQL datasource.Answer: We have analyzed your requirement. We can bind the SQL data source to the TreeGrid control by using the SqlDataSource control, for this we need to map the SqlDataSource control ID to the TreeGrid DataSourceID property. In this SqlDataSource control we need to provide the connection string and “selectcommand” to fetch the data.  Please find our below sample browser sample link for this.Sample browser Link: https://asp.syncfusion.com/demos/web/treegrid/sqlbinding.aspxQuery2: How is it possible to specify the column heads and then the records to expand.Answer: In TreeGrid, we can define the required columns by using columns property. Please find the below code example

[ASPX] 

<ej:TreeGrid runat="server" ID="TreeGridControlSQLBinding" IdMapping="TaskId" 

     <Columns> 

      <ej:TreeGridColumn HeaderText="Task Id" Field="TaskId" Width="45" /> 

      <ej:TreeGridColumn HeaderText="Task Name" Field="TaskName" /> 

      <ej:TreeGridColumn HeaderText="Start Date" Field="StartDate" /> 

      <ej:TreeGridColumn HeaderText="End Date" Field="EndDate" /> 

      <ej:TreeGridColumn HeaderText="Duration" Field="Duration" /> 

      <ej:TreeGridColumn HeaderText="Progress" Field="Progress" /> 

    </Columns> 

    <SizeSettings Width="100%" Height="450px" /> 

  </ej:TreeGrid>

We can also define the required columns in the server side, please find the below code example

[CS] 

protected void Page_Load(object sender, EventArgs e) 

        { 

            var DataSource = this.GetDataSource(); 

            this.TreeGrid.DataSource = DataSource; 

            List<TreeGridColumn> colList = new List<TreeGridColumn>(); 

            colList.Add(new TreeGridColumn() { Field = "TaskId", HeaderText = "ID", Width = 45 }); 

            colList.Add(new TreeGridColumn() { Field = "TaskName", HeaderText = "Task Name" }); 

            colList.Add(new TreeGridColumn() { Field = "StartDate", HeaderText = "Start Date" }); 

            this.TreeGrid.Columns.AddRange(colList); 

            this.TreeGrid.DataBind(); 

        }

TreeGrid have the support for both Hierarchical and Self-Referential data binding. Please find the below documentation links for more information. 

 

Hierarchical DataBinding: https://help.syncfusion.com/aspnet/treegrid/data-binding#hierarchy-datasource-binding 

Self-Referential DataBinding: https://help.syncfusion.com/aspnet/treegrid/data-binding#self-referential-data-binding-flat-data 

 

When rendering the TreeGrid control with SQL database, we suggest you to use the self- reference data to maintain the parent-child relation. Can you please share us the code snippet or screenshots about structure of data source used in your project, it will be very helpful to provide the better solution. 

 

Regards, 

Jesus Arockia Sankaran S 

[Alex Jermy]

Hi,

Sorry for the late reply - I worked on a different project for a while.

I have tried to create a Treegrid of 'Snapshots'. These are essentially comparisons between certain business metrics between each months and are generated from SQL jobs.

I want to create a Treegrid that shows the month that a particular 'Snapshot' was taken ('snap' in my example) and for this to be the first Node in the Treegrid. On clicking the Snapshot, I want the Treegrid to expand to show all the 'Workpackages' (discrete units of work that are billed to a client) and allow the user to filter by 'Trade' and 'Billing Type'. I have prepared an example but it is not displaying. In the Treegrid I set 'ParentID' to 'EXT_Snapshot_Project_Snap_ID' (the unique identifier for a project) and the Childmapping to 'EXT_Snapshot_Workpackage_ID' (the unique identifier for a snapshot of a particular workpackage).

However the Treegrid is not displaying.

Is there something I need to set in the codebehind?

Thanks very mcuh

Attachment: Bands_511248f1.zip

[Jesus Arockia Sankaran S]

Hi Alex, 

 

We have checked the provided files and you have missed to add the necessary script and themes file in aspx page. And also we need to include ScriptManager in aspx page to render Syncfusion controls. 

Please check the below documentation link for this. 

Link – https://help.syncfusion.com/aspnet/gantt/getting-started 

 

In TreeGrid, we can bind two types of data source hierarchical data and self-reference data, please refer the following link to know more about this. 

Link – https://help.syncfusion.com/aspnet/gantt/data-binding 

 

In your code, snippet you are using ‘EXT_Snapshot_Project_Snap_ID’ as parent id and ‘EXT_Snapshot_Workpackage_Snap_ID’ as id value but this fields are not provided in the ‘WorkpackageSnapshots’ class. 

It is necessary to include ‘EXT_Snapshot_Project_Snap_ID’ to render the TreeGrid with parent-child relation. We have prepared a sample by directly getting the data from database. Please modify our provided sample based on your application and revert us. Also, please share the structure of the data that you passed to the TreeGrid since it will be helpful to provide better solution. 

 

Link- https://www.syncfusion.com/downloads/support/directtrac/general/ze/TreeGridSample670073235 

 

Regards, 

Jesus Arockia Sankaran S