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