Invoke and Customize the Query Builder

• Sep 28, 2018
• 6 minutes to read

This document describes how to integrate the Query Builder without using the Data Source Wizard in which it is used initially:

• Invoke the Query Builder
• Customize the Query Builder

Invoke the Query Builder

Use the following SqlDataSourceUIHelper class methods to invoke the Query Builder in a WinForms application:

• SqlDataSourceUIHelper.AddQueryWithQueryBuilder
• SqlDataSourceUIHelper.EditQueryWithQueryBuilder

These methods’ parameters include a QueryBuilderEditQueryContext instance and a custom QueryBuilderRunner implementation that define various Query Builder options.

Tip

Online Example: How to use the Query Builder in a WinForms application

Customize the Query Builder

This document section describes how to replace the Query Builder with a custom dialog in a Data Source Wizard:

The Create a Query or Select a Stored Procedure (Multi-Query Version) wizard page initializes and displays the standard Query Builder dialog.

Tip

Online Example: Data Source Wizard - How to customize the standard Query Builder dialog

Do the following to substitute the Query Builder with a custom dialog:

1. Override the RunQueryBuilder virtual method of this page’s presenter (MultiQueryConfigurePage<TModel>).

   In this method’s body, implement the logic required to display a custom Query Builder form and obtain the Query Builder execution results.

   C#

   using DevExpress.DataAccess.Sql;
   using DevExpress.DataAccess.Wizard.Presenters;
   using DevExpress.DataAccess.Wizard.Model;
   using System.Linq;
   using System.Windows.Forms;
   // ...
   
   protected override SqlQuery RunQueryBuilder(SqlQuery query) {
       string tableName = null;
       int topRecords = 100;
   
       // Analyze the query that is being edited (if any).
       SelectQuery selectQuery = query as SelectQuery;
       if (selectQuery != null && selectQuery.Tables.Count == 1 && selectQuery.Columns.Count == 1 
           && string.IsNullOrEmpty(selectQuery.FilterString) && selectQuery.Skip == 0) {
           var columns = selectQuery.Columns[0] as AllColumns;
           if (columns != null && columns.Table == null) {
               tableName = selectQuery.Tables[0].Name;
               topRecords = selectQuery.Top;
           }
       }
       if (tableName == null && query != null) {
           ShowMessage("This query is too complex and cannot be displayed.");
           return null;
       }
   
       // Display a custom query builder form.
       using (var form = new FrmQueryBuilder()) {
           form.Initialize(DbTables.Keys.OrderBy(s => s));
           if (tableName != null) {
               form.Selected = tableName;
               form.TopRecords = topRecords;
           }
           if (form.ShowDialog(((Control)View).FindForm()) != DialogResult.OK)
               return null;
           tableName = form.Selected;
           topRecords = form.TopRecords;
       }
   
       // Generate a query based on user input.
       if (string.IsNullOrEmpty(tableName))
           return null;
       SqlQuery result = SelectQueryFluentBuilder
           .AddTable(tableName)
           .SelectAllColumns()
           .Top(topRecords)
           .Build(tableName);
   
       // Make sure that the table columns have been loaded.
       dbSchemaProvider.LoadColumns(Model.DataConnection, 
           DBSchema.Tables.First(t => t.Name == tableName));
   
       return result;
   }
   

   VB.NET

   Imports DevExpress.DataAccess.Sql
   Imports DevExpress.DataAccess.Wizard.Presenters
   Imports DevExpress.DataAccess.Wizard.Model
   Imports System.Linq
   Imports System.Windows.Forms
   ' ...
   
   Protected Overrides Function RunQueryBuilder(ByVal query As SqlQuery) As SqlQuery
       Dim tableName As String = Nothing
       Dim topRecords As Integer = 100
   
       ' Analyze the query that is being edited (if any).
       Dim selectQuery As SelectQuery = TryCast(query, SelectQuery)
       If selectQuery IsNot Nothing AndAlso selectQuery.Tables.Count = 1 AndAlso selectQuery.Columns.Count = 1 _
           AndAlso String.IsNullOrEmpty(selectQuery.FilterString) AndAlso selectQuery.Skip = 0 Then
           Dim columns = TryCast(selectQuery.Columns(0), AllColumns)
           If columns IsNot Nothing AndAlso columns.Table Is Nothing Then
               tableName = selectQuery.Tables(0).Name
               topRecords = selectQuery.Top
           End If
       End If
       If tableName Is Nothing AndAlso query IsNot Nothing Then
           ShowMessage("This query is too complex and cannot be displayed.")
           Return Nothing
       End If
   
       ' Display a custom query builder form.
       Using form = New FrmQueryBuilder()
           form.Initialize(DbTables.Keys.OrderBy(Function(s) s))
           If tableName IsNot Nothing Then
               form.Selected = tableName
               form.TopRecords = topRecords
           End If
           If form.ShowDialog(CType(View, Control).FindForm()) <> DialogResult.OK Then
               Return Nothing
           End If
           tableName = form.Selected
           topRecords = form.TopRecords
       End Using
   
       ' Generate a query based on user input.
       If String.IsNullOrEmpty(tableName) Then
           Return Nothing
       End If
       Dim result As SqlQuery = SelectQueryFluentBuilder.
           AddTable(tableName).
           SelectAllColumns().
           Top(topRecords).
           Build(tableName)
   
       ' Make sure that the table columns have been loaded.
       dbSchemaProvider.LoadColumns(Model.DataConnection, DBSchema.Tables.First(Function(t) t.Name = tableName))
   
       Return result
   End Function
   

