DashboardSqlDataSource Class

A SQL data source that provides data for the dashboard.

Namespace: DevExpress.DashboardCommon

Assembly: DevExpress.Dashboard.v19.1.Core.dll

Declaration

C#

public class DashboardSqlDataSource :
    SqlDataSource,
    IDashboardDataSource,
    IDashboardComponent,
    IComponent,
    IDisposable,
    ISupportInitialize,
    ISupportPrefix,
    IDashboardDataSourceInternal,
    IFederationDataProvider,
    ICloneable<DashboardSqlDataSource>,
    IAssignable<DashboardSqlDataSource>,
    IQueryDataSource<SqlQuery>

VB.NET

Public Class DashboardSqlDataSource
    Inherits SqlDataSource
    Implements IDashboardDataSource,
               IDashboardComponent,
               IComponent,
               IDisposable,
               ISupportInitialize,
               ISupportPrefix,
               IDashboardDataSourceInternal,
               IFederationDataProvider,
               ICloneable(Of DashboardSqlDataSource),
               IAssignable(Of DashboardSqlDataSource),
               IQueryDataSource(Of SqlQuery)

Remarks

Use the DashboardSqlDataSource class to create a data source that uses a connection to the SQL database. You can specify connection parameters and select the data as follows:

1. Specify connection parameters to the SQL database. For this, create the DataConnectionParametersBase class descendant and set the properties specific for a data source type.

2. Assign the resulting object to the DashboardSqlDataSource.ConnectionParameters property.

   Tip

   As an alternative, you can use a connection string from the application configuration file. Assign the connection string name to the DashboardSqlDataSource.ConnectionName property.

3. Create the Select query. Use one of the following objects:

   • The SelectQuery object allows you to specify a set of tables/columns, which forms a SELECT statement when executing a query.
   • The CustomSqlQuery object allows you to specify an SQL query manually as a query string.
   • The StoredProcQuery object allows you to perform a stored procedure call to supply the dashboard with data.

4. Add the query object to the DashboardSqlDataSource.Queries collection.

5. Call the DashboardSqlDataSource.Fill method.

6. Add the created DashboardSqlDataSource object to the Dashboard.DataSources collection.

Example

This example demonstrates how to bind a dashboard created at runtime to a SQL data source (Microsoft Access database, Microsoft SQL Server, XML file) with the DashboardSqlDataSource.

Note

The complete sample project How to Bind a Dashboard to a SQL Data Source is available in the DevExpress Examples repository.

C#

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

namespace Dashboard_SqlDataProvider
{
    public partial class Form1 : DevExpress.XtraEditors.XtraForm
    {
        public Form1() {
            InitializeComponent();
            dashboardDesigner1.CreateRibbon();

            DataConnectionParametersBase connParameters = CreateConnectionParameters("MSAccess");
            DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQL Data Source 1", connParameters);
            sqlDataSource.Queries.Add(CreateQuery("fluent"));
            sqlDataSource.Fill();
            dashboardDesigner1.Dashboard = CreateDashboard(sqlDataSource);
        }

        private DataConnectionParametersBase CreateConnectionParameters(string providerName)
        {
            switch (providerName)
            {
                case "MSAccess":
                    return new Access97ConnectionParameters()
                    {
                        FileName = @"Data\nwind.mdb"
                    };
                case "MSSqlServer":
                    return new MsSqlConnectionParameters()
                    {
                        ServerName = "localhost",
                        DatabaseName = "Northwind",
                        AuthorizationType = MsSqlAuthorizationType.Windows
                    };
                default:
                    return new XmlFileConnectionParameters()
                    {
                        FileName = @"Data\sales-person.xml"
                    };
            }
        }
        private SqlQuery CreateQuery(string builderName)
        {
            switch (builderName)
            {
                case "fluent":
                    return SelectQueryFluentBuilder
                        .AddTable("SalesPersons")
                        .SelectColumns("CategoryName", "SalesPerson", "OrderDate", "ExtendedPrice")
                        .Build("Query 1");
                default:
                    return new CustomSqlQuery()
                    {
                        Name = "Query 1",
                        Sql = @"SELECT CategoryName, SalesPerson, OrderDate, ExtendedPrice FROM SalesPersons"
                    };
            }
        }

