windowsforms-119019-common-features-data-source-wizard-query-builder.md
This document describes the Query Builder and consists of the following sections:
The Query Builder provides a visual interface for constructing SQL queries used to access an SqlDataSource‘s tables and views.
Note
The Query Builder is not available for an object, Entity Framework, or Excel data sources.
The following DevExpress products use the Query Builder for binding to data:
Use the SqlWizardSettings.QueryBuilderDiagramView option to specify whether the Query Builder should use a table-like or diagram-based interface for selecting fields.
You can invoke the Query Builder from the Data Source Wizard‘s query customization page by clicking the Run Query Builder… button.
Another version of the query customization page appears when you use the Report Wizard to bind reports to data. In this case, click the button in the Queries category to create a new query using the Query Builder.
Use the following SqlDataSourceUIHelper class methods to invoke the Query Builder in your WinForms application in a standalone mode:
These methods’ parameters include a QueryBuilderEditQueryContext instance and a custom QueryBuilderRunner implementation that define various Query Builder options.
See How to use the Query Builder in a WinForms application for a sample code.
You can add a specific data table or view to a query by dragging the corresponding item from the list of available tables and dropping it onto the list of data tables to be used.
Enable check boxes for the table fields that you want to include in the query result set.
Each table provides the context menu, which allows you to rename the table or remove it from the query.
Click the list of available tables on the left and press CTRL+F to search for a specific table or view.
You can join multiple tables within the same query. The Query Builder automatically highlights tables related to any of the previously added tables. Drag-and-drop a subordinate table in the same way you added a main table to include it in a query and automatically create an inner join relation based on a key column.
Alternatively, you can join tables by clicking the plus button in a row corresponding to a key column.
You can customize the relationship by right-clicking it on the diagram and selecting Edit Relation in the invoked context menu. Use the Join Editor to select the join type ( Left Outer or Inner ), apply a logical operator ( Equals to , Is less than , etc.) and column key fields.
A left outer join returns an inner join’s values, along with all the values in the “left” table that do not match the “right” table, including rows with NULL (empty) values in the key field.
When the left outer join is selected, the relationship line displays an arrow pointing to the “right” table.
You can manually join tables if they do not have a relationship at the database level. In this case, when you drag-and-drop a table onto the list of tables, the Join Editor is automatically invoked allowing you to construct a custom join relationship.
After executing the query, it returns a “flat” table composed of data records selected based on the specified join options.
Click the Edit Parameters button to invoke the Query Parameters dialog, which allows you to add and remove query parameters as well as specify parameter settings.
The following properties are available for each query parameter:
You can then access the created parameters on the Configure Query Parameters wizard page.
Click the Filter… button in the Query Builder to specify filter criteria. This invokes the Filter Editor , which provides the following capabilities:
Filter Tab
Group Filter Tab
Other Options
The Query Builder displays the column list under the data source editor, which provides various shaping options:
Column
Table
Alias
Output
Sorting Type
Sort Order
Group By
Aggregate
Note
You should apply aggregation/grouping to either all columns or none of them.
Enable the Allow Edit SQL option to customize an SQL string manually. Switching to this mode disables the Query Builder’s visual features in favor of the specified SQL string value.
Important
In the End-User Report Designers, manual SQL query editing is considered unsafe and is disabled by default. See the following documents for more information:
Custom SQL queries are validated before their execution. Although the default validation mechanism only allows custom queries containing SELECT statements (except for SELECT INTO clauses), it is not safe because it does not prevent the execution of potentially harmful requests. We recommend implementing a validation logic that permits only execution of specific query kinds. See the Providing Custom Query Validation in Report Designer document for more information.
You can preview the query execution’s result in a tabular form by clicking the Preview Results button.
This opens the Data Preview window displaying the query result set (limited to the first 1000 data records).