Manage Table Relations

• Jul 06, 2023

Use the SelectQueryFluentBuilder object to create two queries and add a relationship between resulting tables. This data is used to configure a master-detail report.

To add a relationship between two tables, add a MasterDetailInfo instance to the SqlDataSource.Relations collection:

C#

using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
// ...
void AddQueryRelations()
{
    SelectQuery categories = SelectQueryFluentBuilder
        .AddTable("Categories")
        .SelectAllColumns()
        .Build("Categories");
    SelectQuery products = SelectQueryFluentBuilder
        .AddTable("Products")
        .SelectAllColumns()
        .Build("Products");

    DataSource.Queries.AddRange(new SqlQuery[] { categories, products });
    DataSource.Relations.Add(
        new MasterDetailInfo("Categories", "Products", "CategoryID", "CategoryID"));
}

VB.NET

Imports DevExpress.DataAccess.Sql
Imports DevExpress.DataAccess.ConnectionParameters
' ...
Private Sub AddQueryRelations()
    Dim categories As SelectQuery = SelectQueryFluentBuilder.
        AddTable("Categories").
        SelectAllColumns().Build("Categories")
    Dim products As SelectQuery = SelectQueryFluentBuilder.
        AddTable("Products").
        SelectAllColumns().Build("Products")

    DataSource.Queries.AddRange(New SqlQuery() { categories, products })
    DataSource.Relations.Add(New MasterDetailInfo("Categories", "Products", "CategoryID", "CategoryID"))
End Sub

Call the SqlDataSource.RebuildResultSchema method if you modify the Queries collection.

You can visualize the relationship in the Master-Detail Relation Editor. For this, reference the DevExpress.DataAccess.v23.2.UI.dll assembly and call the SqlDataSourceUIHelper.ManageRelations method or the SqlDataSource.ManageRelations extension method:

C#

using DevExpress.DataAccess.UI.Sql;
// ...
SqlDataSourceUIHelper.ManageRelations(sqlDataSource1);
// You can use an extension method instead:
//sqlDataSource1.ManageRelations();

VB.NET

Imports DevExpress.DataAccess.UI.Sql
'...
SqlDataSourceUIHelper.ManageRelations(sqlDataSource1)
' You can use an extension method instead:
'sqlDataSource1.ManageRelations()

The invoked dialog is shown in the image below: