Loading data using custom queries

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

• Pages
• Custom table records
• General system objects (classes) - both default (such as users, forums, newsletters, SKUs) and custom module classes

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:

1. 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](/k10/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/querydatagrid){.internal-link }- [QueryRepeater](/k10/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/queryrepeater){.internal-link }- [QueryDataList](/k10/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/querydatalist){.internal-link }- [QueryUniView](/k10/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/queryuniview){.internal-link }

2. 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>

3. 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:

• Page types - Page types -> Edit page type -> Queries
• Custom tables - Custom tables -> Edit table -> Queries
• Data classes - Modules -> Edit module -> Classes -> Edit class -> Queries

The following properties are available when creating or editing queries:

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:

For example, the following query selects pages of the Page (menu item) type:

SELECT ##TOPN## ##COLUMNS## 
FROM View_CMS_Tree_Joined AS V 
INNER JOIN CONTENT_MenuItem AS C 
ON V.DocumentForeignKeyValue = C.MenuItemID AND V.ClassName = N'CMS.MenuItem' 
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 dynamic parameters into SQL clauses by adding macro expressions into the values of the related web part properties (such as the Where condition). For example:

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

Note: Macros are only supported in web part properties. You cannot place macros directly into the SQL code of queries or stored procedures.