How to sort a table without breaking column relationships when using "DefaultView.ToTable()"

Question

How to preserve relationships between tables after sorting

Related to the Designer.vb Part

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.AdvDgv2 = New AdvDgv()
        Me.AdvDgv1 = New AdvDgv()
        CType(Me.AdvDgv2, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.AdvDgv1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'AdvDgv2
        '
        Me.AdvDgv2.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.AdvDgv2.Location = New System.Drawing.Point(421, 3)
        Me.AdvDgv2.Name = "AdvDgv2"
        Me.AdvDgv2.Size = New System.Drawing.Size(546, 310)
        Me.AdvDgv2.TabIndex = 1
        '
        'AdvDgv1
        '
        Me.AdvDgv1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.AdvDgv1.Location = New System.Drawing.Point(2, 3)
        Me.AdvDgv1.Name = "AdvDgv1"
        Me.AdvDgv1.Size = New System.Drawing.Size(399, 310)
        Me.AdvDgv1.TabIndex = 0
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(979, 450)
        Me.Controls.Add(Me.AdvDgv2)
        Me.Controls.Add(Me.AdvDgv1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.AdvDgv2, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.AdvDgv1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

    Friend WithEvents AdvDgv1 As AdvDgv
    Friend WithEvents AdvDgv2 As AdvDgv
End Class

Related to the Namespace Part . which consists of 3 classes

form1 , MenuFillterSort , AdvDgv

This is my method. But I think I am wrong. There is a better way. Thank

Imports System.ComponentModel
Public Class Form1
    Dim WithEvents dtBase, dtDetails As New DataTable
    Dim ds As New DataSet
    Private Shadows WithEvents Menu As New MenuFillterSort()
    Dim rnd As New Random
    Public Sub New()
        ' This call is required by the designer.
        InitializeComponent()
        'Table Primary
        dtBase.Columns.Add("id", GetType(Integer))
        dtBase.Columns.Add("fName")
        dtBase.Columns.Add("family")
        For a = 0 To 10
            dtBase.Rows.Add({a, TextCreatorRandom(), TextCreatorRandom()})
        Next
        dtBase.PrimaryKey = New DataColumn() {dtBase.Columns("id")}
        'Table Child
        dtDetails.Columns.Add("id", GetType(Integer))
        dtDetails.Columns.Add("idOfPrimary", GetType(Integer))
        dtDetails.Columns.Add("fName")
        dtDetails.Columns.Add("family")
        dtDetails.Columns.Add("Qty", GetType(Integer))
        SetupDataSetWithRelations()
        For a = 0 To 5
            dtDetails.Rows.Add({a, a, dtBase.Rows.Find(a).Item("fName"), dtBase.Rows.Find(a).Item("family")})
        Next
    End Sub
    Sub SetupDataSetWithRelations()
        ds.Tables.Add(dtBase)
        ds.Tables.Add(dtDetails)
        ds.Relations.Add(New DataRelation("Id_idOfPrimary", dtBase.Columns("id"), dtDetails.Columns("idOfPrimary"), True))

        Dim MultiColumnParent As DataColumn() = {dtBase.Columns("ID"), dtBase.Columns("fName")}
        Dim MultiColumnChild As DataColumn() = {dtDetails.Columns("idOfPrimary"), dtDetails.Columns("fName")}
        ds.Relations.Add(New DataRelation("dtBase@dtDetails_fName", MultiColumnParent, MultiColumnChild, createConstraints:=True))

        MultiColumnParent = {dtBase.Columns("ID"), dtBase.Columns("family")}
        MultiColumnChild = {dtDetails.Columns("idOfPrimary"), dtDetails.Columns("family")}
        ds.Relations.Add(New DataRelation("dtBase@dtDetails_family", MultiColumnParent, MultiColumnChild, createConstraints:=True))
    End Sub
    Private Sub menu__ItemClicked(sender As Object, e As ToolStripItemClickedEventArgs, dgv As MenuFillterSort.DgvProperty) Handles Menu._ItemClicked
        Dim dicAscDesc As New Dictionary(Of String, String)
        dicAscDesc.Add("Sort A-Z", "Asc")
        dicAscDesc.Add("Sort Z-A", "Desc")
        dtBase.DefaultView.Sort = "[" & dgv.ColumnName & "] " & dicAscDesc(e.ClickedItem.Text)
        dgv.dgv.Set_DataTableToDataGridView(dtBase.DefaultView.ToTable)
    End Sub
    Function TextCreatorRandom() As String
        Dim stt As String = ""
        For a = 0 To rnd.Next(2, 6)
            stt += Microsoft.VisualBasic.Chr(rnd.Next(65, 91))
        Next
        Return stt
    End Function
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        AdvDgv1.dtAnyCheang = dtBase
        AdvDgv2.dtAnyCheang = dtDetails

        AdvDgv1.Set_DataTableToDataGridView(dtBase)
        AdvDgv2.Set_DataTableToDataGridView(dtDetails)

        AdvDgv1.Columns.Cast(Of DataGridViewColumn).ToList.ForEach(Sub(g) g.SortMode = DataGridViewColumnSortMode.Programmatic)
        AdvDgv2.Columns.Cast(Of DataGridViewColumn).ToList.ForEach(Sub(g) g.SortMode = DataGridViewColumnSortMode.Programmatic)
    End Sub
    Private Sub dtBase_RowChanging(sender As Object, e As DataRowChangeEventArgs) Handles dtBase.RowChanging

    End Sub
    Private Sub dtBase_RowChanged(sender As Object, e As DataRowChangeEventArgs) Handles dtBase.RowChanged
        If AdvDgv2.dtAnyCheang Is Nothing Then Exit Sub
        AdvDgv2.Set_DataTableToDataGridView(AdvDgv2.dtAnyCheang)
    End Sub
    Private Sub AdvDgv1__ButCellHeader(sender As ToolStrip, e As DataGridViewColumn) Handles AdvDgv1._ButCellHeader
        Menu.OuterRectangle = AdvDgv1.GetCellHeader(e)
        Menu.DgvP.ColumnName = e.Name
        Menu.DgvP.Column = AdvDgv1.Columns(e.Name)
        Menu.DgvP.dgv = AdvDgv1
        Menu.Menu.Show(AdvDgv1.GetCellHeader(e).Location)
    End Sub
End Class
Public Class MenuFillterSort
    Public WithEvents Menu As New ContextMenuStrip
    Structure DgvProperty
        Dim ColumnName As String
        Dim Column As DataGridViewColumn
        Dim dgv As AdvDgv
    End Structure
    Public DgvP As DgvProperty
    Public Event _ItemClicked(sender As Object, e As ToolStripItemClickedEventArgs, dgv As DgvProperty)
#Region "__________________________Move And Resize Form"
    Public Enum ResizeDirection
        None = 0
        Left = 1
        TopLeft = 2
        Top = 3
        TopRight = 4
        Right = 5
        BottomRight = 6
        Bottom = 7
        BottomLeft = 8
    End Enum
    Private _resizeDir As ResizeDirection = ResizeDirection.None
    Private Const WM_NCLBUTTONDOWN As Integer = &HA1
    Private Const HTBORDER As Integer = 18
    Private Const HTBOTTOM As Integer = 15
    Private Const HTBOTTOMLEFT As Integer = 16
    Private Const HTBOTTOMRIGHT As Integer = 17
    Private Const HTCAPTION As Integer = 2
    Private Const HTLEFT As Integer = 10
    Private Const HTRIGHT As Integer = 11
    Private Const HTTOP As Integer = 12
    Private Const HTTOPLEFT As Integer = 13
    Private Const HTTOPRIGHT As Integer = 14
    <System.Runtime.InteropServices.DllImport("user32.dll")>
    Public Shared Function ReleaseCapture() As Boolean
    End Function
    <System.Runtime.InteropServices.DllImport("user32.dll")>
    Public Shared Function SendMessage(ByVal hWnd As IntPtr, ByVal Msg As Integer, ByVal wParam As Integer, ByVal lParam As Integer) As Integer
    End Function
    Private Sub MoveForm(inObj As Object)
        ReleaseCapture()
        SendMessage(Menu.Handle, WM_NCLBUTTONDOWN, HTCAPTION, 0)
    End Sub
    Private Sub pnlMove_MouseDown(sender As Object, e As MouseEventArgs) Handles Menu.MouseDown ', lblLeftRight.MouseDown ', lblSheetName.MouseDown ',lstValidtion.MouseDown,trvValidtion.MouseDown
        Dim point3 As Point = New Point(Menu.Width - 15, Menu.Height - 15)
        Dim squareBounds As New Rectangle(Menu.Width - 15, Menu.Height - 15, 15, 15)
        Dim mousePosition As Point = Menu.PointToClient(Control.MousePosition)
        If squareBounds.Contains(e.Location) Then
            ResizeForm(ResizeDirection.BottomRight)
        Else
            MoveForm(sender)
        End If
    End Sub
    Private Sub ResizeForm(ByVal direction As ResizeDirection)
        Dim dir As Integer = -1
        Select Case direction
            Case ResizeDirection.Left
                dir = HTLEFT
            Case ResizeDirection.TopLeft
                dir = HTTOPLEFT
            Case ResizeDirection.Top
                dir = HTTOP
            Case ResizeDirection.TopRight
                dir = HTTOPRIGHT
            Case ResizeDirection.Right
                dir = HTRIGHT
            Case ResizeDirection.BottomRight
                dir = HTBOTTOMRIGHT
            Case ResizeDirection.Bottom
                dir = HTBOTTOM
            Case ResizeDirection.BottomLeft
                dir = HTBOTTOMLEFT
        End Select

        If dir <> -1 Then
            ReleaseCapture()
            SendMessage(Menu.Handle, WM_NCLBUTTONDOWN, dir, 0)
        End If
    End Sub
#End Region

    Public Sub New(Optional RectangleOutSide As Rectangle = Nothing, Optional dgv As DgvProperty = Nothing)
        Menu.Items.Add("Sort A-Z")
        Menu.Items.Add("Sort Z-A")
        DgvP.dgv = dgv.dgv
        DgvP.ColumnName = dgv.ColumnName
        OuterRectangle = RectangleOutSide
    End Sub
#Region "Outer rectangle cover checker"
    'The moment the menu frame opens, if it's supposed to cover it, the menu opens again but on top of the outer rectangle.
    Dim WithEvents tmrReloadButTop As New Timer
    Public OuterRectangle As Rectangle
    Dim bitToRelod As Boolean
    Private Sub tmrReloadButTop_Tick(sender As Object, e As EventArgs) Handles tmrReloadButTop.Tick
        tmrReloadButTop.Enabled = False
        Dim po As Point = OuterRectangle.Location
        po.Offset(0, -(Menu.Height + OuterRectangle.Height))
        bitToRelod = True
        Menu.Show(po)
    End Sub

    Private Sub Menu_Opening(sender As Object, e As CancelEventArgs) Handles Menu.Opening
        If bitToRelod = False AndAlso sender.Location.Y < OuterRectangle.Location.Y Then
            e.Cancel = True
            tmrReloadButTop.Enabled = False
            tmrReloadButTop.Enabled = True
            Exit Sub
        End If
    End Sub
    Private Sub Menu_Opened(sender As Object, e As EventArgs) Handles Menu.Opened
        bitToRelod = False
    End Sub

#End Region
    Private Sub Menu_ItemClicked(sender As Object, e As ToolStripItemClickedEventArgs) Handles Menu.ItemClicked
        RaiseEvent _ItemClicked(sender, e, DgvP)
    End Sub
End Class

Public Class AdvDgv 'Advanced DataGridView
    Inherits DataGridView
    Public Event _ButCellHeader(sender As ToolStrip, e As DataGridViewColumn)
    Public dtAnyCheang As DataTable
    Public Sub New()
        MyBase.New
    End Sub
    Public Sub Set_DataTableToDataGridView(inDataTable As DataTable)
        Dim gRow(inDataTable.Rows.Count - 1) As DataGridViewRow
        If Columns.Count = 0 Then
            For a = 0 To inDataTable.Columns.Count - 1
                Columns.Add(inDataTable.Columns(a).ColumnName, inDataTable.Columns(a).ColumnName)
                Columns(a).DataPropertyName = inDataTable.Columns(a).ColumnName
                Columns(a).ValueType = inDataTable.Columns(a).DataType
            Next
        End If
        Rows.Clear()
        For a = 0 To inDataTable.Rows.Count - 1
            Dim dataGridViewRow As New DataGridViewRow
            If inDataTable.Rows(a).ItemArray.Contains(DBNull.Value) Then
                Dim stt As Object() = inDataTable.Rows(a).ItemArray.AsEnumerable().Select(Function(r) IIf(r.GetType.Name = "DBNull", "", r)).ToArray
                dataGridViewRow.CreateCells(Me, stt)
            Else
                dataGridViewRow.CreateCells(Me, inDataTable.Rows(a).ItemArray)
            End If
            gRow(a) = dataGridViewRow
        Next
        Rows.AddRange(gRow)
    End Sub
    Public Function GetCellHeader(inColumn As DataGridViewColumn) As Rectangle
        Dim rec As Rectangle = GetColumnDisplayRectangle(inColumn.Index, True)
        rec.Height = ColumnHeadersHeight

        Dim pointOnScreen = PointToScreen(rec.Location)
        pointOnScreen.Offset(New Point(0, rec.Height))
        rec.Location = pointOnScreen
        'pCurrentCellRectangle = rec
        'gRec = rec
        Return rec
    End Function
    Private Sub AdvDgv_CellPainting(sender As Object, e As DataGridViewCellPaintingEventArgs) Handles Me.CellPainting
        If e.RowIndex = -1 AndAlso e.ColumnIndex > -1 Then
            Me.Controls("But_" & Columns(e.ColumnIndex).Name).Left = e.CellBounds.X + e.CellBounds.Width - Me.Controls("But_" & Columns(e.ColumnIndex).Name).Width
        End If
    End Sub
    Private Sub AdvDgv_ColumnAdded(sender As Object, e As DataGridViewColumnEventArgs) Handles Me.ColumnAdded
        Dim ButFS As New ToolStrip
        Dim but As New ToolStripButton With {.BackColor = Color.Green, .Width = 5, .Height = 5, .Padding = New Padding(0)}
        but.AutoSize = False
        but.Size = New Size(10, 10)
        ButFS.Name = "but_" & e.Column.Name
        ButFS.AutoSize = False
        ButFS.GripStyle = ToolStripGripStyle.Hidden
        ButFS.Size = New Size(12, 15)
        ButFS.Top = 7
        ButFS.Dock = DockStyle.None
        Dim sttControls As String() = Me.Controls.Cast(Of Control).Select(Function(d) d.Name).ToArray
        If Not sttControls.Contains(ButFS.Name) Then
            but.Name = e.Column.Name
            ButFS.Items.Add(but)
            AddHandler but.Click, AddressOf Button_Click
            Me.Controls.Add(ButFS)
        End If
    End Sub
    Private Sub Button_Click(ByVal sender As ToolStripButton, e As EventArgs)
        Dim ParentOfToolStripButton As ToolStrip = sender.GetCurrentParent
        RaiseEvent _ButCellHeader(ParentOfToolStripButton, Columns(sender.Name))
    End Sub

    Private Sub AdvDgv_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles Me.CellValueChanged
        Dim cell As DataGridViewCell = Item(e.ColumnIndex, e.RowIndex)
        Dim drow As DataRow() = dtAnyCheang.Select("[id] =" & CurrentRow.Cells("id").Value) 'Columns(e.ColumnIndex).Name
        drow(0).Item(Me.Columns(e.ColumnIndex).Name) = cell.Value
    End Sub
End Class

Answer 1

Hi @Mansour_Dalir ,

You should perform the sorting in such a way that it maintains the consistency of your data, especially when there are DataRelations involved.

1. Sort the parent DataTable (dtBase): You can use the DefaultView property to sort the DataTable. Then, instead of creating a new DataTable using ToTable(), update the existing DataTable.
2. Update the child DataTable (dtDetails): Ensure that the child DataTable is also updated to reflect the sorted order of the parent DataTable.
3. Rebind the sorted DataTables to the DataGridView: Reassign the sorted DataTable to the DataGridView.

      Public Sub SortDataTable(dt As DataTable, sortColumn As String, sortDirection As String)
          ' Perform the sort on the DefaultView
          dt.DefaultView.Sort = "[" & sortColumn & "] " & sortDirection
          ' Use a temporary DataTable to store sorted data
          Dim sortedDt As DataTable = dt.DefaultView.ToTable()
          ' Clear the original DataTable and import rows from the sorted DataTable
          dt.Rows.Clear()
          For Each row As DataRow In sortedDt.Rows
              dt.ImportRow(row)
          Next
      End Sub
      Private Sub Menu__ItemClicked(sender As Object, e As ToolStripItemClickedEventArgs, dgv As MenuFillterSort.DgvProperty) Handles Menu._ItemClicked
          Dim dicAscDesc As New Dictionary(Of String, String)
          dicAscDesc.Add("Sort A-Z", "Asc")
          dicAscDesc.Add("Sort Z-A", "Desc")
          Dim sortDirection As String = dicAscDesc(e.ClickedItem.Text)
          SortDataTable(dtBase, dgv.ColumnName, sortDirection)
          ' Rebind the sorted DataTables to the DataGridView
          AdvDgv1.Set_DataTableToDataGridView(dtBase)
          AdvDgv2.Set_DataTableToDataGridView(dtDetails)
      End Sub
      
   

   Best Regards.

Jiachen Li

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.