3 level sql query schema

[David]

Hi 
   I currently have a 2 level tree view that displays departments and then assets within those departments. To do this I am using a sql stored procedure to prepare the data. See the attatched file to see the output of this stored procedure. I am then using the following c# code to bind the tree view.  

DataTable assets = new DataTable();

          using (SqlConnection connection = new SqlConnection(ConnectionString))
          {
              using (SqlCommand command = new SqlCommand("GetAssetHierarchyByUserId", connection))
              {
                  command.Parameters.Add(new SqlParameter("UserId", UserId));

                  command.CommandType = CommandType.StoredProcedure;

                  SqlDataAdapter adapter = new SqlDataAdapter(command);

                  connection.Open();

                  try
                  {
                      adapter.Fill(assets);
                  }
                  finally
                  {
                      connection.Close();
                  }
              }

My Question is, how do I go about adding another level of data to the tree. I want to now display SITES, that have Departments that have Assets. I.E I want to add a grand parent. what would be the correct schema for my stored procedure output to achieve this. 

Thanks
   Dave

[Nikolay Tsenkov]

Hello David,

The structure that I see on the provided screenshot is sufficient to create a multi-level (not limited by a specific constant) hierarchy of nodes.
Here is a nice article explaining in a bit better detail the Hierarchical Binding in RadTreeView: http://www.telerik.com/help/aspnet-ajax/treeview-data-binding-hierarchical.html


Regards,
Nikolay Tsenkov
the Telerik team

[Hassan]

Hello Nikolay,
I try bind hierarchical data to radtreeview above,but i need 3 level nodes.how can i do that?
Thanks

[Nikolay Tsenkov]

Hi Hassan,

If you are using a similar scheme and you want to bind a maximum of 3 levels (even when more exist), you can set the MaxDataBindDepth property of RadTreeView.

I hope this helps.

If this is not your case, please, further clarify.

Regards,
Nikolay Tsenkov
the Telerik team

[tafi]

DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT Id FROM Tree_Table WHERE Id NOT IN (SELECT DISTINCT ParentId FROM Tree_Table WHERE ParentId  IS NOT NULL)
 
DECLARE @IdPk INT 
OPEN c 
FETCH NEXT FROM c INTO @IdPk 
 WHILE (@@FETCH_STATUS = 0) 
 BEGIN 
  DECLARE @NodeValue VARCHAR(MAX) 
  SET @NodeValue = '' 
  WHILE(EXISTS(SELECT * FROM Tree_Table WHERE Id=@IdPk)) 
  BEGIN 
   SELECT  @NodeValue=Name+'/'+@NodeValue, 
   @IdPk=ParentId 
   FROM    Tree_Table 
   WHERE   id=@IdPk       
  END 
 PRINT 'Parent to Leaf: ' + LEFT(@NodeValue,LEN(@NodeValue)-1)  
 FETCH NEXT FROM c INTO @IdPk 
END 
CLOSE c 
DEALLOCATE c 

Detail Here...
Query For Showing Tree Type Value