---
title: Working with database queries in the API
related:
  - https://docs.kentico.com/k8/developing-websites/loading-and-displaying-data-on-websites/loading-data-using-custom-queries.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).

## Running queries

You can execute system queries in your custom code.

```csharp title="Examples"

using System.Data;
using CMS.DataEngine;

...

// Executes the cms.user.selectall query, with specified columns, and a WHERE and ORDER BY clause
DataSet users = new DataQuery("cms.user.selectall")
    .Columns("UserID", "UserName", "FullName")
    .Where("UserName", QueryOperator.Like, "%admin%")
    .OrderBy("FullName")
    .Execute();

// Assigns the value "administrator" to the "@UserName" query parameter
QueryDataParameters parameters = new QueryDataParameters();
parameters.Add("@UserName", "administrator");

// Executes the cms.user.selectbyusername query
// Uses the "administrator" value for the "@UserName" parameter in the query's code
var query = new DataQuery("cms.user.selectbyusername");
query.Parameters = parameters;
DataSet selectedUser = query.Result;


```

To create custom queries or modify existing ones, use one of the following approaches:

- Manually edit the **CMS\_Query** database table.
- Manage queries through the administration interface in:

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

## Pre-processing queries

You can pre-process database queries using the **ExecuteQuery.Before** event of the **SqlEvents** class. The system raises the event before executing any database query. The event allows you to dynamically modify the behavior and code of queries.

To create a handler for the _ExecuteQuery.Before_ event:

1. Create a new class in your project's **App\_Code** folder (or **CMSApp\_AppCode -> Old\_App\_Code** if you installed the project as a web application).
2. Add a reference to the **CMS.Base** and **CMS.DataEngine** namespaces.
3. Extend the **CMSModuleLoader** partial class.
4. Create a new class inside _CMSModuleLoader_ that inherits from **CMSLoaderAttribute**.
5. Add the attribute defined by the internal class before the definition of the _CMSModuleLoader_ partial class.
6. Override the **Init** method inside the attribute class and assign a handler method to the _SqlEvents.ExecuteQuery.Before_ event.
7. Define the handler method as required.

The system automatically runs the **Init** method when the application starts, which registers your event handler.

The following handler example replaces _CMS\_User_ with _View\_CMS\_User_ in the query code when processing the _cms.user.selectall_ query:

```csharp

using System.Data;

using CMS.Base;
using CMS.DataEngine;

[PrePocessingQueriesLoaderModule]
public partial class CMSModuleLoader
{
    /// <summary>
    /// Module registration
    /// </summary>
    private class PrePocessingQueriesLoaderModuleAttribute : CMSLoaderAttribute
    {
        /// <summary>
        /// Initializes the module
        /// </summary>
        public override void Init()
        {
            SqlEvents.ExecuteQuery.Before += BeforeExecuteQuery;
        }

        static void BeforeExecuteQuery(object sender, ExecuteQueryEventArgs<DataSet> e)
        {
            if (e.Query.Name != null)
            {
                switch (e.Query.Name.ToLowerCSafe())
                {
                    case "cms.user.selectall":
                        e.Query.Text = e.Query.Text.Replace("CMS_User", "View_CMS_User");
                        break;
                }
            }
        }
    }
}

```

## Post-processing queries

You can process the results of queries using the **ExecuteQuery.After** event of the **SqlEvents** class. The system raises the event after executing any database query. The event allows you to use or modify the data retrieved by queries.

To create a handler for the _ExecuteQuery.After_ event:

1. Create a new class in your project's **App\_Code** folder (or **CMSApp\_AppCode -> Old\_App\_Code** if you installed the project as a web application).
2. Add a reference to the **CMS.Base** and **CMS.DataEngine** namespaces.
3. Extend the **CMSModuleLoader** partial class.
4. Create a new class inside _CMSModuleLoader_ that inherits from **CMSLoaderAttribute**.
5. Add the attribute defined by the internal class before the definition of the _CMSModuleLoader_ partial class.
6. Override the **Init** method inside the attribute class and assign a handler method to the _SqlEvents.ExecuteQuery.After_ event.
7. Define the handler method as required.

The system automatically runs the **Init** method when the application starts, which registers your event handler.

The following handler example dynamically generates the full name of users and overrides the default full name (whenever the _cms.user.selectall_ query is executed).

```csharp

using System.Data;

using CMS.Base;
using CMS.DataEngine;

[PostPocessingQueriesModuleLoader]
public partial class CMSModuleLoader
{
    /// <summary>
    /// Module registration
    /// </summary>
    private class PostPocessingQueriesModuleLoaderAttribute : CMSLoaderAttribute
    {
        /// <summary>
        /// Initializes the module
        /// </summary>
        public override void Init()
        {
            SqlEvents.ExecuteQuery.After += AfterExecuteQuery;
        }

        static void AfterExecuteQuery(object sender, ExecuteQueryEventArgs<DataSet> e)
        {
            if (e.Query.Name != null)
            {
                switch (e.Query.Name.ToLower())
                {
                    case "cms.user.selectall":
                        if (e.Result != null)
                        {
                            DataTable dt = e.Result.Tables[0];
                            foreach (DataRow dr in dt.Rows)
                            {
                                dr["FullName"] = dr["FirstName"] + " " + dr["MiddleName"] + " " + dr["LastName"];
                            }
                        }
                        break;
                }
            }
        }
    }
}

```