How to: Bind a Dashboard to a Microsoft SQL Server Database File in a WPF project

• Jul 23, 2019
• 3 minutes to read

This example demonstrates how to bind a dashboard to a Microsoft SQL Server database file (*.mdf) in WPF project.

The dashboard and its data source are created at runtime. A dashboard data source is a new instance of the DashboardSqlDataSource object containing the SelectQuery to retrieve data. The data source is added to the Dashboard.DataSources collection. However, the data source connection parameters are not specified at this time.

The dashboard is assigned to the DashboardControl.DashboardSource property within the Window_Loaded event handler.

Prior to data loading, the dashboard control fires the DashboardControl.ConfigureDataConnection event. A connection string for the NorthWind database file is assigned to the CustomStringConnectionParameters.ConnectionString property, accessible using the e.ConnectionParameters property. Note that the property returns an object of the base class, and you should cast it to the CustomStringConnectionParameters type.

Subsequently the data source is filled with data automatically, and the dashboard displays the data.

Note

The complete sample project How to Bind a Dashboard to Microsoft SQL Server Database File is available in the DevExpress Examples repository.

C#

using DevExpress.DashboardCommon;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using System.Windows;

namespace WpfDashboard_SqlDataSource
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            Dashboard dashboard = CreateDashboard();
            dashboardControl1.Dashboard = dashboard;
        }

        private void dashboardControl1_ConfigureDataConnection(object sender, DashboardConfigureDataConnectionEventArgs e)
        {
            CustomStringConnectionParameters parameters = e.ConnectionParameters as CustomStringConnectionParameters;
            if (e.DataSourceName == "MySqlDataSource")
                parameters.ConnectionString =
                    @"XpoProvider=MSSqlServer;Data Source=(LocalDB)\MSSQLLocalDB;" +
                    "AttachDbFilename=|DataDirectory|\NWind.mdf;Integrated Security=True";
        }
        private Dashboard CreateDashboard()
        {
            Dashboard dashBoard = new Dashboard();

            IDashboardDataSource sqlDataSource = CreateSqlDataSource();
            dashBoard.DataSources.Add(sqlDataSource);

            ChartDashboardItem chart = new ChartDashboardItem();
            chart.DataSource = sqlDataSource; chart.DataMember = "MyQuery";
            chart.Arguments.Add(new Dimension("OrderDate", DateTimeGroupInterval.MonthYear));
            chart.Panes.Add(new ChartPane());
            SimpleSeries salesAmountSeries = new SimpleSeries(SimpleSeriesType.SplineArea);
            salesAmountSeries.Value = new Measure("ExtendedPrice");
            chart.Panes[0].Series.Add(salesAmountSeries);

            GridDashboardItem grid = new GridDashboardItem();
            grid.DataSource = sqlDataSource;
            grid.DataMember = "MyQuery";
            grid.Columns.Add(new GridDimensionColumn(new Dimension("SalesPerson")));
            grid.Columns.Add(new GridMeasureColumn(new Measure("ExtendedPrice")));

            dashBoard.Items.AddRange(chart, grid);

            return dashBoard;
        }
        private IDashboardDataSource CreateSqlDataSource()
        {
            DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("MySqlDataSource");
            SelectQuery selectQuery = SelectQueryFluentBuilder
                .AddTable("SalesPerson")
                .SelectColumns("CategoryName", "SalesPerson", "OrderDate", "ExtendedPrice")
                .Build("MyQuery");
            sqlDataSource.Queries.Add(selectQuery);
            return sqlDataSource;
        }
    }
}

VB.NET

Imports DevExpress.DashboardCommon
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
Imports System.Windows

Namespace WpfDashboard_SqlDataSource
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            Dim dashboard As Dashboard = CreateDashboard()
            dashboardControl1.Dashboard = dashboard
        End Sub
        Private Function CreateSqlDataSource() As IDashboardDataSource
            Dim sqlDataSource As New DashboardSqlDataSource("MySqlDataSource")
            Dim selectQuery As SelectQuery = SelectQueryFluentBuilder.AddTable("SalesPerson").SelectColumns("CategoryName", "SalesPerson", "OrderDate", "ExtendedPrice").Build("MyQuery")
            sqlDataSource.Queries.Add(selectQuery)
            Return sqlDataSource
        End Function
        Private Function CreateDashboard() As Dashboard
            Dim dashBoard As New Dashboard()

            Dim sqlDataSource As IDashboardDataSource = CreateSqlDataSource()
            dashBoard.DataSources.Add(sqlDataSource)

            Dim chart As New ChartDashboardItem()
            chart.DataSource = sqlDataSource
            chart.DataMember = "MyQuery"
            chart.Arguments.Add(New Dimension("OrderDate", DateTimeGroupInterval.MonthYear))
            chart.Panes.Add(New ChartPane())
            Dim salesAmountSeries As New SimpleSeries(SimpleSeriesType.SplineArea)
            salesAmountSeries.Value = New Measure("ExtendedPrice")
            chart.Panes(0).Series.Add(salesAmountSeries)

            Dim grid As New GridDashboardItem()
            grid.DataSource = sqlDataSource
            grid.DataMember = "MyQuery"
            grid.Columns.Add(New GridDimensionColumn(New Dimension("SalesPerson")))
            grid.Columns.Add(New GridMeasureColumn(New Measure("ExtendedPrice")))

            dashBoard.Items.AddRange(chart, grid)

            Return dashBoard
        End Function

        Private Sub dashboardControl1_ConfigureDataConnection(ByVal sender As Object, ByVal e As DashboardConfigureDataConnectionEventArgs)
            Dim parameters As CustomStringConnectionParameters = TryCast(e.ConnectionParameters, CustomStringConnectionParameters)
            If e.DataSourceName = "MySqlDataSource" Then
                parameters.ConnectionString = "XpoProvider=MSSqlServer;"_
                + "Data Source=(LocalDB)\MSSQLLocalDB;" _
                + "AttachDbFilename=|DataDirectory|\NWind.mdf;" _
                + "Integrated Security=True"
            End If

        End Sub
    End Class
End Namespace