[RESOLVED] Join Datatables for local Reporting

[Zeljko]

I don't know how to join multiple datatables created in Designer in 1 single Datatable.
I need 1 Datatable with all data in it so I can give that single Datatable to one single Tablix in ReportViewer (rdlc).
Problem: ReportViewer Tablix can accept only one datatable at time and I need them all at once in one tablix.

How did I get stuck here:

* I have added Dataset from Solution Explorer to my Project (ds1)
* Double click on that item gave me Dataset Designer (ds1.xsd)
* Here I have created 4 Datatables with all needed columns, mutually connected with parent-child relationships (one to many, nested).
dt1 is parent to dt2, dt2 is parent to dt3, dt3 is parent to dt4 (dt1 > dt2 > dt3 > dt4).
* On my forms I have binded all my controls to this 4 Datatables and their respective columns so the data is populated hierarhicaly to dataset (as planed).
* I have added ReportViewer Control to one form for reporting purposes and Design a new report (rdlc)...

As I have read in past few days (read: week or two) I need to Join all my datatables to one single (flated) datatable somehow and then feed that DataTable to my Report.
So basicaly I have 2 questions:
1. How to join my 4 datatables? Can I do it from Designer or I must do it in code?
2. If I cant do joining from Designer how will I see this new datatable with all columns from my Report? If I wanna add a Dataset/DataTable to my Report I go to Tab Report Data - New - Dataset - comboToSelectExistingDatasets and if Datatable/Dataset is not created in Designer I can not see it here to add it to work with it.

I will be really really really thankful for any help I can get

Zeljko
vs2010, .net 3.5

[.paul.]

you can query your datatables with LINQ (in code) + create a new joined table
exactly how depends on the exact structure + contents of your datatables

[Zeljko]

My Datatables are looking like this:

ds1
---dt1
---aAutoID (PK)
---aUser
------dt2
------bAutoID (PK)
------baID (relation to dt1)
------bAdresses
---------dt3
---------cAutoID (PK)
---------cbID (relation to dt2)
---------cSubUsers
------------dt4
------------dAutoID (PK)
------------dcID (relation to dt3)
------------dSubSubUsers

dt1 > dt2 : one to many : aAutoID (PK) > baID
dt2 > dt3 : one to many : bAutoID (PK) > cbID
dt3 > dt4 : one to many : cAutoID (PK) > dcID

If I eventually find a way to join above tables in one flat datatable - programmatically,
how will I add that new Datatable to my Report if Report can not see it (it can only see what is in Designer-Solution explorer)?

[.paul.]

you need to create an empty datatable with the same fields as your dynamic datatable + set that as your datasource, then swap datasources at runtime

[Zeljko]

I was afraid of doing so much duplicate job but the more I think about it it seems like an easy solution to my problem.
So, I will create another Dataset with single Datatable and add (through designer) all columns from above 4 datatables to this datatable. Fortunately each column name in all 4datatables is unique to whole dataset so I can have exactly the same column names in the new single datatable

So, now I need idea on how to populate this new datatable with data from original 4 datatables?
How to loop through all datatables and not mess up the records?

[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

[.paul.]

did that work ok with your report?

[Zeljko]

Not there yet.
I'm still finishing some other stuff, and joining tables in to one flatten table was big break point form me.

Joining works. And yes I can 'import' it in report. Importing part (lets call it filtering and sorting) is not yet done but I will post results as I go there. I think I will now how to deal with lot of duplicates, but still, better that then nothing

.paul., thanks for idea