2. Implement the IWizardCustomizationService interface to register a custom query configuration form.

   To invoke the custom query customization form instead of the default Query Builder, implement the ISqlEditorsCustomizationService interface.

   C#

   using DevExpress.DataAccess.UI.Sql;
   using DevExpress.DataAccess.UI.Wizard;
   using DevExpress.DataAccess.Wizard.Model;
   using DevExpress.DataAccess.Wizard.Presenters;
   using DevExpress.XtraReports.Wizards;
   using System.ComponentModel.Design;
   // ...
   
   class MyWizardCustomizationService : IWizardCustomizationService, ISqlEditorsCustomizationService {
       #region Implementation of IWizardCustomizationService
       public void CustomizeReportWizard(IWizardCustomization<XtraReportModel> tool) {
           // Register a custom query customization page in the Report Wizard.
           tool.RegisterPage<MultiQueryConfigurePage<XtraReportModel>, MyConfigureQueryPageEx<XtraReportModel>>();
       }
   
       // Register a custom query customization page in the Data Source Wizard. 
       public void CustomizeDataSourceWizard(IWizardCustomization<XtraReportModel> tool) { RegisterPages(tool); }
   
       public bool TryCreateDataSource(IDataSourceModel model, out object dataSource, out string dataMember) {
           dataSource = null;
           dataMember = null;
           return false;
       }
   
       public bool TryCreateReport(IDesignerHost designerHost, XtraReportModel model, object dataSource, string dataMember) { return false; }
   
       #endregion
   
       #region Implementation of ISqlEditorsCustomizationService
       // Replace the Query Builder with a custom form.
       public void CustomizeEditor(SqlEditorId editor, IWizardCustomization<SqlDataSourceModel> tool) {
           if (editor == SqlEditorId.Query)
               RegisterPages(tool);
       }
       #endregion
   
       static void RegisterPages<TModel>(IWizardCustomization<TModel> tool) where TModel : ISqlDataSourceModel {
           tool.RegisterPage<MultiQueryConfigurePage<TModel>, MyConfigureQueryPage<TModel>>();
       }
   }
   

   VB.NET

   Imports DevExpress.DataAccess.UI.Sql
   Imports DevExpress.DataAccess.UI.Wizard
   Imports DevExpress.DataAccess.Wizard.Model
   Imports DevExpress.DataAccess.Wizard.Presenters
   Imports DevExpress.XtraReports.Wizards
   Imports System.ComponentModel.Design
   ' ...
   
   Friend Class MyWizardCustomizationService
       Implements IWizardCustomizationService, ISqlEditorsCustomizationService
   
       #Region "Implementation of IWizardCustomizationService"
       Public Sub CustomizeReportWizard(ByVal tool As IWizardCustomization(Of XtraReportModel)) _
           Implements IWizardCustomizationService.CustomizeReportWizard
           ' Register a custom query customization page in the Report Wizard.
           tool.RegisterPage(Of MultiQueryConfigurePage(Of XtraReportModel), MyConfigureQueryPageEx(Of XtraReportModel))()
       End Sub
   
       ' Register a custom query customization page in the Data Source Wizard. 
       Public Sub CustomizeDataSourceWizard(ByVal tool As IWizardCustomization(Of XtraReportModel)) _
           Implements IWizardCustomizationService.CustomizeDataSourceWizard
           RegisterPages(tool)
       End Sub
   
       Public Function TryCreateDataSource(ByVal model As IDataSourceModel, <System.Runtime.InteropServices.Out()> ByRef dataSource As Object, <System.Runtime.InteropServices.Out()> ByRef dataMember As String) As Boolean _
           Implements IWizardCustomizationService.TryCreateDataSource
           dataSource = Nothing
           dataMember = Nothing
           Return False
       End Function
       Public Function TryCreateReport(ByVal designerHost As IDesignerHost, ByVal model As XtraReportModel, ByVal dataSource As Object, ByVal dataMember As String) As Boolean _
           Implements IWizardCustomizationService.TryCreateReport
           Return False
       End Function
       #End Region
   
       #Region "Implementation of ISqlEditorsCustomizationService"
       ' Replace the Query Builder with a custom form.
       Public Sub CustomizeEditor(ByVal editor As SqlEditorId, ByVal tool As IWizardCustomization(Of SqlDataSourceModel)) _
           Implements ISqlEditorsCustomizationService.CustomizeEditor
           If editor = SqlEditorId.Query Then
               RegisterPages(tool)
           End If
       End Sub
       #End Region
   
       Private Shared Sub RegisterPages(Of TModel As ISqlDataSourceModel)(ByVal tool As IWizardCustomization(Of TModel))
           tool.RegisterPage(Of MultiQueryConfigurePage(Of TModel), MyConfigureQueryPage(Of TModel))()
       End Sub
   End Class
   

