HierarchyID SQL 2008

[Rob Teegarden]

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

[Atanas Korchev]

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.

[Rob Teegarden]

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.

[Mark Mrozek]

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!

[T. Tsonev]

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.

[AM]

Has this feature been released in Q2 2010?

[Nikolay Tsenkov]

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

[Mac]

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.

[Nikolay Tsenkov]

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

[AM]

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.

[Peter]

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

[CCrotts]

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.

[Mako]

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

[Sonia]

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.

[Mako]

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

[Sonia]

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.

[Mako]

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

[Sonia]

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>

[Sonia]

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>

[Mako]

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

[Sonia]

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/

[Mako]

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 bigint) AS Id, CAST(REPLACE(Node.GetAncestor(1).ToString(), '/', '1') AS bigint) AS ParentId, Id.ToString() AS [Path], Id.GetLevel() AS [Level], Name FROM EmployeeWithHierarchyID

Again sorry for the confusion.

Thanks

Mako

[Mako]

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