DashboardSqlDataSource Class
An SQL data source that provides data for the dashboard.
Namespace: DevExpress.DashboardCommon
Assembly: DevExpress.Dashboard.v24.2.Core.dll
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:
Specify connection parameters to the SQL database. For this, create the DataConnectionParametersBase class descendant and set the properties specific for a data source type.
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.
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.
Add the query object to the DashboardSqlDataSource.Queries collection.
Call the DashboardSqlDataSource.Fill method.
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 | ||
Firebird | 1.5, 2.5.7, 3.0.2, 5.0.1 | FirebirdSql.Data.FirebirdClient.dll FirebirdSql.Data.Firebird.dll | |
Google BigQuery | n/a | ||
IBM DB2 | 9.5 or higher | IBM.Data.DB2.dll | |
Microsoft Access | Access 2000 or higher Access 2007 or higher | System.Data.dll | |
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.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) | ||
Oracle Database | 9i, 10g, 11g, 12c, 18c, 21c, 23ai |
System.Data.OracleClient.dll Oracle.DataAccess.dll | |
Pervasive PSQL | 9.x or higher | Pervasive.Data.SqlClient.dll |
|
PostgreSQL | 7, 8, 9, 10, 11, 12, 15, 16, 17 | ||
SAP HANA | 2.0 or higher |
Sap.Data.Hana.Core.v2.1.dll | |
SAP Sybase Advantage | Advantage Database Server 9.1 or higher |
| |
SAP Sybase ASE | Sybase Adaptive Server 12.0 or higher |
| |
SAP SQL Anywhere | 11 or higher |
| |
SQLite | 3.x | ||
Teradata | 13.0 or higher | ||
VistaDB | 4, 5, 6 | VistaDB.4.dll 4.* VistaDB.5.NET40.dll 5.* |
|
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.
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;
}
}
}