3 level sql query schema

5 posts, 0 answers
  1. David
    David avatar
    1 posts
    Member since:
    May 2011

    Posted 03 May 2011 Link to this post

    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
  2. Nikolay Tsenkov
    Admin
    Nikolay Tsenkov avatar
    734 posts

    Posted 04 May 2011 Link to this post

    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

    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Hassan
    Hassan avatar
    6 posts
    Member since:
    Sep 2012

    Posted 26 Nov 2011 Link to this post

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

    Posted 28 Nov 2011 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now
  6. tafi
    tafi avatar
    1 posts
    Member since:
    Jul 2014

    Posted 01 Jul 2014 Link to this post

    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
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017