        private Dashboard CreateDashboard(IDashboardDataSource dataSource)
        {
            Dashboard newDashboard = new Dashboard();
            newDashboard.DataSources.Add(dataSource);

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

            GridDashboardItem grid = new GridDashboardItem
            {
                DataSource = dataSource,
                DataMember = "Query 1"
            };
            grid.Columns.Add(new GridDimensionColumn(new Dimension("SalesPerson")));
            grid.Columns.Add(new GridMeasureColumn(new Measure("ExtendedPrice")));

            newDashboard.Items.AddRange(chart, grid);
            return newDashboard;
        }
    }
}

VB.NET

Imports DevExpress.DashboardCommon
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql

Namespace Dashboard_SqlDataProvider
    Partial Public Class Form1
        Inherits DevExpress.XtraEditors.XtraForm

        Public Sub New()
            InitializeComponent()
            dashboardDesigner1.CreateRibbon()

            Dim connParameters As DataConnectionParametersBase = CreateConnectionParameters("MSAccess")
            Dim sqlDataSource As New DashboardSqlDataSource("SQL Data Source 1", connParameters)
            sqlDataSource.Queries.Add(CreateQuery("fluent"))
            sqlDataSource.Fill()
            dashboardDesigner1.Dashboard = CreateDashboard(sqlDataSource)
        End Sub

        Private Function CreateConnectionParameters(ByVal providerName As String) As DataConnectionParametersBase
            Select Case providerName
                Case "MSAccess"
                    Return New Access97ConnectionParameters() With {.FileName = "Data\nwind.mdb"}
                Case "MSSqlServer"
                    Return New MsSqlConnectionParameters() With {.ServerName = "localhost", .DatabaseName = "Northwind", .AuthorizationType = MsSqlAuthorizationType.Windows}
                Case Else
                    Return New XmlFileConnectionParameters() With {.FileName = "Data\sales-person.xml"}
            End Select
        End Function
        Private Function CreateQuery(ByVal builderName As String) As SqlQuery
            Select Case builderName
                Case "fluent"
                    Return SelectQueryFluentBuilder.AddTable("SalesPersons").SelectColumns("CategoryName", "SalesPerson", "OrderDate", "ExtendedPrice").Build("Query 1")
                Case Else
                    Return New CustomSqlQuery() With {.Name = "Query 1", .Sql = "SELECT CategoryName, SalesPerson, OrderDate, ExtendedPrice FROM SalesPersons"}
            End Select
        End Function

        Private Function CreateDashboard(ByVal dataSource As IDashboardDataSource) As Dashboard
            Dim newDashboard As New Dashboard()
            newDashboard.DataSources.Add(dataSource)

            Dim chart As ChartDashboardItem = New ChartDashboardItem With {.DataSource = dataSource, .DataMember = "Query 1"}
            chart.Arguments.Add(New Dimension("OrderDate", DateTimeGroupInterval.MonthYear))
            chart.Panes.Add(New ChartPane())
            Dim salesAmountSeries As New SimpleSeries(SimpleSeriesType.SplineArea) With {.Value = New Measure("ExtendedPrice")}
            chart.Panes(0).Series.Add(salesAmountSeries)

            Dim grid As GridDashboardItem = New GridDashboardItem With {.DataSource = dataSource, .DataMember = "Query 1"}
            grid.Columns.Add(New GridDimensionColumn(New Dimension("SalesPerson")))
            grid.Columns.Add(New GridMeasureColumn(New Measure("ExtendedPrice")))

            newDashboard.Items.AddRange(chart, grid)
            Return newDashboard
        End Function
    End Class
End Namespace

Implements

IDataComponent

IDashboardDataSource

Inheritance

Object

MarshalByRefObject

Component

DevExpress.DataAccess.DataComponentBase

SqlDataSource

DashboardSqlDataSource