Skip to main content

DashboardSqlDataSource Class

An SQL data source that provides data for the dashboard.

Namespace: DevExpress.DashboardCommon

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

NuGet Package: DevExpress.Dashboard.Core

Declaration

public class DashboardSqlDataSource :
    SqlDataSource,
    IDashboardDataSource,
    IDashboardComponent,
    IComponent,
    IDisposable,
    ISupportInitialize,
    ISupportPrefix,
    IDashboardDataSourceInternal,
    IFederationDataProvider,
    ICloneable<DashboardSqlDataSource>,
    IAssignable<DashboardSqlDataSource>,
    IQueryDataSource<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 Versions

.NET Framework Data Provider

.NET 6+ Data Provider

Microsoft SQL Server

2005 or higher

2005 Express or higher

Azure SQL Database

System.Data.SqlClient

System.Data.dll (Included in .NET Framework)

System.Data.SqlClient

Microsoft Access

Access 2000 or higher

Access 2007 or higher

System.Data.dll

System.Data.OleDb

Microsoft SQL Server CE

3.5, 4.0

System.Data.SqlServerCe.dll (Included in .NET Framework)

 

Oracle Database

9i or higher

Oracle.ManagedDataAccess

System.Data.OracleClient.dll

Oracle.DataAccess.dll

Oracle.ManagedDataAccess

Amazon Redshift

n/a

Npgsql

Npgsql

Google BigQuery

Only legacy SQL functions and operations are supported

ODBC driver for BigQuery

ODBC driver for BigQuery

Teradata

13.0 or higher

Teradata.Client.Provider

Teradata.Client.Provider

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

 

SAP SQL Anywhere

11 or higher

Sap.Data.SQLAnywhere

 

IBM DB2

9.5 or higher

IBM.Data.DB2.dll

IBM.Data.DB2.Core

Firebird

1.5 or higher, Dialect 3

FirebirdSql.Data.FirebirdClient

FirebirdSql.Data.Firebird.dll

FirebirdSql.Data.FirebirdClient

MySQL

4.1 or higher

5.5, 5.6, 5.7, 8.0, 8.3 (for the MySqlConnector provider)

MySqlConnector

MySql.Data

MySqlConnector

MySql.Data

Pervasive PSQL

9.x or higher

Pervasive.Data.SqlClient.dll

 

PostgreSQL

7.x or higher

Npgsql

Npgsql

VistaDB

4, 5, 6

VistaDB.6.dll

 

SQLite

3.x

System.Data.SQLite

Microsoft.Data.SQLite

System.Data.SQLite

Microsoft.Data.SQLite

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

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;
        }
    }
}
See Also