3. Add the custom services to the Report Designer by calling the XRDesignMdiController.AddService method at the application’s startup.

   C#

   using DevExpress.DataAccess.UI.Sql;
   using DevExpress.XtraEditors;
   using DevExpress.XtraReports.Wizards3;
   // ...
   
   public partial class FrmDesigner : XtraForm {
       public FrmDesigner() {
           InitializeComponent();
           MyWizardCustomizationService customizationService = new MyWizardCustomizationService();
           reportDesignerMDIController.AddService(typeof(IWizardCustomizationService), customizationService);
           reportDesignerMDIController.AddService(typeof(ISqlEditorsCustomizationService), customizationService);
           reportDesignerMDIController.CreateNewReportWizard();
       }
   }
   

   VB.NET

   Imports DevExpress.DataAccess.UI.Sql
   Imports DevExpress.XtraEditors
   Imports DevExpress.XtraReports.Wizards
   ' ...
   
   Partial Public Class FrmDesigner
       Inherits XtraForm
   
       Public Sub New()
           InitializeComponent()
           Dim customizationService As New MyWizardCustomizationService()
           reportDesignerMDIController.AddService(GetType(IWizardCustomizationService), customizationService)
           reportDesignerMDIController.AddService(GetType(ISqlEditorsCustomizationService), customizationService)
           reportDesignerMDIController.CreateNewReportWizard()
       End Sub
   End Class