ikantspelwurdz
Well-known member
- Joined
- Dec 8, 2009
- Messages
- 49
- Programming Experience
- 1-3
I have an access file with a main table that looks like this:
I want the "aliases" data to be represented by two more tables. A list of known aliases:
And a relational table linking the original rows to the known aliases.
I've gone ahead and made the tables and relations in the database. I wrote code that will clear the tables and then populate them, but it seems like there should be an easier way.
First, deleting each record takes forever. Is there a way to delete all the records at once, using strongly typed datasets?
Second, writing to the relational table just seems much more complicated than it should be. The "Suspect" structure already contains the many-to-many relation data in memory, but there doesn't seem to be a straightforward way to just write that data to the table - I have to populate the KnownAliases table first and read it back into memory first.
ID | FirstName | Aliases |
2 | Jason | Derek, Greg, John, Jay |
3 | Joe | Gabe, Peanut, Zapp |
4 | Eduardo | Eddie, Blas, 2x4 |
I want the "aliases" data to be represented by two more tables. A list of known aliases:
ID | Alias |
2 | Derek |
3 | Greg |
4 | John |
5 | Jay |
[...] | [...] |
And a relational table linking the original rows to the known aliases.
ID | FirstName | Alias |
2 | Jason | Derek |
3 | Jason | Greg |
4 | Jason | John |
5 | Jason | Jay |
[...] | [...] | [...] |
I've gone ahead and made the tables and relations in the database. I wrote code that will clear the tables and then populate them, but it seems like there should be an easier way.
VB.NET:
Structure Suspect
Public MR As mainRow
Public Aliases As List(Of String)
End Structure
Sub Main()
'Clears the old data from KnownAliases
Dim KATA As New KnownAliasesTableAdapter
Dim KADT As KnownAliasesDataTable = KATA.GetData
For Each KAR As KnownAliasesRow In KADT
KATA.Delete(KAR.ID, KAR._Alias)
Next
'Clears the old data from MainKnownAliasesMM
Dim MKATA As New MainKnownAliasesMMTableAdapter
Dim MKADT As MainKnownAliasesMMDataTable = MKATA.GetData
For Each MKAR As MainKnownAliasesMMRow In MKADT
MKATA.Delete(MKAR.ID, MKAR.FirstName, MKAR._Alias)
Next
'Reads main table, creates list of Suspects:
Dim MTA As New mainTableAdapter
Dim MDT As mainDataTable = MTA.GetData
Dim suspects As New List(Of Suspect)
For Each MR As mainRow In MDT
Dim s As New Suspect
s.MR = MR
s.Aliases = New List(Of String)
Dim aliasArray() As String = MR.Aliases.Split({" "c, ","c})
For Each a As String In aliasArray
If Not a Is Nothing And Not a = "" Then
If Not s.Aliases.Contains(a) Then
s.Aliases.Add(a)
End If
End If
Next
suspects.Add(s)
Next
'Reads list of suspects, creates list of aliases
Dim aliases As New List(Of String)
For Each s As Suspect In suspects
For Each a As String In s.Aliases
If Not aliases.Contains(a) Then
aliases.Add(a)
End If
Next
Next
'Reads list of aliases, creates KnownAliasesTable
KADT = New KnownAliasesDataTable
For Each a As String In aliases
KADT.AddKnownAliasesRow(a)
Next
'Writes KnownAliasTable to KnownAliases
KATA.Update(KADT)
'Reads KnownAliases, creates collection of KnownAliasRows
Dim KnownAliasRows As New Collection
KADT = KATA.GetData
For Each KAR As KnownAliasesRow In KADT
KnownAliasRows.Add(KAR, KAR._Alias)
Next
'Reads list of suspects and list of KnownAliasRows, creates MainKnownAliasesTable
MKADT = New MainKnownAliasesMMDataTable
For Each s As Suspect In suspects
For Each a As String In s.Aliases
MKADT.AddMainKnownAliasesMMRow(s.MR, KnownAliasRows(a))
Next
Next
'Writes MainKnownAliasesTable to MainKnownAliasesMM
MKATA.Update(MKADT)
End Sub
First, deleting each record takes forever. Is there a way to delete all the records at once, using strongly typed datasets?
Second, writing to the relational table just seems much more complicated than it should be. The "Suspect" structure already contains the many-to-many relation data in memory, but there doesn't seem to be a straightforward way to just write that data to the table - I have to populate the KnownAliases table first and read it back into memory first.