How to: Bind ASP.NET MVC Dashboard to a Federated Data Source

• Dec 11, 2019
• 4 minutes to read

This example demonstrates how to create a Federated data source and add it to the Web Dashboard data source list.

In this example, the DashboardFederationDataSource class is used to supply data obtained from the federated query.

This example uses the DevExpress.DataAccess.DataFederation.SelectNodeBuilder object to create a federated query. It involves the following steps:

1. Create the Source object for each data source integrated in the federated data source.

2. Use the Source.From extension method to access the SelectNodeBuilder and create a SelectNode object.

3. Add the SelectNode object to the FederationDataSourceBase.Queries collection as a federated query.

Note

The complete sample project ASP.NET MVC Dashboard - How to Bind a Dashboard to a Federated Data Source Created at Runtime is available in the DevExpress Examples repository.

DashboardConfig.cs

using DevExpress.DashboardCommon;
using DevExpress.DashboardWeb;
using DevExpress.DashboardWeb.Mvc;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.DataFederation;
using DevExpress.DataAccess.Excel;
using DevExpress.DataAccess.Sql;
using System.Web.Hosting;
using System.Web.Routing;

namespace MVC_DataFederationExample
{
    public static class DashboardConfig
    {
        public static void RegisterService(RouteCollection routes)
        {
            routes.MapDashboardRoute("dashboardControl");

            DashboardFileStorage dashboardFileStorage = new DashboardFileStorage("~/App_Data/Dashboards");
            DashboardConfigurator.Default.SetDashboardStorage(dashboardFileStorage);

            DashboardConfigurator.PassCredentials = true;

            // Uncomment this string to allow end users to create new data sources based on predefined connection strings.
            //DashboardConfigurator.Default.SetConnectionStringsProvider(new DevExpress.DataAccess.Web.ConfigFileConnectionStringsProvider());

            DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();

            // Registers an SQL data source.
            SQLiteConnectionParameters sqliteParams = new SQLiteConnectionParameters();
            sqliteParams.FileName = HostingEnvironment.MapPath(@"~/App_Data/nwind.db");

            DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQLite Data Source", sqliteParams);
            SelectQuery selectQuery = SelectQueryFluentBuilder
                .AddTable("Orders")
                .SelectAllColumnsFromTable()
                .Build("SQLite Orders");
            sqlDataSource.Queries.Add(selectQuery);
            sqlDataSource.Fill();
            dataSourceStorage.RegisterDataSource("sqlDataSource", sqlDataSource.SaveToXml());

            // Registers an Object data source.
            DashboardObjectDataSource objDataSource = new DashboardObjectDataSource();
            objDataSource.Name = "ObjectDS";
            objDataSource.DataSource = DataGenerator.Data;
            objDataSource.Fill();

            dataSourceStorage.RegisterDataSource("objDataSource", objDataSource.SaveToXml());

            // Registers an Excel data source.
            DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("ExcelDS");
            excelDataSource.FileName = HostingEnvironment.MapPath(@"~/App_Data/SalesPerson.xlsx");
            excelDataSource.SourceOptions = new ExcelSourceOptions(new ExcelWorksheetSettings("Data"));
            excelDataSource.Fill();
            dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml());

            // Registers the Federated data source.
            DashboardFederationDataSource federationDataSource = new DashboardFederationDataSource("Federated Data Source");
            Source sqlSource = new Source("sqlite", sqlDataSource, "SQLite Orders");
            Source excelSource = new Source("excel", excelDataSource, "");
            Source objectSource = new Source("SalesPersonDS", objDataSource, "");
            SelectNode mainQueryCreatedByNodeBuilder =
                sqlSource.From()
                .Select("OrderDate", "ShipCity", "ShipCountry")
                .Join(excelSource, "[excel.OrderID] = [sqlite.OrderID]")
                    .Select("CategoryName", "ProductName", "Extended Price")
                    .Join(objectSource, "[SalesPersonDS.SalesPerson] = [excel.Sales Person]")
                        .Select("SalesPerson", "Weight")
                        .Build("FDS");
            federationDataSource.Queries.Add(mainQueryCreatedByNodeBuilder);
            federationDataSource.CalculatedFields.Add("FDS", "[Weight] * [Extended Price] / 100", "Score");
            federationDataSource.Fill(new DevExpress.Data.IParameter[0]);
            dataSourceStorage.RegisterDataSource("federatedDataSource", federationDataSource.SaveToXml());


