Find Duplicate Records in a Datatable
I was asked how to find duplicate records in a datatable, I used a dataview to sort my records. I used the sorted list to check the next record for a duplicate record. The duplicate will be deleted.
Imports System.Security.Cryptography
Public Class Form1
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dt.Columns.Add("Name")
dt.Columns.Add("Number", GetType(Integer))
For x As Integer = 0 To 200
Select Case x Mod 3
Case 0
dt.LoadDataRow(New Object() {"Ken", TrueRandom.Rand(100)}, True)
Case 1
dt.LoadDataRow(New Object() {"Kelly", TrueRandom.Rand(100)}, True)
Case 2
dt.LoadDataRow(New Object() {"Bill", TrueRandom.Rand(100)}, True)
End Select
Next
DataGridView1.DataSource = dt
FindDups(dt)
End Sub
Public Sub FindDups(ByVal dt As DataTable)
Dim dv As New DataView(dt)
dv.Sort = "Number, Name"
For x As Integer = 0 To dv.Count - 2
Dim drv As DataRowView = dv.Item(x)
Dim drvNext As DataRowView = dv.Item(x + 1)
If drv.Item("Name").ToString = drvNext.Item("Name").ToString AndAlso drv.Item("Number").ToString = drvNext.Item("Number").ToString Then
drv.Delete()
End If
Next
End Sub
End Class
Public Class TrueRandom
Public Shared Function Rand(ByVal MaxNum As Integer) As Integer
Dim rnd(20) As Byte
Dim num As Long
Dim generator As New RNGCryptoServiceProvider
generator.GetBytes(rnd)
For x As Integer = 0 To 20
num += CInt(rnd(x))
Next x
Return CInt(num Mod MaxNum)
End Function
End Class