Query Builder

• Dec 17, 2020
• 3 minutes to read

Overview

The ASP.NET MVC Query Builder is an HTML5/JS based control that allows you and your end-users to create and edit SQL queries without manual coding. It provides a rich and convenient interface to visually display all query elements, which simplifies the query construction process for users who are unfamiliar with SQL and its provider-specific dialects. The Query Builder generates SELECT statements incorporating query clauses for data filtering, grouping, and sorting. You can use these statements to supply data to various data-aware MVC controls (such as the Grid View or Chart Control).

Getting Started
	This section contains the following step-by-step tutorials that provide general information about using the ASP.NET MVC Query Builder: Lesson 1 - Add a Query Builder to a Web Application Lesson 2 - Use the Resulting Query
Data Binding
	The Query Builder is a data-aware control and requires a valid data connection to obtain database schema information for populating a table list. This connection also enables the Query Builder to generate the resulting query in a proper SQL dialect and automatically resolve table relations based on foreign keys.
Selecting and Joining Tables
	You can add specific tables or views to a query using the drag-and-drop operation without manually writing the query string. Enabling the table columns’ check boxes includes them in the query result set. The Query Builder allows you to join multiple tables or views in the same query and specify various join options. This control automatically resolves table relationships if they are defined at the database level. You can also manually construct relationships by connecting the table key fields.
Data Shaping
	The Query Builder supports various data-related features that allow you to process records obtained from a database: Sorting You can sort data against one or more columns and specify the sorting precedence to rearrange initial data. Grouping and Aggregation Grouping enables you to combine data values into groups based on identical field values. The Query Builder also supports a variety of aggregate functions including Count, Sum, Min, Max, Avg, etc. Filtering The Query Builder enables you to narrow the query result set by applying filter criteria to initial and grouped data. The built-in Filter Editor provides a visual interface to simplify the filtering process and construct criteria of any complexity.
Query Generation
	The Query Builder produces the following query objects: A string that specifies the final ‘SELECT’ statement and optionally contains ‘WHERE’, ‘GROUP BY’ and ‘ORDER BY’ query clauses; A SelectQuery object storing a SQL query’s high-level object model that you can reopen in the Query Builder for further editing.
Embedded Preview
	You can preview the query execution’s result at any moment using the built-in Data Preview window, which displays the result set in a tabular form. The Query Builder also provides the capability to preview the current SELECT statement created using a visual interface.
Localization
	You can localize the Query Builder using built-in localization mechanisms like in other .NET applications or by substituting particular localization strings programmatically.