DashboardSqlDataSource Class

An SQL data source that provides data for the dashboard.

Namespace: DevExpress.DashboardCommon

Assembly: DevExpress.Dashboard.v24.2.Core.dll

NuGet Package: DevExpress.Dashboard.Core

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:

   • Create a SelectQuery object to specify a set of tables/columns that form a SELECT statement when you execute 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.

Supported Data Providers

Relational Database System	Supported Version	.NET Framework Data Provider	.NET Data Provider
Amazon Redshift	n/a	Npgsql.dll	Npgsql
Firebird	1.5, 2.5.7, 3.0.2, 5.0.1	FirebirdSql.Data.FirebirdClient.dll FirebirdSql.Data.Firebird.dll	FirebirdSql.Data.FirebirdClient
Google BigQuery	n/a	ODBC driver for BigQuery	ODBC driver for BigQuery
IBM DB2	9.5 or higher	IBM.Data.DB2.dll	IBM.Data.DB2.Core
Microsoft Access	Access 2000 or higher Access 2007 or higher	System.Data.dll	System.Data.OleDb
Microsoft SQL Server	SQL Server 2005 (with Express) SQL Server 2008 (with R2 & Express) SQL Server 2012, 2014, 2016, 2017, 2019, 2022 (with Express & LocalDB) SQL Azure™ Database Azure SQL Database	System.Data.SqlClient.dll System.Data.dll (Included in .NET Framework)	Microsoft.Data.SqlClient or System.Data.SqlClient If the Microsoft.Data.SqlClient is not found, the System.Data.SqlClient provider is used.
Microsoft SQL Server CE	3.5, 4.0	System.Data.SqlServerCe.dll (Included in .NET Framework)
MySQL	4.1 or higher 5.5, 5.6, 5.7, 8.0, 8.3, 9.1 (for the MySqlConnector provider)	MySqlConnector MySql.Data	MySqlConnector MySql.Data
Oracle Database	9i, 10g, 11g, 12c, 18c, 21c, 23ai	Oracle.ManagedDataAccess.dll System.Data.OracleClient.dll Oracle.DataAccess.dll	Oracle.ManagedDataAccess
Pervasive PSQL	9.x or higher	Pervasive.Data.SqlClient.dll
PostgreSQL	7, 8, 9, 10, 11, 12, 15, 16, 17	Npgsql.dll	Npgsql
SAP HANA	2.0 or higher	SAP HANA Client 2.0	SAP HANA Client 2.0 Sap.Data.Hana.Core.v2.1.dll
SAP Sybase Advantage	Advantage Database Server 9.1 or higher	Advantage.Data.Provider.dll
SAP Sybase ASE	Sybase Adaptive Server 12.0 or higher	Sybase.AdoNet4.AseClient.dll
SAP SQL Anywhere	11 or higher	Sap.Data.SQLAnywhere.dll
SQLite	3.x	System.Data.SQLite.dll 1.*	Microsoft.Data.SQLite 8.*
Teradata	13.0 or higher	Teradata.Client.Provider.dll	Teradata.Client.Provider
VistaDB	4, 5, 6	VistaDB.4.dll 4.* VistaDB.5.NET40.dll 5.* VistaDB.6.dll
XML file	n/a	Built-in support	Built-in support

Example

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

View Example: How to Bind a Dashboard to an SQL Data Source

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

DataComponentBase

SqlDataSource

DashboardSqlDataSource