FederationDataSource Class

A federated data source that integrates different data sources and provides uniform data access with a federated query.

Namespace: DevExpress.DataAccess.DataFederation

Assembly: DevExpress.DataAccess.v19.2.dll

Declaration

C#

[ToolboxSvgImage("DevExpress.DataAccess.Images.FederationDataSource.svg,DevExpress.DataAccess.v19.2, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")]
[XRDesigner("DevExpress.DataAccess.UI.Design.XRFederationDataSourceDesigner,DevExpress.DataAccess.v19.2.UI, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", typeof(IDesigner))]
public class FederationDataSource :
    FederationDataSourceBase,
    IListSource,
    IFederationListAdapter,
    IListAdapterAsync,
    IListAdapter,
    ISupportInitialize,
    IXtraSerializable,
    IXtraSupportDeserializeCollectionItem

VB.NET

<ToolboxSvgImage("DevExpress.DataAccess.Images.FederationDataSource.svg,DevExpress.DataAccess.v19.2, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")>
<XRDesigner("DevExpress.DataAccess.UI.Design.XRFederationDataSourceDesigner,DevExpress.DataAccess.v19.2.UI, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", GetType(IDesigner))>
Public Class FederationDataSource
    Inherits FederationDataSourceBase
    Implements IListSource,
               IFederationListAdapter,
               IListAdapterAsync,
               IListAdapter,
               ISupportInitialize,
               IXtraSerializable,
               IXtraSupportDeserializeCollectionItem

Remarks

The FederationDataSource class is a federated data source that integrates different data sources and provides uniform data access with a federated query.

Federated Data Source supports the following data source types:

• SQL Data Source
• Excel Data Source
• Object Data Source
• Entity Framework Data Source
• JSON Data Source
• XPO Persistent Class Data Source

You can create the federated data source at design time or runtime.

Design Time

Use the Data Source Wizard in Reporting and Dashboards products to create a federated data source and connect to it.

Reporting Data Source Wizard

Dashboards Data Source Wizard

The wizard allows you to create a federated query based on data from other data sources.

The wizard automatically assigns the name to a federated data source and federated query. The federated data source name is “Federation Data Source N” where N is a serial number. The federated query name is the same as the name of the first data source included in the query.

Run Time

Use a default constructor to create a FederationDataSource class instance.

The main part of the Federated Data Source is the FederationDataSourceBase.Queries collection that contains federated queries.

There are two methods to create a federated query:

Fluent Interface

This method uses the SelectNodeBuilder object that provides a fluent interface to build a query tree. Fluent interface allows you to create queries in a few lines of code.

The entry point is the Source.From method. It returns the SelectNodeBuilder instance.

The final point is the SelectNodeBuilder.Build method that returns the query object.

Follow the steps below to build a federated query:

• Create a Source object for each data source integrated in the federated data source.
• Choose the source that becomes the query root and call its Source.From extension method.
• Chain SelectNodeBuilder method calls, such as Select and Join, to build a query.
• Call the SelectNodeBuilder.Build method to finalize the process.
• Assign the resulting query to the SelectNode instance.
• Add the SelectNode object to the FederationDataSourceBase.Queries collection.

Note

The complete sample project How to Create a Federated Data Source at Runtime is available in the DevExpress Examples repository.

The following code sample creates a federated data source with two federated queries from SQL and Excel data sources.

C#

using DevExpress.DataAccess.DataFederation;
// ...
  FederationDataSource federation = new FederationDataSource();

  Source sourceProducts = new Source("Products", CreateSqlDataSource(), "Products");
  Source sourceOrderDetail = new Source("OrderDetail", CreateExcelDataSource("SalesPerson.xlsx", "Data"));
  Source sourceHeader = new Source("OrderHeader", CreateExcelDataSource("OrderHeaders.xlsx", "OrderHeader"));


  SelectNode query1 = sourceProducts.From().Select("ProductName", "QuantityPerUnit", "UnitsInStock")
      .Join(sourceOrderDetail, "[Products.ProductName] = [OrderDetail.ProductName]")
      .Select("OrderDate")
      .Build("ProductsOrderDetail");

  SelectNode query2 = sourceHeader.From().Select("OrderID", "Status", "Description")
      .Join(sourceOrderDetail, "[OrderHeader.OrderID] = [OrderDetail.OrderID]")
      .Select("Quantity", "Extended Price")
      .Build("OrderHeaderOrderDetail");

  federation.Queries.AddRange(new[] { query1, query2 });
  federation.Fill();

VB.NET

