Loading data using custom queries

Queries allow you to load any type of data stored in the Kentico database, including:

Documents
Custom table records
General system objects (classes) such as users, forums, newsletters, SKUs, etc.

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:

- **Query data source** (connected with a basic listing web part)- **Grid with custom query**- **Repeater with custom query**- **Datalist with custom query**- **Universal viewer with custom query**

Use controls to display query data on ASPX page templates or inside custom components:

- [QueryDataGrid](/k8/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/querydatagrid){.internal-link }- [QueryRepeater](/k8/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/queryrepeater){.internal-link }- [QueryDataList](/k8/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/querydatalist){.internal-link }- [QueryUniView](/k8/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/queryuniview){.internal-link }

Select a predefined query through the Query name property (or create a new query).
Assign transformations.

The component loads data from the database using the selected query, and displays the results according to the specified transformation.

Managing queries

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:

Document types - Document types -> Edit document type -> Queries
Custom tables - Custom tables -> Edit table -> Queries
System classes - Modules -> Edit module -> Classes -> Edit class -> Queries

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.
Requires transaction	If enabled, the system processes the query as a database transaction.
Query text	Write the code of queries using standard Transact-SQL 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:

select - loads a single record
selectall - loads all records from the table (with optional conditions)
selectversions - loads document version records
insert - adds a record
insertidentity - adds a record with an explicitly set ID column
update - updates a single record
updateall - updates multiple records in the table (based on conditions)
delete - removes a single record
deleteall - deletes records from the table (based on a where condition)
searchtree - used by the SQL search to search the fields of individual document types

Warning: Changing the default queries without considering all options may prevent the system from working correctly. We recommend the following customization approach:

Click Generate default query above the editor to get the default code of the system query.
Extend the query according to your custom requirements.
Save the query.

Using properties to modify queries

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) documents:




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:

{% CurrentAliasPath %} - alias path of the current page.
{% CurrentDocumentCulture.CultureCode %} - the culture code of the language in which the current document is displayed.
{% CurrentSiteID %} - SiteID value of the current site.

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.