Using strongly typed datasets to build a many-to-many relational table

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:
IDFirstNameAliases
2JasonDerek, Greg, John, Jay
3JoeGabe, Peanut, Zapp
4EduardoEddie, Blas, 2x4

I want the "aliases" data to be represented by two more tables. A list of known aliases:
IDAlias
2Derek
3Greg
4John
5Jay
[...][...]

And a relational table linking the original rows to the known aliases.
IDFirstNameAlias
2JasonDerek
3JasonGreg
4JasonJohn
5JasonJay
[...][...][...]

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.
 
G'd morning ikantspelwurdz,
Datasets and datatables expose the clear method. Something like this: dst.Tables(0).Rows.Clear().
For the second problem... it will happen with typed, not typed, direct connection or anything else. That's what integrity is for. That being said. Why do you need to clear the "KnownAliases" table programmatically? or better.. why do you have set referential integrity in that relationship?
 
Datasets and datatables expose the clear method.
Ok... how do I use this to clear the rows on disk? I tried this:
VB.NET:
        Dim KATA As New KnownAliasesTableAdapter
        Dim KADT As KnownAliasesDataTable = KATA.GetData
        KADT.Rows.Clear()
        Dim MKATA As New MainKnownAliasesMMTableAdapter
        Dim MKADT As MainKnownAliasesMMDataTable = MKATA.GetData
        MKADT.Rows.Clear()

This only clears the rows in memory, not on disk. Fiddling with the other exposed methods hasn't helped.

For the second problem... it will happen with typed, not typed, direct connection or anything else. That's what integrity is for.
Does that mean my approach is irreducibly complex? I would have thought that there would be a way to write a bunch of business objects to a database in one go, rather than having to write half of the association data to one table, then read it back into memory, and write the other half to the other table. It seems like a common enough problem that there would be a de facto way to do it.

Why do you need to clear the "KnownAliases" table programmatically?
The main table could change. The logic for extracting aliases might also change. Either way I would want to dump both of the new tables and re-create them.

why do you have set referential integrity in that relationship?
Actually, I don't. But I should - and then I'll need to swap the first two steps.
 
G'd Afternoon!
To Append/Update/Delete your underlying table you do the same
VB.NET:
KADT.rows.clear
KATA.Update(KADT)

"...It seems like a common enough problem that there would be a de facto way to do it: Here i'll have to agree with you. There is "defacto" way to solve it. If you use the VS data objects in your designer they take care of all the hard work.
But if you choose to do it by code, then you have to do (almost) everything by code. I'v assumed that you are doing it by code, so the solution is to first update one table and then the other. This makes sense, think about the Nortwhind Db. If you want to add an order you need to previously have customers and products. This is a very common task. If the customer doesn't exist you have to create a new one, just like in any shop, give your info, fill the form,wait and then process the order.

I think that you're missing a key point, when you say "I have to populate the KnownAliases table first and read it back into memory first." No, you don't have to. The main purpose of in memory objects is to minimize the trips to the server. In your case you don't need to load a bunch of rows just for delete them. Execute a procedure on the server and then load your dataset/datatable with no rows.
 
To Append/Update/Delete your underlying table you do the same
This didn't work. Here's a bit of code:
VB.NET:
        Dim MKATA As New MainKnownAliasesMMTableAdapter
        Dim MKADT As MainKnownAliasesMMDataTable = MKATA.GetData
        MsgBox("There are " & MKADT.Rows.Count & " rows stored in memory!")
        MKADT.Rows.Clear()
        MsgBox("There are " & MKADT.Rows.Count & " rows stored in memory!")
        MKATA.Update(MKADT)
        MKADT = MKATA.GetData
        MsgBox("There are " & MKADT.Rows.Count & " rows stored in memory!")
Output:
There are 2 rows stored in memory!
There are 0 rows stored in memory!
There are 2 rows stored in memory!

My understanding is that this reads 2 rows from disk into memory, then deletes them from memory, then adds the zero rows in memory to the table (i.e. does nothing at all), and then reads the 2 rows from disk back into memory.

If you use the VS data objects in your designer they take care of all the hard work.
Can you point me to an online tutorial that would show me how to do this? Most of the resources I've found online either use a confusing mishmash of old APIs, new APIs, and designer stuff, or they spend so much time covering concepts I already understand that I lose patience.

I think I'd rather let designer do the hard work if it can do that. I'm only using code because it's the best way that I know how.

I think that you're missing a key point, when you say "I have to populate the KnownAliases table first and read it back into memory first." No, you don't have to. The main purpose of in memory objects is to minimize the trips to the server. In your case you don't need to load a bunch of rows just for delete them. Execute a procedure on the server and then load your dataset/datatable with no rows.
Actually, when I said that, I was referring to the last steps of populating the tables, not the first steps of clearing it.

Here's an overview of the program as it stands:
Step 1: Clear "MainKnownAliasesMM" on the disk (one DB read, many DB writes)
Step 2: Clear "KnownAliases" on the disk (one DB read, many DB writes)
Step 3: Read "main" from the disk and create Suspect data objects (one DB read)
Step 4: Create a List of Aliases
Step 5: Write List of Aliases to "KnownAliases" on the disk (one DB write)
Step 6: Read "KnownAliases" from the disk and create a Collection of KnownAliasRows (one DB read)
Step 7: Write Suspect data objects to "MainKnownAliasesMM" on the disk, using items in Collection above as reference arguments (one DB write)

The two issues I have are that Step 1 and 2 have many DB writes, and that Step 6 just seems like it shouldn't be necessary. The bolded quote refers to my second issue.
 
G'd evening!
  1. To not make this reply longer that it needs, i'll try to answer in the same order you've asked.
  2. No, i couldn't find any descent tutorial. I'll keep searching and let your know.
  3. Just like you i avoid as much as i can to work with ado.net wizards. But in your case with many DBs i think you don't have a lot of options, to build your own classes to handle all your dbs could be very time consuming.
  4. Point taken. I was talking about the first two loops to clear your tables. To populate the tables, no matter what you do you will end up facing the same issue: with or without integrity, and that will define the "How" to handle the data.
Back to your code and the first question. I don't understand why it didn't update the underlying source with the clean dataset. I saw that the dataset rows statrus are not deleted!... Any way, may be later we have time to talk about it.
The data adapter has a deletecommand method that you can use to clean your rows. the code looks like this:
            dap.Fill(dst, "dtblTest")
            MsgBox(dst.Tables(0).Rows.Count)
            With dap
                .DeleteCommand = con.CreateCommand
                .DeleteCommand.CommandText = "DELETE * FROM tblTest"
                .DeleteCommand.ExecuteNonQuery()
            End With
            dap = New OleDbDataAdapter(cmd)
            dap.Fill(dst, "tblTest")
            MsgBox(dst.Tables(0).Rows.Count)
 
Back
Top