HierarchyID SQL 2008

23 posts, 0 answers
  1. Rob Teegarden
    Rob Teegarden avatar
    25 posts
    Member since:
    Feb 2008

    Posted 10 Sep 2008 Link to this post

    Hi All,

    I was wondering if anyone is using the HierarchyID to create XML for the treeview control, or even better if Telerik is making a change to bind a treeview to a dataset with the HierarchyID datatype.

    I found this post, but there was no follow up from Telerik.

    Thanks
  2. Atanas Korchev
    Admin
    Atanas Korchev avatar
    8462 posts

    Posted 11 Sep 2008 Link to this post

    Hello Rob,

    As we said in the other thread we still don't provide official support for that SQL 2008 feature. Once we have such support we will announce it. Unfortunately we cannot commit with a deadline with this regards.

    Regards,
    Albert
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Rob Teegarden
    Rob Teegarden avatar
    25 posts
    Member since:
    Feb 2008

    Posted 11 Sep 2008 Link to this post

    I understand, but keep in mind that thread was over 6 months ago.  It could have easily been overlooked.

    I still would be interested to know if anyone else is using HierarchyID in their treeview.  I have put together something using server side expand, but it is nothing out of the ordinary.
  5. Mark Mrozek
    Mark Mrozek avatar
    6 posts
    Member since:
    Jul 2006

    Posted 09 Oct 2009 Link to this post

    We are indeed using the HierarchyID datatype in SQL 2008 and I just ran into the same issue with binding it to Telerik controls. Has this support been added and I missed it? To be specific we are looking to do two things using the most excellent Telerik controls such as the treeview or any other controls that support hierarchy:

    1. Simply represent the hierarchy easily using the hierarchyid rather than parent child. We can "fake it" by making our SQL query create a parent id based on the hierarchyid, but it sort of defeats the point - making hierarchies easier to query.

    2. The tougher one... we want to flip the tree so based on the hierarchy id we can show one of the child nodes with all upstream parents in the data structure actually displayed as children in the tree. The idea here would be to show a supply chain network both upstream and downstream from a given point in the middle.

    Any suggestions are much appreciated! Keep up the great work on your controls!
  6. T. Tsonev
    Admin
    T. Tsonev avatar
    2772 posts

    Posted 16 Oct 2009 Link to this post

    Hello,

    We thing that native support for the HierarchyID data type (it's string representation at least) will be a nice thing to have. We have our hands full at the moment with the Q3 release, but we'll include it in the to-do list for the Q1 2010 release.

    We actually use very similar format for the internal representation of the TreeView hierarchy, so binding to it shouldn't be too hard.

    As for your second question, I'd suggest that you use some of the Load On Demand modes and build the desired set of nodes on the server. You'll still have to query the database manually as I'm not sure how the TreeView itself can help here.

    Best wishes,
    Tsvetomir Tsonev
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  7. AM
    AM avatar
    17 posts
    Member since:
    Mar 2010

    Posted 30 Sep 2010 Link to this post

    Has this feature been released in Q2 2010?
  8. Nikolay Tsenkov
    Admin
    Nikolay Tsenkov avatar
    734 posts

    Posted 06 Oct 2010 Link to this post

    Hello Akhila Mathews,

    This feature is still not implemented, because it's priority dropped soon after this thread was closed.
    After all this is a clear indication that the feature is still desired, we will consider it again and will inform you of our decision.

    Hope that soon we will send some good news to you or at least that it will not be too inconvenient if the final decision is negative.


    Kind Regards,
    Nikolay Tsenkov
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  9. Mac
    Mac avatar
    18 posts
    Member since:
    Mar 2010

    Posted 29 Oct 2010 Link to this post

    I'd also like to see this and even potentially support in OpenAccess.

    One difference though, is I'm using the MVC Extensions TreeView not the RAD version.
  10. Nikolay Tsenkov
    Admin
    Nikolay Tsenkov avatar
    734 posts

    Posted 03 Nov 2010 Link to this post

    Hello Calvin,

    This is a forum thread for ASP.NET AJAX version of RadTreeView. Please, open a thread in the forum for ASP.NET MVC RadTreeView.

    Hope that soon your problem will be resolved!


    Regards,
    Nikolay Tsenkov
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  11. AM
    AM avatar
    17 posts
    Member since:
    Mar 2010

    Posted 19 Nov 2010 Link to this post

    Hi,

    I've implemented 'Dynamic population of Radtreeview using SQL Hierarchy datatype' using this link here:
    http://www.codeproject.com/KB/cs/TreeViewFromHierarchyID.aspx

    Any idea how to get the LoadOnDemand working for this code?

    Thanks.
  12. Peter
    Admin
    Peter avatar
    6637 posts

    Posted 24 Nov 2010 Link to this post

    Hello Akhila,

    Tha project does not use RadTreeView, but we have an online demo which can help you get started with your requirement:

    http://demos.telerik.com/aspnet-ajax/treeview/examples/programming/loadondemandmodes/defaultcs.aspx



    Peter
    the Telerik team
    Browse the vast support resources we have to jumpstart 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.
  13. CCrotts
    CCrotts avatar
    1 posts
    Member since:
    Feb 2010

    Posted 11 Aug 2011 Link to this post

    Hello,

    I wanted to follow up on utilizing the Hierarchy ID directly within the tree view controls.

    Any progress on adding this data type natively to the AJAX suite?

    Thanks.
  14. Mako
    Mako avatar
    12 posts
    Member since:
    Aug 2011

    Posted 08 Mar 2012 Link to this post

    Hi,

    Assuming you have setup a table according to the new format given in this (rather good) blog example:

    http://blogs.msdn.com/b/simonince/archive/2008/10/17/hierarchies-with-hierarchyid-in-sql-2008.aspx

    Fill a Datatable using a select query of:

    SELECT

        Id,
        Id.GetAncestor(1) AS ParentId,

        Id.ToString() AS [Path],

        Id.GetLevel() AS [Level],

        Name

    FROM EmployeeWithHierarchyID

    WHERE Id.IsDescendantOf('/5/') = 1

    and assuming you have treeview control called tvEmpTree:

    [VB.Net]

    With tvEmpTree
           .DataSource = dt 'Filled with Select Statement
           .DisplayMember = "Name"
           .ChildMember = "Id"
           .ParentMember = "ParentId"
           .ValueMember = "Id"
    End With



    [C#]

    tvEmpTree.DataSource = dt; /*Filled with Select Statement*/
    tvEmpTree.DisplayMember = "Name";
    tvEmpTree.ChildMember = "Id";
    tvEmpTree.ParentMember = "ParentId";
    tvEmpTree.ValueMember = "Id";


    The Treeview Control does not seem to mind that the Child and Parent Members are the HierarchyID Type and it works very well as far as I've seen.

    Enjoy

    Mako
  15. Sonia
    Sonia avatar
    90 posts
    Member since:
    Mar 2012

    Posted 25 Mar 2013 Link to this post

    I am interested in using hierarchyid property provided by SQL Server 2008 with RadTreeView or RadTreeList.
    Nowadays, is it possible to do that?

    Thank you.
  16. Mako
    Mako avatar
    12 posts
    Member since:
    Aug 2011

    Posted 25 Mar 2013 Link to this post

    Hi Sonia,

    My post above is an example of a RadTreeView using hierarchyid, I haven't used RadTreeList recently but it should easily scan across.

    Thanks

    Mako
  17. Sonia
    Sonia avatar
    90 posts
    Member since:
    Mar 2012

    Posted 26 Mar 2013 Link to this post

    Hi Mako,

    I have tried your example however, 
    Telerik.Web.UI.RadTreeView does not contain a definition for 'DisplayMember'
    nor for 'ChildMember', 'ParentMember', etc.

    Were is the mistake?

    Thank you,
    Sonia.
  18. Mako
    Mako avatar
    12 posts
    Member since:
    Aug 2011

    Posted 26 Mar 2013 Link to this post

    Hi Sonia,

    Seems like there names have changed from when I did this example, there is no mistake as it were the names have just changed, the new names are based on the old names which in my example are:

    treeView.DataTextField = DisplayMember
    treeView.DataValueField = ValueMember 
    treeView.DataFieldID = ChildMember
    treeView.DataFieldParentID = ParentMember 

    There is also an article on this in the offical documention here:

    http://www.telerik.com/help/aspnet-ajax/treeview-data-binding-hierarchical.html

    Thanks

    Mako
  19. Sonia
    Sonia avatar
    90 posts
    Member since:
    Mar 2012

    Posted 26 Mar 2013 Link to this post

    Thank you Mako for the correlation between the old and the new properties.

    Now i do not get any error however i cannot see anything in the tree when i am using hierarchyids. I have tested the same code with integer ids and the tree loads perfectly, so i guess that is a problem with the data type.

    RadTreeView1 loads the content of the tree, but not RadTreeView2. The difference is in the sql tables:
    CREATE TABLE [dbo].[Employee](
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
        [LoginID] [nvarchar](256) NOT NULL,
        [ManagerID] [int] NULL,
     CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED ([EmployeeID] ASC)
    )
     
    CREATE TABLE [dbo].[EmployeeWithHierarchyID](
        [Id] [hierarchyid] NOT NULL,
        [Name] [nvarchar](50) NOT NULL,
    PRIMARY KEY CLUSTERED ( [Id] ASC )
    )

    TreeView with integer ids & parent ids:<br />
    <telerik:RadTreeView ID="RadTreeView1" runat="server" Width="100%" Height="250px"
            DataFieldID="Id" DataFieldParentID="ParentId" DataTextField="Name"
            DataSourceID="SqlDataSource1">
            <DataBindings>
                <telerik:RadTreeNodeBinding Expanded="true"></telerik:RadTreeNodeBinding>
            </DataBindings>
        </telerik:RadTreeView>
        <br /><br />
     
    TreeView with hierarchyids:<br />
        <telerik:RadTreeView ID="RadTreeView2" runat="server" Width="100%" Height="250px"
            DataFieldID="Id" DataFieldParentID="ParentId" DataTextField="Name"
            DataSourceID="SqlDataSource2">
            <DataBindings>
                <telerik:RadTreeNodeBinding Expanded="true"></telerik:RadTreeNodeBinding>
            </DataBindings>
        </telerik:RadTreeView>
      
    <asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
    SelectCommand="SELECT EmployeeID AS Id, ManagerID AS ParentId, LoginID AS Name From Employee"></asp:SqlDataSource>
    <asp:SqlDataSource runat="server" ID="SqlDataSource2" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
    SelectCommand="SELECT Id, Id.GetAncestor(1) AS ParentId, Id.ToString() AS [Path], Id.GetLevel() AS [Level], Name FROM EmployeeWithHierarchyID"></asp:SqlDataSource>
  20. Sonia
    Sonia avatar
    90 posts
    Member since:
    Mar 2012

    Posted 26 Mar 2013 Link to this post

    A solution (not the best, sure!) to avoid that problem could be adding a new field to the EmployeeWithHierarchyID table.
    If you have any other best solution, please share it with me.

     

    CREATE TABLE [dbo].[EmployeeWithHierarchyID](
        [Id] [hierarchyid] NOT NULL,
        [Name] [nvarchar](50) NOT NULL,
        [IdInteger] [int] NULL,
    PRIMARY KEY CLUSTERED ( [Id] ASC )
    ) ON [PRIMARY]

     

     

     

    <asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
    SelectCommand="SELECT e.IdInteger AS Id, e2.IdInteger AS ParentId, e.Name FROM EmployeeWithHierarchyID AS e LEFT JOIN EmployeeWithHierarchyID AS e2 ON e.Id.GetAncestor(1) = e2.Id"></asp:SqlDataSource>
  21. Mako
    Mako avatar
    12 posts
    Member since:
    Aug 2011

    Posted 26 Mar 2013 Link to this post

    Hi Sonia,

    I can confirm it does not like hierarchyid as a field ID datatype, the moment I changed it to a int type it worked instantly.

    Your solution works but you save yourself having to create ints by using the .ToString() method on the HID, an example using your select statement:

    SELECT Id, Id.GetAncestor(1) AS ParentId, Id.ToString() AS [Path], Id.GetLevel() AS [Level], Name FROM EmployeeWithHierarchyID


    TO

    SELECT CAST(REPLACE(Node.ToString(), '/', '') AS int) AS Id, CAST(REPLACE(Node.GetAncestor(1).ToString(), '/', '') AS int) AS ParentId, Id.ToString() AS [Path], Id.GetLevel() AS [Level], Name FROM EmployeeWithHierarchyID


    The reason this works is because when you output a HID to a string the notation is the old string HID format, example output:

    Id IdString
    0x /
    0x58 /1/
    0x5AC0 /1/1/
    0x5AD6 /1/1/1/
    0x5AD6B0 /1/1/1/1/
    0x5AD6D0 /1/1/1/2/
    0x5AD6F0 /1/1/1/3/
    0x5AD708 /1/1/1/4/
    0x5AD718 /1/1/1/5/
    0x5AD728 /1/1/1/6/

    The REPLACE just removes the "/" symbol and CAST transforms to int, depending on how big your HID structure is you can CAST to bigint and the control will still work.

    Hope that helps.

    Thanks

    Mako
  22. Sonia
    Sonia avatar
    90 posts
    Member since:
    Mar 2012

    Posted 26 Mar 2013 Link to this post

    Hi Mako,

    If i am not mistaken in your solution two different nodes will be casted to the same integer id, for example:

    /1/1/
    /11/

  23. Mako
    Mako avatar
    12 posts
    Member since:
    Aug 2011

    Posted 26 Mar 2013 Link to this post

    Hi Sonia,

    You're correct, sorry about that, I was playing around with replacing the '/' with '1' but in some cases would create a very large number as thus would need a bigint, turns out I forgot to add that bit to the end of the last post.

    So for clarity the statement should read:

    SELECT CAST(REPLACE(Node.ToString(), '/''1'AS bigintAS Id, CAST(REPLACE(Node.GetAncestor(1).ToString(), '/''1'AS bigintAS ParentId, Id.ToString() AS [Path], Id.GetLevel() AS [Level], Name FROM EmployeeWithHierarchyID

    Again sorry for the confusion.

    Thanks

    Mako
  24. Mako
    Mako avatar
    12 posts
    Member since:
    Aug 2011

    Posted 26 Mar 2013 Link to this post

    Caution must be taken though as the max bigint number is 9,223,372,036,854,775,807.

    Also I will make no claim this is a fix, just workaround for small/medium data sets.

    Thanks

    Mako
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017