DashboardDesigner.ValidateCustomSqlQuery Event

Allows you to validate a custom SQL query of the DashboardSqlDataSource.

Namespace: DevExpress.DashboardWin

Assembly: DevExpress.Dashboard.v24.1.Win.dll

NuGet Package: DevExpress.Win.Dashboard

Declaration

C#

public event ValidateDashboardCustomSqlQueryEventHandler ValidateCustomSqlQuery

VB.NET

Public Event ValidateCustomSqlQuery As ValidateDashboardCustomSqlQueryEventHandler

Event Data

The ValidateCustomSqlQuery event's data class is ValidateDashboardCustomSqlQueryEventArgs. The following properties provide information specific to this event:

Property	Description
ConnectionName	Gets the name of the connection to the data source containing a custom SQL query.
ConnectionParameters	Gets parameters used to establish a connection to the data source containing a custom SQL query.
CustomSqlQuery	Gets the custom SQL query that should be checked.
DataSourceComponentName	Gets or sets the component name of the data source for which the event was raised.
DataSourceName	Gets or sets the name of the data source for which the event was raised.
ExceptionMessage	Gets or sets the exception message returned after custom SQL query validation.
Valid	Gets or sets whether the current custom SQL query is valid.

Remarks

This event occurs before a CustomSqlQuery is executed. Custom Queries are stored in the Queries collection of the DashboardSqlDataSource.

The event fires for each custom SQL query in the following cases:

• The control loads a dashboard that gets data from a custom SQL query.
• The control’s ReloadData method forces the DashboardSqlDataSource to update its data.

Note

For security reasons, only SELECT queries are allowed and valid. If the custom query contains statements other than SELECT, or the query is specified incorrectly, the e.Valid property is set to false. The e.ExceptionMessage property contains the text specified by the DataAccessStringId.CustomSqlQueryValidationException value.

If necessary, you can disable query validation and allow users to include other statements in addition to SELECT statements in SQL queries. To accomplish this, follow the steps below:

• Set the static SqlDataSource.DisableCustomQueryValidation property to true. The DashboardSqlDataSource class exposes the DisableCustomQueryValidation property.
• Handle the ValidateCustomSqlQuery event to manage validation of custom SQL queries. Override the value of the ValidateCustomSqlQueryEventArgs.Valid flag and corresponding ValidateCustomSqlQueryEventArgs.ExceptionMessage message. Be careful when you set the e.Valid option to true because this setting skips the default validation.

The following code snippet shows how to handle the DashboardDesigner.ValidateCustomSqlQuery event to restrict access to the Invoices table in a custom query.

Default Validation Section

Checks the validity of the custom query. If the custom query contains statements other than SELECT, or the query is specified incorrectly, the e.Valid property is set to false.

Custom Validation Section

Specifies the custom validation logic and the e.ExceptionMessage text.

C#

// ...
dashboardDesigner1.ValidateCustomSqlQuery += dashboardDesigner1_ValidateCustomSqlQuery;
// ...
private void dashboardDesigner1_ValidateCustomSqlQuery(object sender, ValidateDashboardCustomSqlQueryEventArgs e) {
    // Default Validation
    if(!e.Valid) return;

    // Custom Validation
    if(e.CustomSqlQuery.Sql.Contains("Invoices"))
    {
        e.Valid = false;
        e.ExceptionMessage = "You do not have access to Invoices";
    }
}

VB.NET

' ...
Private dashboardDesigner1.ValidateCustomSqlQuery += AddressOf dashboardDesigner1_ValidateCustomSqlQuery
' ...
Private Sub dashboardDesigner1_ValidateCustomSqlQuery(ByVal sender As Object, ByVal e As ValidateDashboardCustomSqlQueryEventArgs)
    ' Default Validation
    If Not e.Valid Then
        Return
    End If

    ' Custom Validation
    If e.CustomSqlQuery.Sql.Contains("Invoices") Then
        e.Valid = False
        e.ExceptionMessage = "You do not have access to Invoices"
    End If
End Sub

Implements

ValidateCustomSqlQuery