Imports DevExpress.DataAccess.DataFederation
' ...
  Dim federation As New FederationDataSource()

  Dim sourceProducts As New Source("Products", CreateSqlDataSource(), "Products")
  Dim sourceOrderDetail As New Source("OrderDetail", CreateExcelDataSource("SalesPerson.xlsx", "Data"))
  Dim sourceHeader As New Source("OrderHeader", CreateExcelDataSource("OrderHeaders.xlsx", "OrderHeader"))


        Dim query1 As SelectNode = sourceProducts.From().
            Select("ProductName", "QuantityPerUnit", "UnitsInStock").
            Join(sourceOrderDetail, "[Products.ProductName] = [OrderDetail.ProductName]").
            Select("OrderDate").Build("ProductsOrderDetail")

        Dim query2 As SelectNode = sourceHeader.From().
            Select("OrderID", "Status", "Description").
            Join(sourceOrderDetail, "[OrderHeader.OrderID] = [OrderDetail.OrderID]").
            Select("Quantity", "Extended Price").Build("OrderHeaderOrderDetail")

        federation.Queries.AddRange( { query1, query2 })
  federation.Fill()

Data Federation API

This method builds a query tree by instantiating objects and adding them to the collections.

Follow the steps below to build a federated query:

• Create a Source object for each data source integrated in the federated data source.
• Create a a SourceNode object for each data source.
• Choose the source node that becomes the query root and create a SelectNode object for that data source.
• Create a new SelectColumnExpression object for each data field and add it to the SelectNode.Expressions collection.
• Create a JoinElement object for each JOIN and add it to the SelectNode.SubNodes collection.
• Add the SelectNode object to the FederationDataSourceBase.Queries collection.

Note

The complete sample project How to Create a Federated Data Source at Runtime is available in the DevExpress Examples repository.

The following code sample creates a federated data source with a federated query from SQL and Excel data sources.

C#

using DevExpress.DataAccess.DataFederation;
// ...
  FederationDataSource federation = new FederationDataSource();

  Source sourceProducts = new Source("Products", CreateSqlDataSource(), "Products");
  Source sourceOrderDetail = new Source("OrderDetail", CreateExcelDataSource("SalesPerson.xlsx", "Data"));

  var sourceNodeProducts = new SourceNode(sourceProducts, "Products");
  var sourceNodeOrderDetail = new SourceNode(sourceOrderDetail, "OrderDetail");

  var query = new SelectNode(sourceNodeProducts)
  {
      Alias = "ProductsOrderDetail",
      SubNodes = {
              new JoinElement {
                  Node = sourceNodeOrderDetail,
                  Condition = $"[{sourceNodeProducts.Alias}.ProductName] == [{sourceNodeOrderDetail.Alias}.ProductName]"
              }
          },
      Expressions = {
          new SelectColumnExpression(sourceNodeProducts, "ProductName"),
          new SelectColumnExpression(sourceNodeProducts, "QuantityPerUnit"),
          new SelectColumnExpression(sourceNodeOrderDetail, "OrderID"),
          new SelectColumnExpression(sourceNodeOrderDetail, "OrderDate"),
          new SelectColumnExpression(sourceNodeOrderDetail, "Quantity"),
          new SelectColumnExpression(sourceNodeOrderDetail, "UnitPrice")
          }
  };
  federation.Queries.Add(query);
  federation.Fill();

VB.NET

Imports DevExpress.DataAccess.DataFederation
' ...
  Dim federation As New FederationDataSource()

  Dim sourceProducts As New Source("Products", CreateSqlDataSource(), "Products")
  Dim sourceOrderDetail As New Source("OrderDetail", CreateExcelDataSource("SalesPerson.xlsx", "Data"))

  Dim sourceNodeProducts = New SourceNode(sourceProducts, "Products")
  Dim sourceNodeOrderDetail = New SourceNode(sourceOrderDetail, "OrderDetail")

        Dim query = New SelectNode(sourceNodeProducts)
        query.Alias = "ProductsOrderDetail"
        query.SubNodes.Add(New JoinElement With {.Node = sourceNodeOrderDetail,
        .Condition = $"[{sourceNodeProducts.Alias}.ProductName] == [{sourceNodeOrderDetail.Alias}.ProductName]"})

        query.Expressions.Add(New SelectColumnExpression(sourceNodeProducts, "QuantityPerUnit"))
        query.Expressions.Add(New SelectColumnExpression(sourceNodeOrderDetail, "OrderID"))
        query.Expressions.Add(New SelectColumnExpression(sourceNodeOrderDetail, "OrderDate"))
        query.Expressions.Add(New SelectColumnExpression(sourceNodeOrderDetail, "Quantity"))
        query.Expressions.Add(New SelectColumnExpression(sourceNodeOrderDetail, "UnitPrice"))
        federation.Queries.Add(query)
        federation.Fill()

Note

At the moment, federated queries do not support the UNION operator.

Implements

IDataComponent

Inheritance

Object

MarshalByRefObject

Component

DevExpress.DataAccess.DataComponentBase

FederationDataSourceBase

FederationDataSource