            DashboardConfigurator.Default.SetDataSourceStorage(dataSourceStorage);
            DashboardConfigurator.Default.DataLoading += DataLoading;
        }

        private static void DataLoading(object sender, DataLoadingWebEventArgs e)
        {
            if (e.DataSourceName == "ObjectDS")
            {
                e.Data = DataGenerator.CreateSourceData();
            }
        }
    }
}

DashboardConfig.vb

Imports DevExpress.DashboardCommon
Imports DevExpress.DashboardWeb
Imports DevExpress.DashboardWeb.Mvc
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.DataFederation
Imports DevExpress.DataAccess.Excel
Imports DevExpress.DataAccess.Sql
Imports System.Web.Hosting
Imports System.Web.Routing

Namespace MVC_DataFederationExample
    Public NotInheritable Class DashboardConfig

        Private Sub New()
        End Sub

        Public Shared Sub RegisterService(ByVal routes As RouteCollection)
            routes.MapDashboardRoute("dashboardControl")

            Dim dashboardFileStorage As New DashboardFileStorage("~/App_Data/Dashboards")
            DashboardConfigurator.Default.SetDashboardStorage(dashboardFileStorage)

            DashboardConfigurator.PassCredentials = True

            ' Uncomment this string to allow users to create new data sources based on predefined connection strings.
            'DashboardConfigurator.Default.SetConnectionStringsProvider(new DevExpress.DataAccess.Web.ConfigFileConnectionStringsProvider());

            Dim dataSourceStorage As New DataSourceInMemoryStorage()

            ' Registers an SQL data source.
            Dim sqliteParams As New SQLiteConnectionParameters()
            sqliteParams.FileName = HostingEnvironment.MapPath("~/App_Data/nwind.db")

            Dim sqlDataSource As New DashboardSqlDataSource("SQLite Data Source", sqliteParams)
            Dim selectQuery As SelectQuery = SelectQueryFluentBuilder.AddTable("Orders").SelectAllColumnsFromTable().Build("SQLite Orders")
            sqlDataSource.Queries.Add(selectQuery)
            sqlDataSource.Fill()
            dataSourceStorage.RegisterDataSource("sqlDataSource", sqlDataSource.SaveToXml())

            ' Registers an Object data source.
            Dim objDataSource As New DashboardObjectDataSource()
            objDataSource.Name = "ObjectDS"
            objDataSource.DataSource = DataGenerator.Data
            objDataSource.Fill()

            dataSourceStorage.RegisterDataSource("objDataSource", objDataSource.SaveToXml())

            ' Registers an Excel data source.
            Dim excelDataSource As New DashboardExcelDataSource("ExcelDS")
            excelDataSource.FileName = HostingEnvironment.MapPath("~/App_Data/SalesPerson.xlsx")
            excelDataSource.SourceOptions = New ExcelSourceOptions(New ExcelWorksheetSettings("Data"))
            excelDataSource.Fill()
            dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml())

            ' Registers the Federated data source.
            Dim federationDataSource As New DashboardFederationDataSource("Federated Data Source")
            Dim sqlSource As New Source("sqlite", sqlDataSource, "SQLite Orders")
            Dim excelSource As New Source("excel", excelDataSource, "")
            Dim objectSource As New Source("SalesPersonDS", objDataSource, "")
            Dim mainQueryCreatedByNodeBuilder As SelectNode = sqlSource.From().Select("OrderDate", "ShipCity", "ShipCountry").Join(excelSource, "[excel.OrderID] = [sqlite.OrderID]").Select("CategoryName", "ProductName", "Extended Price").Join(objectSource, "[SalesPersonDS.SalesPerson] = [excel.Sales Person]").Select("SalesPerson", "Weight").Build("FDS")
            federationDataSource.Queries.Add(mainQueryCreatedByNodeBuilder)
            federationDataSource.CalculatedFields.Add("FDS", "[Weight] * [Extended Price] / 100", "Score")
            federationDataSource.Fill(New DevExpress.Data.IParameter(){})
            dataSourceStorage.RegisterDataSource("federatedDataSource", federationDataSource.SaveToXml())


            DashboardConfigurator.Default.SetDataSourceStorage(dataSourceStorage)
            AddHandler DashboardConfigurator.Default.DataLoading, AddressOf DataLoading
        End Sub

        Private Shared Sub DataLoading(ByVal sender As Object, ByVal e As DataLoadingWebEventArgs)
            If e.DataSourceName = "ObjectDS" Then
                e.Data = DataGenerator.CreateSourceData()
            End If
        End Sub
    End Class
End Namespace