[RESOLVED] Join Datatables for local Reporting

[Zeljko]

So I have made a first Mock-Up of my effort to
IT WORKS PERFECTLY! Join 4 hierarchically nested datatables in 1 single flattened datatable..

Code is posted here 'as is' so that others my find it useful or at least as a starting point...
If you have any ideas to refine this code (or part of it) I'm all ears

'I have 1 dataset and in that dataset 4 datatables with all needed columns created through designer
'every datatable name in original dataset has unique name (unique to all dataset and not just datatable)
'in every datatable columns are:
'   first column is Int32 - autogeneratedID - unique to table
'   second column is Int32 - ID - the same key as parent key (this field is binding conection to parent table: parent:child > one:many > autogeneratedID:ID
'   other columns as you like
'Expected result is 1 new dataset with 1 new flattened datatable created totally through code which conteins all above 4 datatables. Result is showned in new form in a datagridview manner...
 
Private Sub FlattenAndShowDatatable_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripBtnPrint.Click
'original datatables        
        Dim dsOrg As New DataSet
        Dim dt1 As DataTable = ds1.dt1o.Copy
        Dim dt2 As DataTable = ds1.dt2o.Copy
        Dim dt3 As DataTable = ds1.dt3o.Copy
        Dim dt4 As DataTable = ds1.dt4o.Copy
        dsOrg.Tables.AddRange(New DataTable() {dt1, dt2, dt3, dt4})
'preparing for new dataset/datatable
        Dim dsJoined As New DataSet
        Dim dtJ As New DataTable
        dsJoined.Tables.Add(dtJ)
 
'last table in original dataset (when I reach the end row in this table it moves to one higher parent table (curentIndexTable-1)
        Dim startTableIndx As Integer = dsOrg.Tables.Count - 1 
 
        Do
            Dim dr As DataRow = dtJ.NewRow
            Dim keyparent As String = Nothing
 
            For iTbl As Integer = startTableIndx To 0 Step -1
                For iRow As Integer = 0 To dsOrg.Tables(iTbl).Rows.Count - 1
 
                    Dim alreadyExist As Boolean = False
                    For i As Integer = 0 To dtJ.Rows.Count - 1
                        If iTbl = startTableIndx AndAlso dtJ.Rows(i).Item(dsOrg.Tables(iTbl).Columns(0).ToString).ToString = dsOrg.Tables(iTbl).Rows(iRow).Item(0).ToString Then
                            alreadyExist = True
                            Exit For
                        End If
                    Next
 
                    If alreadyExist = False Then
                        If dsOrg.Tables(iTbl).Rows(iRow).Item(0).ToString = keyparent Or keyparent = Nothing Then 'if its not correct parent, skip it
                            For iCol As Integer = 0 To dsOrg.Tables(iTbl).Columns.Count - 1
'add column if column name not existing in table
                                If Not dsJoined.Tables(0).Columns.Contains(dsOrg.Tables(iTbl).Columns(iCol).ToString) Then
                                    dsJoined.Tables(0).Columns.Add(dsOrg.Tables(iTbl).Columns(iCol).ToString)
                                End If
'HERE WE ACTUALLY POPULATING THE CELL IN NEW DATATABLE
                                dr(dsOrg.Tables(iTbl).Columns(iCol).ToString) = dsOrg.Tables(iTbl).Rows(iRow).Item(iCol)   'dr("ColumnName") = "aaa" ...add data to this column under that row
                                If iCol = 1 Then   'second Column contains ID of parent table
                                    keyparent = dsOrg.Tables(iTbl).Rows(iRow).Item(iCol).ToString
                                End If
                            Next iCol
                            Exit For
 
                        End If
                    End If
 
                    If iRow = dsOrg.Tables(startTableIndx).Rows.Count - 1 Then 
                        startTableIndx -= 1
                    End If
 
                Next iRow
            Next iTbl
 
'HERE WE ARE ACTUALLY ADDING A ROW POPULATED WITH CELLS TO NEW DATATABLE
            dtJ.Rows.Add(dr)
        Loop Until startTableIndx = -1
 
'showing the results in new form with datagridview
        Dim myForm As New Form
        Dim dgv As New DataGridView
        With dgv
            .Name = "dgv"
            .Location = New Point(15, 15)
            .Dock = DockStyle.Fill
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        End With
        myForm.Controls.Add(dgv)
        dgv.DataSource = dtJ
        'dgv.DataMember = "dtJ"
        myForm.WindowState = FormWindowState.Maximized
        myForm.Show()
    End Sub