Invoke and Customize the Query Builder
- 5 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
Use the following SqlDataSourceUIHelper class methods to invoke the Query Builder in a WinForms application:
These methods’ parameters include a QueryBuilderEditQueryContext instance and a custom QueryBuilderRunner implementation that define various Query Builder options.
#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.
Do the following to substitute the Query Builder with a custom dialog:
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.
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; }
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.
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>>(); } }
Add the custom services to the Report Designer by calling the XRDesignMdiController.AddService method at the application’s startup.
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(); } }