none
Dataset and Hierarchial Data How to Sort RRS feed

  • Question

  • This is probably a dumb question, but I've hit a wall with this one at this current time. I have some data which is hierarchial in nature which is in an ADO.NEt dataset.

    The first field is the ID, the second is the Name, the third is the Parent ID.

    ID NAME Parent ID

    1 Air Handling NULL

    2 Compressor 1

    3 Motor 4

    4 Compressor 1

    5 Motor 2

    6 Controller 4

    7 Controller 2

    So the tree would look like the following:

    1- Air Handling

      4- Compressor 

          6 - Controller

          3 - Motor

      2- Compressor

          7- Controller

          5 - Motor

    What I'm trying to figure our is how to get the dataset in the same order that ths would be viewed in a treeview, which in this case is the levels at the appropriate levels for the nodes and then the children at the appropriate levels sorted by the name. It would be like binding this to a treeview and then simply working your way down the nodes to get the right order. Any links or direction would be greatly appreciated.

    Wednesday, June 9, 2010 5:56 PM

Answers

  • Hi,

     

    I modified the method using only .NET 2.0 methods for your references:  J

    http://msdn.microsoft.com/en-us/library/system.data.datatable.select.aspx

    http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

     

    ===================================================================

        Dim dt As New DataTable

        dt.Columns.Add("ID", GetType(Integer))

        dt.Columns.Add("Name", GetType(String))

        dt.Columns.Add("ParentID", GetType(Integer))

        dt.PrimaryKey = New DataColumn() { dt.Columns.Item(0) }

        Dim ds As New DataSet

        ds.Tables.Add(dt)

        ds.Relations.Add(dt.Columns.Item(0), dt.Columns.Item(2))

        dt.Rows.Add(New Object() { 1, "Air Handling", DBNull.Value })

        dt.Rows.Add(New Object() { 2, "Compressor", 1 })

        dt.Rows.Add(New Object() { 4, "Compressor", 1 })

        dt.Rows.Add(New Object() { 3, "Motor", 4 })

        dt.Rows.Add(New Object() { 5, "Motor", 2 })

        dt.Rows.Add(New Object() { 6, "Controller", 4 })

        dt.Rows.Add(New Object() { 7, "Controller", 2 })

        dt.AcceptChanges

        Dim list As New List(Of DataRow)

        BuildDataRowList(list, dt, Nothing)

    ===================================================================

    Private Sub BuildDataRowList(ByVal list As List(Of DataRow), ByVal table As DataTable, ByVal parentID As Nullable(Of Integer))

        Dim rowList As DataRow()

        Dim index As Integer = 0

        If Not parentID.HasValue Then

            rowList = table.Select("Isnull(ParentID, 0) = 0")

            list.InsertRange(0, rowList)

        Else

            rowList = table.Select(("ParentID=" & parentID.Value), "Name")

            index = list.IndexOf(table.Select(("ID=" & parentID.Value))(0))

            list.InsertRange((index + 1), rowList)

        End If

        Dim i As Integer

        For i = 0 To rowList.Length - 1

            BuildDataRowList(list, table, New Nullable(Of Integer)(CInt(rowList(i).Item("ID"))))

        Next i

    End Sub

    ===================================================================

    BTW, I wrote the C# version originally and use Reflector to convert it.   It may have some problems, please have a test and tell me whether it can help you solve the problem.

     

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, June 11, 2010 9:14 AM
    Moderator

