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
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 Dashboard
Sql property.Data Source. Connection Name 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. | Firebird Firebird | |
Google Big | n/a | ||
IBM DB2 | 9. | IBM. | |
Microsoft Access | Access 2000 or higher Access 2007 or higher | System. | |
Microsoft SQL Server | SQL Server 2005 (with Express) SQL Server 2008 (with R2 & Express) SQL Server 2012, 2014, 2016, 2017, 2019, 2022 (with Express & Local SQL Azure™ Database Azure SQL Database |
System. | Microsoft. If the Microsoft. |
Microsoft SQL Server CE | 3. | System. |
|
My | 4. 5. | ||
Oracle Database | 9i, 10g, 11g, 12c, 18c, 21c, 23ai |
System. Oracle. | |
Pervasive PSQL | 9. | Pervasive. |
|
Postgre | 7, 8, 9, 10, 11, 12, 15, 16, 17 | ||
SAP HANA | 2. |
Sap. | |
SAP Sybase Advantage | Advantage Database Server 9. |
| |
SAP Sybase ASE | Sybase Adaptive Server 12. |
| |
SAP SQL Anywhere | 11 or higher |
| |
SQLite | 3. | ||
Teradata | 13. | ||
Vista | 4, 5, 6 | Vista Vista |
|
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;
}
}
}