Is this really the version you are looking for? Kentico Xperience 8.1 Documentation is no longer updated. Use the switcher in the header to choose your product version.
Queries allow you to load any type of data stored in the Kentico database, including:
Using queries gives you full control over the data retrieval, but requires knowledge of SQL syntax and the Kentico database structure.
To display data loaded by a query on the website:
Add one of the query web parts or controls onto the page.
Query web parts | Query controls |
|---|---|
On portal engine pages, load and display query data using dedicated web parts:
| Use controls to display query data on ASPX page templates or inside custom components: |
Select a predefined query through the Query name property (or create a new query).
Note: For queries that belong to module data classes (both the default Kentico modules and custom modules), you cannot use the query selector. You need to manually type the full query name into the property in format: <class *code name*>.<query name>
Assign transformations.
The component loads data from the database using the selected query, and displays the results according to the specified transformation.
You can create new queries or modify existing ones through the administration interface. The location depends on the type of data that you wish to load:
The following properties are available when creating or editing queries:
Property | Description |
|---|---|
Query name | Serves as the identifier of the query. Cannot contain spaces, periods or other special characters. The full identifier of the query is in format: <*parent object code name*>.<query name> |
Query type | Determines whether the query is processed as a stored procedure (opens in a new tab). |
Requires transaction | If enabled, the system processes the query as a database transaction (opens in a new tab). |
Query text | Write the code of queries using standard Transact-SQL (opens in a new tab) syntax. |
Overriding default system queries
The system uses automatically generated queries for basic operations. If necessary, you can override the defaults by creating new queries with the following Query name:
Warning: Changing the default queries without considering all options may prevent the system from working correctly. We recommend the following customization approach:
Listing web parts and controls provide properties that set sections (clauses) of the query that loads the data. These properties allow users to adjust the data retrieval for individual instances of web parts or controls. To maintain this functionality for your custom queries, add the following expressions into the query code:
SQL code expression | Web part property | Control property |
|---|---|---|
##ORDERBY## | ORDER BY expression | OrderBy |
##COLUMNS## | Columns | SelectedColumns |
##TOPN## | Select top N | TopN |
##WHERE## | WHERE condition | WhereCondition |
For example, the following query selects page (menu item) pages:
SELECT ##TOPN## ##COLUMNS## FROM View_CONTENT_MenuItem_Joined WHERE (##WHERE##) ORDER BY ##ORDERBY##When executing queries for web parts/controls, the system replaces the expressions with the values of the corresponding properties.
Dynamically inserting parameters into SQL clauses
You can insert values into SQL clause properties (such as the Where condition) through macro expressions. For example:
By default, the system protects the SQL properties of web parts against SQL injection attacks, which may affect how macros are resolved. If the macro returns a string value that contains single quote characters ('), the system replaces them with two single quotes (''). This may cause SQL syntax errors.
To disable single quote escaping for specific macro expressions, add the handlesqlinjection macro parameter and set its value to false:
{% ... |(handlesqlinjection)false %}
Note: Disabling SQL protection may create security vulnerabilities if the macro resolves its value according to data that can be modified by the website's users, such as in the case of QueryString macros.