How to: Bind a dashboard to a Microsoft SQL Server Database File (.MDF)

• Jul 24, 2019
• 3 minutes to read

This example demonstrates how to bind a dashboard to a Microsoft SQL Server database file. The DevExpress.DashboardWin.DashboardDesigner displays the resulting dashboard.

To bind the dashboard to the Microsoft SQL Server database file, create a DashboardSqlDataSource instance and provide connection parameters with the CustomStringConnectionParameters instance.

The following connection string is used for .MDF file in this example:

XpoProvider=MSSqlServer;Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\NW19.mdf;Integrated Security=True

The SelectQuery object is used to build a SQL query in code.

Note

The SelectQueryFluentBuilder instance in not appropriate for this query because the fluent builder does not support multiple JOINs.

The dashboard uses calculated fields. Create them in code and add to the DashboardSqlDataSource.CalculatedFields collection.

To bind a dashboard item to the SQL Data Source, you should specify the DataDashboardItem.DataMember setting. To bind a calculated field, specify the CalculatedField.DataMember setting. The DataMember setting is the name of the created SelectQuery.

Note

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

C#

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

namespace BindToMsSqlDatabaseFileExample
{
    public partial class Form1 : DevExpress.XtraEditors.XtraForm
    {
        public Form1()
        {
            InitializeComponent();
            dashboardDesigner1.CreateRibbon();
            Dashboard currentDashboard = CreateDashboard();
            BindDataSource(currentDashboard, CreateDataSource());
            dashboardDesigner1.Dashboard = currentDashboard;
        }

        private void BindDataSource(Dashboard dashboard, DashboardSqlDataSource dashboardSqlDataSource)
        {
            dashboard.DataSources.Add(dashboardSqlDataSource);
            foreach (var item in dashboard.Items)
            {
                DataDashboardItem dataItem = item as DataDashboardItem;
                if (dataItem != null)
                {
                    dataItem.DataSource = dashboardSqlDataSource;
                    dataItem.DataMember = dashboardSqlDataSource.Queries[0].Name;
                }
            }
        }

        private DashboardSqlDataSource CreateDataSource()
        {
            CustomStringConnectionParameters connectionParameters = new CustomStringConnectionParameters();
            connectionParameters.ConnectionString =
                @"XpoProvider=MSSqlServer;Data Source=(LocalDB)\MSSQLLocalDB;" +
                @"AttachDbFilename=|DataDirectory|\NW19.mdf;" +
                @"Integrated Security=True";
            DashboardSqlDataSource sqlDataSource =
                new DashboardSqlDataSource("NW19 SQL Server Database File", connectionParameters);
            SelectQuery selectQuery = CreateSqlQuery();
            sqlDataSource.Queries.Add(selectQuery);
            sqlDataSource.CalculatedFields.AddRange(CreateCalculatedFields(selectQuery));
            sqlDataSource.Fill();
            return sqlDataSource;
        }

        private static CalculatedFieldCollection CreateCalculatedFields(SelectQuery selectQuery)
        {
            CalculatedField fieldSalesPerson = new CalculatedField()
            {
                Name = "Sales Person",
                DataMember = selectQuery.Name,
                Expression = "Concat([FirstName], ' ', [LastName])"
            };
            CalculatedField fieldExtPrice = new CalculatedField()
            {
                Name = "Extended Price",
                DataMember = selectQuery.Name,
                Expression = "[Quantity] * [UnitPrice]",
            };
            return new CalculatedFieldCollection() { fieldSalesPerson, fieldExtPrice };
        }

        private static SelectQuery CreateSqlQuery()
        {
            SelectQuery selectQuery = new SelectQuery("SalesPersons");
            var orders = selectQuery.AddTable("Orders");
            var order_details = selectQuery.AddTable("Order Details");
            var employees = selectQuery.AddTable("Employees");
            var products = selectQuery.AddTable("Products");
            var categories = selectQuery.AddTable("Categories");
            selectQuery.AddRelation(order_details, orders, "OrderID");
            selectQuery.AddRelation(orders, employees, "EmployeeID");
            selectQuery.AddRelation(order_details, products, "ProductID");
            selectQuery.AddRelation(products, categories, "CategoryID");
            selectQuery.SelectColumns(orders, new string[] { "OrderDate", "ShipCity", "ShipCountry" });
            selectQuery.SelectColumns(order_details, new string[] { "UnitPrice", "Quantity" });
            selectQuery.SelectColumns(employees, new string[] { "FirstName", "LastName" });
            selectQuery.SelectColumn(products, "ProductName");
            selectQuery.SelectColumn(categories, "CategoryName");
            return selectQuery;
        }

        private Dashboard CreateDashboard()
        {
            Dashboard dBoard = new Dashboard();
            ChartDashboardItem chart = new ChartDashboardItem();;
            chart.Arguments.Add(new Dimension("OrderDate", DateTimeGroupInterval.MonthYear));
            chart.Panes.Add(new ChartPane());
            SimpleSeries salesAmountSeries = new SimpleSeries(SimpleSeriesType.SplineArea);
            salesAmountSeries.Value = new Measure("Extended Price");
            chart.Panes[0].Series.Add(salesAmountSeries);
            GridDashboardItem grid = new GridDashboardItem();
            grid.Columns.Add(new GridDimensionColumn(new Dimension("Sales Person")));
            grid.Columns.Add(new GridMeasureColumn(new Measure("Extended Price")));
            dBoard.Items.AddRange(chart, grid);
            return dBoard;
        }
    }
}

VB.NET