|
|
Pre- and post-processing queries |
|
|
|
|
|
Pre- and post-processing queries |
|
|
|
|
|
|
Pre- and post-processing queries |
|
|
|
|
|
Pre- and post-processing queries |
|
|
|
|
|
|
||
You can pre-process database queries and post-process query results using the OnBeforeExecuteQuery and OnAfterExecuteQuery events in the SqlHelperClass.
The OnBeforeExecuteQuery event is executed before any query is executed. Using it, you can influence the behavior of the query and its code on the fly.
The code below is the delegate definition for the event:
/// <summary> /// Query execution event handler /// </summary> /// <param name="query">Executed query</param> /// <param name="conn">Connection</param> public delegate void BeforeExecuteQueryEventHandler(QueryParameters query, IDataConnection conn); |
And this code example shows how you can use the event. You need to create a new module class in App_Code (or Old_App_Code if you installed the project as a web application), e.g. ~\App_Code\Custom\PreProcessingQueriesModule.cs, which will extend the CMSModuleLoader partial class. The class should contain the method to be executed on the event and its registration into the event in the Init() method, which is executed on each application start.
The method in this particular example replaces CMS_User with View_CMS_User in case that the processed query is cms.user.selectall.
using CMS.SettingsProvider;
[PrePocessingQueriesLoaderModule] public partial class CMSModuleLoader { /// <summary> /// Module registration /// </summary> private class PrePocessingQueriesLoaderModuleAttribute : CMSLoaderAttribute { /// <summary> /// Initializes the module /// </summary> public override void Init() { SqlHelperClass.OnBeforeExecuteQuery += new SqlHelperClass.BeforeExecuteQueryEventHandler(BeforeExecuteQuery); }
static void BeforeExecuteQuery(CMS.SettingsProvider.QueryParameters query, CMS.SettingsProvider.IDataConnection conn) { if (query.Name != null) { switch (query.Name.ToLower()) { case "cms.user.selectall": query.Text = query.Text.Replace("CMS_User", "View_CMS_User"); break; } } } } } |
The OnAfterExecuteQuery event is raised after any query is executed and you can use it to modify the result of the query. It can handle only a DataSet, thereby only calls using the ExecuteQuery method can be post-processed this way.
The code below is the delegate definition for this event:
/// <summary> /// Query execution event handler /// </summary> /// <param name="query">Executed query</param> /// <param name="conn">Connection</param> public delegate void BeforeExecuteQueryEventHandler(QueryParameters query, IDataConnection conn); |
And this code example shows how you can use the event. You need to create a new module class in App_Code (or Old_App_Code if you installed the project as a web application), e.g. ~\App_Code\Custom\PreProcessingQueriesModule.cs, which will extend the CMSModuleLoader partial class. The class should contain the method to be executed on the event and its registration into the event in the Init() method, which is executed on each application start.
The method in this particular example basically gives dynamically generated full name instead of the one that is set in the user settings. This will not be reflected in the UI as you are only processing the result of the query, so please take this just as an example of how you can use the event.
using System.Data;
using CMS.SettingsProvider;
[PostPocessingQueriesModuleLoader] public partial class CMSModuleLoader { /// <summary> /// Module registration /// </summary> private class PostPocessingQueriesModuleLoaderAttribute : CMSLoaderAttribute { /// <summary> /// Initializes the module /// </summary> public override void Init() { SqlHelperClass.OnAfterExecuteQuery += new SqlHelperClass.AfterExecuteQueryEventHandler(AfterExecuteQuery); }
static void AfterExecuteQuery(QueryParameters query, IDataConnection conn, ref DataSet result) { if (query.Name != null) { switch (query.Name.ToLower()) { case "cms.user.selectall": if (result != null) { DataTable dt = result.Tables[0]; foreach (DataRow dr in dt.Rows) { dr["FullName"] = dr["FirstName"] + " " + dr["MiddleName"] + " " + dr["LastName"]; } } break; } } } } } |