DataSet & Table Relations

[fatcodeguy]

Hi,

I've queried 2 tables (inner joined) from the database via a DataAdapter and filled a DataSet. The data that's currently in the DataSet is then a single DataTable with all the information.

Instead, I was wondering if there's a way of storing the information in the DataSet as 2 DataTables and a DataRelation object relating the two.

Is this possible to do without running 2 queries?

Thanks!!

 

[SqueakinSweep]

Firstly yes...you can add both tables to a dataset and then create a relationship using dataset.relations.add. Whether this is the best way depends on how you are displaying your data. If using a tree view or parent/child datagrid I would go with the 2 queries, 2 datatables, and a relationship.


Sweep
...if it works, you know the rest..

 

[chrissie1]

So no not without running 2 queries.

Christiaan Baes
Belgium

"My new site" - Me

 

[fatcodeguy]

Thanks.

So I've created the two tables (TAB_DEFINITION, MODULE) from 2 queries and I'd like to join them via a common TAB_ID column and then write the DataSet to an XML.

However, the data relation doesn't seem to be working properly.

        '...
        'ds is a DataSet
        Dim dc1 As DataColumn = ds.Tables("TAB_DEFINITION").Columns("TAB_ID")
        If dc1 Is Nothing Then
            Throw New ApplicationException("WTF - dc1 null")
        End If
        Dim dc2 As DataColumn = ds.Tables("MODULE").Columns("TAB_ID")
        If dc2 Is Nothing Then
            Throw New ApplicationException("WTF - dc2 null")
        End If

        Dim dr As DataRelation = New DataRelation("TAB_DEFINITION_MODULE", dc1, dc2)
        ds.Relations.Add(dr)

When I write the file, I want the xml to look something like this

<TAB_DEFINITION ID="1"..... >
    <MODULE ID="1" ... />
    <MODULE ID="2" ... />
</TAB_DEFINITION>

<TAB_DEFINITION ID="2"..... >
    <MODULE ID="3" ... />
    <MODULE ID="4" ... />
</TAB_DEFINITION>

but instead I get this

<TAB_DEFINITION ID="1"..... />
<TAB_DEFINITION ID="2"..... />
<MODULE ID="1" ... />
<MODULE ID="2" ... />
<MODULE ID="3" ... />
<MODULE ID="4" ... />

Any Ideas?

Thanks!!

 

[fatcodeguy]

OK, I figured it out.

I have to set the DataRelation object Nested property to True