'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
