Working with database queries in the API

Running queries

You can execute system queries in your custom code.

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:
- Page types -> Edit page 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:

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).
Add a reference to the CMS.Base and CMS.DataEngine namespaces.
Extend the CMSModuleLoader partial class.
Create a new class inside CMSModuleLoader that inherits from CMSLoaderAttribute.
Add the attribute defined by the internal class before the definition of the CMSModuleLoader partial class.
Override the Init method inside the attribute class and assign a handler method to the SqlEvents.ExecuteQuery.Before event.
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:




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:

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).
Add a reference to the CMS.Base and CMS.DataEngine namespaces.
Extend the CMSModuleLoader partial class.
Create a new class inside CMSModuleLoader that inherits from CMSLoaderAttribute.
Add the attribute defined by the internal class before the definition of the CMSModuleLoader partial class.
Override the Init method inside the attribute class and assign a handler method to the SqlEvents.ExecuteQuery.After event.
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).




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;
                }
            }
        }
    }
}