How to use DataSet Relations with several different relational types to the same child table

Question

I´m trying to dynamic load a DataSet with hierarchic multilevel related tables. There is no problem as long as I have relationships with unique tables, but when the same table exists in several different Relations, I get trouble with that ds.Tables already contains the table.

I put the DataTables object in ds.Tables, and these must have field TableName set to the correct table name otherwise it´s not working to lookup of data and next level of child and so on. (only one column relationship so far).

Exception occur on ds.Tables.Add(childDataTable); In my example code the BaseTbl have two relations to the table Connections and each Connection have one relation to ConnectionTypes.

MS SqlServer tables script:

CREATE TABLE [dbo].[BaseTbl] (
    [Id]                    UNIQUEIDENTIFIER NOT NULL,
    [Name]                  NVARCHAR (50)    NOT NULL,
    [SourceConnection]      UNIQUEIDENTIFIER NULL,
    [TargetConnection]      UNIQUEIDENTIFIER NULL,
    CONSTRAINT [PK_BaseTbl] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [U_BaseTbl_Name] UNIQUE NONCLUSTERED ([Name] ASC),
    CONSTRAINT [FK_BaseTbl_Connections_SourceConnection] FOREIGN KEY ([SourceConnection]) REFERENCES [dbo].[Connections] ([Id]),
    CONSTRAINT [FK_BaseTbl_Connections_TargetConnection] FOREIGN KEY ([TargetConnection]) REFERENCES [dbo].[Connections] ([Id]) 
};

CREATE TABLE [dbo].[Connections] ( 
    [Id]                    UNIQUEIDENTIFIER NOT NULL,
    [ConnectionString]      NVARCHAR (50)    NOT NULL,
    [ConType]       UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Connections] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [U_Connections_ConnectionString] UNIQUE NONCLUSTERED ([ConnectionString] ASC),
    CONSTRAINT [FK_Connections_ConnectionTypes_ConType] FOREIGN KEY ([ConType]) REFERENCES [dbo].[ConnectionTypes] ([Id])
};

CREATE TABLE [dbo].[ConnectionTypes] ( 
    [Id]                    UNIQUEIDENTIFIER NOT NULL,
    [TypeName]              NVARCHAR (50)    NOT NULL,
    CONSTRAINT [PK_ConnectionTypes] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [U_ConnectionTypes_TypeName] UNIQUE NONCLUSTERED ([TypeName] ASC)
};

C# code:

public class ForeignKeyDef {
    public string  FkName { get; set; }
    public string ParentTable { get; set; }
    public string ParentColumnName { get; set; }
    public string ReferencedTable { get; set; }
    public string ReferencedColumnName { get; set; }
}

private void GetRelatedChildForTable(DataTable parentDataTable, DataSet ds) {
    List<ForeignKeyDef> fks = GetForeignKeys(parentDataTable.TableName);
    foreach (ForeignKeyDef fk in fks) {
        DataTable childDataTable = GetDataForTable(fk.ReferencedTable);
        childDataTable.TableName = fk.ParentColumnName;//FkName;
        ds.Tables.Add(childDataTable);
        ds.Relations.Add(fk.FkName, parentDataTable.Columns[fk.ParentColumnName], childDataTable.Columns[fk.ReferencedColumnName], false);
        GetRelatedChildForTable(fk.ReferencedTable, childDataTable, ds);
    }
}

Running code example:

....
_ds = new DataSet();
DataTable parentDataTable = GetDataForTable(selectedTopTable);
parentDataTable.TableName = selectedTopTable;
_ds.Tables.Add(parentDataTable);
GetRelatedChildForTable(parentDataTable, _ds);
...
LoadHierarchicGridControl(_ds);
...