Compare Datarows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello. I have in dataset, two datatables. Each table has only two columns.
What I wish to do is to compare 1st table to see if the datarow can be found
in 2nd table. For example, lets say we are comparing student last name and
student ID, using both tables. Process is to retrieve the last name and ID
from 1st table, then iterate through 2nd table to see if data can be found in
the columns. I'm not sure if a comparison of both columns must be done or if
the comparison can be done at a higher level. Any info will be helpfull. I
use C# 05

toco
 
Datarelations are most likely your best bet, they are already optimized for
the hash/lookup searching. Also, if studentID is the unique identifier in
the parent table, and the child tables use that value as their foriegn key
then that is all you need for your searches. (See below example)

Assuming:

Table 1:
StudentID int not null Identity(1,1)
Lastname varchar not null

Table 2:
StudentID int not null,
Lastname varchar not null

and the dataset is not strongly typed

Finally: given a windows form, add two datagrids(datagridviews) and a button

Private mDS As DataSet
Private Const maxMatchCount As Integer = 250

Private Sub DataGridView1_SelectionChanged(ByVal sender As Object, ByVal e
As System.EventArgs) Handles DataGridView1.SelectionChanged

Me.DataGridView2.DataSource = Nothing

Dim dt As DataTable = CType(Me.DataGridView1.DataSource, DataTable)
Dim dr As DataRow = CType(dt.Rows(Me.DataGridView1.CurrentRow.Index),
DataRow)
Dim iID As Integer = dr("StudentID")

Dim drv As DataView

drv = New DataView(mDS.Tables("Student Table2"),
String.Format("StudentID = '{0}'", iID), String.Empty,
DataViewRowState.CurrentRows)

Me.DataGridView2.DataSource = drv 'mDS.Tables("Student
Table2").Select(String.Format("StudentID = '{0}'", iID))

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Me.DataGridView1.DataSource = Nothing
Me.DataGridView1.DataSource = Nothing

mDS = New DataSet

'Create tables
With mDS
.Tables.Add("Student Table1")
.Tables.Add("Student Table2")
End With

'Define schema
For i As Integer = 1 To 2
With mDS.Tables(String.Format("Student Table{0}", i))
.Columns.Add("StudentID", GetType(Integer))

If i = 1 Then
'Table 1 will have it's studentID set to autoincrement
With .Columns("StudentID")
.AutoIncrement = True
.AutoIncrementSeed = -1
.AutoIncrementStep = -1
End With
End If

.Columns.Add("LastName", GetType(String))

End With
Next

'Create a relationship for easy lookup
mDS.Relations.Add("Table1Table2", mDS.Tables("Student
Table1").Columns("StudentID"), mDS.Tables("Student
Table2").Columns("StudentID"))

'Populate the tables with some arbitrary data
For i As Integer = 1 To maxMatchCount
Dim dr As DataRow = mDS.Tables("Student Table1").NewRow()
Dim j As Integer, k As Integer

With dr
.Item("Lastname") = String.Format("Student #{0}", i)
End With

mDS.Tables("Student Table1").Rows.Add(dr)

'Determine how many child matches to make for the child table
j = CInt(Int((maxMatchCount * Rnd()) + 1))

For k = 1 To j
Dim dr1 As DataRow = mDS.Tables("Student Table2").NewRow
'In this instance, the child rows will simply be a copy of the
parent row
dr1.ItemArray = dr.ItemArray
mDS.Tables("Student Table2").Rows.Add(dr1)
Next

Next

Me.DataGridView1.DataSource = mDS.Tables("Student Table1")

End Sub
 
Back
Top