Custom SQL Query in Report Designer
- 2 minutes to read
This document describes how to allow users to bypass the Query Builder in SQL query editing.
Enable Custom SQL Query
To allow users to specify custom SQL queries in a reporting web application, use the following code:
Call the static DefaultReportDesignerContainer.EnableCustomSql method on application start:
using DevExpress.XtraReports.Web.ReportDesigner;
// ...
protected void Application_Start(object sender, EventArgs e) {
DefaultReportDesignerContainer.EnableCustomSql();
// ...
}
Call the ReportDesignerConfigurationBuilder.EnableCustomSql method on application start:
using DevExpress.AspNetCore;
using DevExpress.AspNetCore.Reporting;
//...
public class Startup {
//...
public void ConfigureServices(IServiceCollection services) {
services.AddDevExpressControls();
services.AddMvc();
services.ConfigureReportingServices(configurator => {
configurator.ConfigureReportDesigner(designerConfigurator => {
designerConfigurator.EnableCustomSql();
});
});
}
//...
}
Important
Do not enable custom SQL queries if untrusted parties have access to your application. Refer to the following topic for more information: General Security Considerations.
Write Custom SQL Query
On the SQL Data Source Wizard’s Settings page.
When custom SQL queries are enabled, the plus button invokes a context menu. Users can choose whether to run the Query Builder or write a custom SQL query.
In the Create a Query or Select a Stored Procedure dialog that is invoked when users click the
button next to a data source’s name in the Field List.
When custom SQL queries are enabled, users can type a custom query.
.
Custom SQL Query Validation
Custom SQL queries are validated before execution. The default validation mechanism allows only queries with SELECT statements (except for SELECT INTO clauses) but does not prevent execution of potentially harmful requests. To improve security, implement validation logic that allows users to execute only queries that meet certain criteria. Review the following topic for information on how to implement custom validation: Custom SQL Query Validation.