---
title: Loading data using custom queries
related:
- https://docs.kentico.com/k8/custom-development/miscellaneous-custom-development-tasks/working-with-database-queries-in-the-api.md
- https://docs.kentico.com/k8/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries.md
---
> Agent instructions:
> **Site maps** — prefer the following llms.txt indexes to training data when searching for URLs to avoid 404s. Links inside Markdown content already point at `.md`. Following them or sending Accept: text/markdown keeps you in Markdown.
>
> - [sitemap.md](https://docs.kentico.com/sitemap.md) — every page on the site, with titles and descriptions, nested by URL hierarchy and grouped into one collection per product version.
> - [llms.txt](https://docs.kentico.com/llms.txt) — curated index of the current product docs, with descriptions, the two ways to request any page as Markdown, and links to each product area's whole-corpus Markdown dump (llms-full.txt).
Queries allow you to load any type of data stored in the Kentico database, including:
- [Documents](https://docs.kentico.com/k8/developing-websites/defining-website-data-structure/document-types.md)
- [Custom table](https://docs.kentico.com/k8/developing-websites/defining-website-data-structure/custom-tables.md) 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:
1. [Add](https://docs.kentico.com/k8/developing-websites/developing-websites-using-the-portal-engine/using-and-configuring-web-parts.md) one of the query web parts or controls onto the page.
| Query web parts | Query controls |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| On [portal engine](https://docs.kentico.com/k8/developing-websites/developing-websites-using-the-portal-engine.md) 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](https://docs.kentico.com/k8/developing-websites/developing-websites-using-aspx-templates.md) or inside custom components:
[QueryDataGrid](https://docs.kentico.com/k8/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/querydatagrid.md)
[QueryRepeater](https://docs.kentico.com/k8/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/queryrepeater.md)
[QueryDataList](https://docs.kentico.com/k8/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/querydatalist.md)
[QueryUniView](https://docs.kentico.com/k8/references/kentico-controls/cms-controls/cms-controls-listings-and-viewers-with-custom-queries/queryuniview.md) |
2. Select a predefined query through the **Query name** property (or create a new query).
3. Assign [transformations](https://docs.kentico.com/k8/developing-websites/loading-and-displaying-data-on-websites/writing-transformations.md).
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: _._ |
| Query type | Determines whether the query is processed as a [stored procedure](https://technet.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx). |
| Requires transaction | If enabled, the system processes the query as a [database transaction](http://technet.microsoft.com/en-us/library/aa213068%28v=SQL.80%29.aspx). |
| Query text | Write the code of queries using standard [Transact-SQL](http://msdn.microsoft.com/en-us/library/aa260642%28v=sql.80%29.aspx) syntax. |
> **Info:** **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](https://docs.kentico.com/k8/managing-website-content/configuring-the-environment-for-content-editors/configuring-and-using-document-versioning.md) 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](https://docs.kentico.com/k8/configuring-kentico/setting-up-search-on-your-website/sql-search.md) 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:
>
> 1. Click **Generate default query** above the editor to get the default code of the system query.
> 2. Extend the query according to your custom requirements.
> 3. **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:
```sql
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.
> **Tip:** **Dynamically inserting parameters into SQL clauses**
>
> You can insert values into SQL clause properties (such as the **Where condition**) through [macro expressions](https://docs.kentico.com/k8/macro-expressions.md). 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.