All replies

  • Hello,

     

    Welcome to ADO.NET DataSet forum!

     

    Here are some sample codes to make the DataRows in hierarchy order for your references:

    ===================================================================

                DataTable dt = new DataTable();

                dt.Columns.Add("ID", typeof(int));

                dt.Columns.Add("Name", typeof(string));

                dt.Columns.Add("ParentID", typeof(int));

                dt.PrimaryKey = new DataColumn[] { dt.Columns[0] };

     

                DataSet ds = new DataSet();

                ds.Tables.Add(dt);

                ds.Relations.Add(dt.Columns[0], dt.Columns[2]);

     

                dt.Rows.Add(1, "Air Handling", DBNull.Value);

                dt.Rows.Add(2, "Compressor", 1);

                dt.Rows.Add(4, "Compressor", 1);

                dt.Rows.Add(3, "Motor", 4);

                dt.Rows.Add(5, "Motor", 2);

                dt.Rows.Add(6, "Controller", 4);

                dt.Rows.Add(7, "Controller", 2);

     

                dt.AcceptChanges();

     

                List<DataRow> list = new List<DataRow>();

                BuildDataRowList(list, dt.AsEnumerable(), null);

     

    void BuildDataRowList(List<DataRow> list, IEnumerable<DataRow> table, int? parentID)

            {

                DataRow[] rowList;

                int index = 0;

                if (parentID == null)

                {

                    rowList = table.Where(r => r["ParentID"] == DBNull.Value).ToArray();

                    list.InsertRange(0, rowList);

                }

                else

                {

                    rowList = table.Where(r => r["ParentID"] != DBNull.Value && (int)r["ParentID"] == parentID.Value).OrderBy(r => r.Field<string>("Name")).ToArray();

                    index = list.IndexOf(table.First(r => r.Field<int>("ID") == parentID.Value));

                    list.InsertRange(index + 1, rowList);

                }

     

                for (int i = 0; i < rowList.Length; i++)

                {

                    BuildDataRowList(list, table, rowList[i].Field<int>("ID"));

                }

            }

    ===================================================================

    I used LINQ to DataSet and recursion methods to build the new DataRow list.  

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, June 10, 2010 6:26 AM
    Moderator
  • Thanks,

     

    any chance you have this in VB.NET?  If not, I'll try to use a converted to bring over from C# to VB.NET. 

    Thursday, June 10, 2010 8:46 PM
  • Actually, would be good to see if there is a method to do this in .NET 2.0 environment, as this is the platform that we are developing for. 
    Friday, June 11, 2010 1:25 AM
  • Hi,

     

    I modified the method using only .NET 2.0 methods for your references:  J

    http://msdn.microsoft.com/en-us/library/system.data.datatable.select.aspx

    http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

     

    ===================================================================

        Dim dt As New DataTable

        dt.Columns.Add("ID", GetType(Integer))

        dt.Columns.Add("Name", GetType(String))

        dt.Columns.Add("ParentID", GetType(Integer))

        dt.PrimaryKey = New DataColumn() { dt.Columns.Item(0) }

        Dim ds As New DataSet

        ds.Tables.Add(dt)

        ds.Relations.Add(dt.Columns.Item(0), dt.Columns.Item(2))

        dt.Rows.Add(New Object() { 1, "Air Handling", DBNull.Value })

        dt.Rows.Add(New Object() { 2, "Compressor", 1 })

        dt.Rows.Add(New Object() { 4, "Compressor", 1 })

        dt.Rows.Add(New Object() { 3, "Motor", 4 })

        dt.Rows.Add(New Object() { 5, "Motor", 2 })

        dt.Rows.Add(New Object() { 6, "Controller", 4 })

        dt.Rows.Add(New Object() { 7, "Controller", 2 })

        dt.AcceptChanges

        Dim list As New List(Of DataRow)

        BuildDataRowList(list, dt, Nothing)

    ===================================================================

    Private Sub BuildDataRowList(ByVal list As List(Of DataRow), ByVal table As DataTable, ByVal parentID As Nullable(Of Integer))

        Dim rowList As DataRow()

        Dim index As Integer = 0

        If Not parentID.HasValue Then

            rowList = table.Select("Isnull(ParentID, 0) = 0")

            list.InsertRange(0, rowList)

        Else

            rowList = table.Select(("ParentID=" & parentID.Value), "Name")

            index = list.IndexOf(table.Select(("ID=" & parentID.Value))(0))

            list.InsertRange((index + 1), rowList)

        End If

        Dim i As Integer

        For i = 0 To rowList.Length - 1

            BuildDataRowList(list, table, New Nullable(Of Integer)(CInt(rowList(i).Item("ID"))))

        Next i

    End Sub

    ===================================================================

    BTW, I wrote the C# version originally and use Reflector to convert it.   It may have some problems, please have a test and tell me whether it can help you solve the problem.

     

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, June 11, 2010 9:14 AM
    Moderator
  • Hi,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 15, 2010 1:37 AM
    Moderator