Question How do I get an external hierarchyID field working


Mar 23, 2012
Programming Experience
In my project I have a data set which is populated from a SQL Server 2008R2 database.
The source tables contain fields of type heirarchyID when these are imported into data tables within my data set their type is set to string.

I want to populate a tree view using one of these columns. I've found some sample code on CodeProject and tried to modify it for my needs, like this

    Private Sub LoadTreeSQLHierarchy(oTV As TreeView, oTable As DataTable, sKeyField As String, sTextField As String)[INDENT]
        Dim oNode As TreeNode

        'get an empty id to get the top node
        Dim iID As New SqlHierarchyId()

        'filter the table using linq.[COLOR=#ff0000]
        Dim query As EnumerableRowCollection(Of DataRow) = From TNodes In oTable.AsEnumerable() Where SqlHierarchyId.Parse(TNodes.Field(Of String)(sKeyField)).GetAncestor(1).Equals(iID) Select TNodes[/COLOR]

        'convert to a dataview because I am comfortable with a dataview.
        Dim oDV As DataView = query.AsDataView()
        If oDV.Count = 1 Then
            'load up a node
            oNode = New TreeNode(oDV(0)(sTextField).ToString())

            'put the datarow into the tag property
            oNode.Tag = oDV(0).Row

            'load up the children
            LoadNodeSQLHierarchy(oNode, oTable)

            'add the node hierarchy to the tree
        End If
    End Sub

(Skeyfield is a column which is of type hierarchyid in the underlying SQL table and type string in the datatable.)

The line in red doesn't work, you can't change the type in the LINQ query in the way I've attempted here, I can't work out a way to correctly recast it on the fly and I can't work out how to store a field of this type in a data table either. How should this be approached?
Top Bottom