The requested page is not available for the requested platform. You are viewing the content for Default platform.

Custom SQL Queries

  • 2 min to read

The Dashboard Data Source Wizard allows you to type SQL queries in the SQL String editor. Note that this capability is disabled by default.

The following properties control custom SQL queries execution on the server:

Platform

Property

ASP.NET MVC, ASP.NET Core

DashboardConfigurator.AllowExecutingCustomSql

ASP.NET Web Forms

ASPxDashboard.AllowExecutingCustomSql

If you enable these properties to allow custom SQL queries execution, you can allow end users to edit a custom SQL string in the Data Source wizard with the following properties:

Platform

Property

Client Control

DataSourceWizardExtensionOptions.enableCustomSql

ASP.NET Core

DataSourceWizardOptionBuilder.EnableCustomSql

ASP.NET MVC

DashboardExtensionSettings.EnableCustomSql

ASP.NET Web Forms

ASPxDashboard.EnableCustomSql

Warning

The use of custom SQL queries can lead to inadvertent or unauthorized modifications to your data/database structure. Therefore, make sure that you follow best practices and implement the appropriate end-user read/write privileges at the database level.

wdd-custom-sql-page1

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 specify the SQL query, click Next. On the next page, you can add query parameters and specify its settings.

wdd-custom-sql-page2

Validation of Custom SQL Queries

The Dashboard Data Source Wizard allows you to use only SELECT statements in the query. You can disable query validation and allow end-users to include more than one SELECT statement into SQL queries. Use the static SqlDataSource.DisableCustomQueryValidation property exposed by the DashboardSqlDataSource class to control this capability.

The Web Dashboard allows you to manage validation of custom SQL queries. Handle the ASPxDashboard.ValidateDashboardCustomSqlQuery / DashboardConfigurator.ValidateCustomSqlQuery event to specify your own query validation logic.