joins on dataset.tables?

[theAsocialApe]

I'm making a little admin utility for updating an error message table. Over time, the messages in dev, test, and prod get out of synch, and so this page will use a gridview to pull from all three datasources, linking them up on their common primary key (errorid). edits or additions can only be made in dev, then user hits a button, and the message text, and errorid (if inserting) are put in test, another button click promotes from test to prod. deletes go against all three regions.

right now, i've got this -

Dim myTempTableDev As Data.DataTable
Dim myTempTableProd As Data.DataTable
Dim myTempTableTest As Data.DataTable


Dim testDS As Data.DataSet = New Data.DataSet

'fill myTempTableDev with data from dev

'fill myTempTableTest with data from test

'fill myTempTableProd with data from prod

'add them all to a dataset and rename them

testDS.Tables.Add(myTempTableDev)
testDS.Tables.Add(myTempTableTest)
testDS.Tables.Add(myTempTableProd)

testDS.Tables(0).TableName = "DEV"
testDS.Tables(1).TableName = "TEST"
testDS.Tables(2).TableName = "PROD"

now what I want to do is combine them into another table in testDS, and use that for the datasource for an asp gridview. the sql, if they were in a db would just be (this is just for testing, eventually, i'll be doing outer joins):

select d.id, d.description as descriptiondev t.description as descriptiontest, p.description as descriptionprod from DEV d inner join TEST t on d.id = t.id inner join PROD p on d.id = p.id

I think I remember there is a way to just use a data.dataset like a db - you can run sql on the tables in it, but i can't remember how.


Am I correct and if so, can you share some knowledge, or am I just getting senile?

Thanks.

 

[cjard]

youre doing your data access in a very hard way.. see the DW2 link in my signature, section "creating a simple data app" for more info

 

[theAsocialApe]

thanks. i'll check that out at lunch.

i think i was thinking about putting sql in a dataAdapter or something.
someone else pointed me to the new LINQ thing for .net, but we're not using that.

here's how i ended up solving it - still needs some cleanup (marking rows that don't exist in all three tables appropriately, and getting some 'EXIT FOR's' put in an what not, but it came out more simply than i had hoped.

    Private Sub joinTables(ByRef myTbl1 As Data.DataTable, ByVal myTbl2 As Data.DataTable, ByVal myTbl3 As Data.DataTable)
        Dim cleanUp(2) As String

        For Each drOuter As Data.DataRow In myTbl1.Rows  ' find all rows in myTbl2 that match to MyTbl1 on ID, and 
            For Each drInner As Data.DataRow In myTbl2.Rows 'put their error desc in MyTbl1
                If drOuter(0) = drInner(0) Then
                    drOuter(2) = drInner(1)
                    drInner.Delete()
                    Exit For
                End If
            Next
            myTbl2.AcceptChanges()
            myTbl1.AcceptChanges()
        Next
        If myTbl2.Rows.Count > 0 Then   'get all the leftover rows in myTbl 2 and add them to myTbl1
            For Each dr As Data.DataRow In myTbl2.Rows
                cleanUp(0) = dr(0)
                cleanUp(1) = ""
                cleanUp(2) = dr(1)
                myTbl1.Rows.Add(cleanUp)
                myTbl1.AcceptChanges()
            Next
        End If


        For Each drOuter As Data.DataRow In myTbl1.Rows  ' find all rows in myTbl3 that match to MyTbl1 on ID, and 
            For Each drInner As Data.DataRow In myTbl3.Rows 'put their error desc in MyTbl1
                If drOuter(0) = drInner(0) Then
                    drOuter(3) = drInner(1)
                    drInner.Delete()
                End If
            Next
            myTbl3.AcceptChanges()
            myTbl1.AcceptChanges()
        Next
        If myTbl3.Rows.Count > 0 Then
            For Each dr As Data.DataRow In myTbl3.Rows
                cleanUp(0) = dr(0)
                cleanUp(1) = ""
                cleanUp(3) = dr(1)
                myTbl1.Rows.Add(cleanUp)
                myTbl1.AcceptChanges()
            Next
        End If
    End Sub