Custom SQL Queries
- 2 minutes to read
The Query Editor dialog allows you to specify SQL queries manually by typing them in the SQL String editor. Note that this capability is disabled by default. To allow end-users to specify custom SQL queries in the Query Editor, set the SqlWizardSettings.EnableCustomSql property exposed by the DashboardDataSourceWizardSettings class to true. Use the DataSourceWizard property to get access to the DataSourceWizard’s settings.
Warning
The use of custom SQL queries can lead to inadvertent or unauthorized modifications to your data/database structure. Ensure that you follow best practices and implement the appropriate user read/write privileges at the database level.
Note
Various SQL extensions can require a special character as the variable’s first character. For instance, this can be the “at” sign (@) for Transact-SQL or the colon (:) for PL/SQL. Insert the required character before the parameter name within the custom SQL query.
After you specified the SQL query, click Next. On the next page, you can add query parameters and specify its settings.
Validation of Custom SQL Queries
By default, the Query Editor allows you to use only SELECT statements in the query. If necessary, you can disable query validation and allow end-users to include not only SELECT statements into SQL queries. This capability is controlled by the static SqlDataSource.DisableCustomQueryValidation property exposed by the DashboardSqlDataSource class. The Dashboard Designer also allows you to manage validation of custom SQL queries by handling the DashboardDesigner.ValidateCustomSqlQuery event which allows you to specify your own query validation logic.
To manage the validation of custom SQL queries in the Dashboard Viewers, use the following events.
- DashboardViewer.ValidateCustomSqlQuery;
- ASPxDashboard.ValidateDashboardCustomSqlQuery/(for ASP.NET MVC Dashboard Extension, use DashboardConfigurator.ValidateCustomSqlQuery);
- Dashboard.ValidateCustomSqlQuery.