Pre- and post-processing queries

  Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic! Mail us feedback on this topic!  

You can pre-process database queries and post-process query results using the OnBeforeExecuteQuery and OnAfterExecuteQuery events in the SqlHelperClass.

 

Pre-processing queries

 

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 register the event in the AfterApplicationStart method in ~/App_Code/Global/CMS/CMSApplication.cs. This particular example replaces 'CMS_User' with 'View_CMS_User' in case that the processed query is cms.user.selectall.

 

///

/// Fires after the application start event

///

public static void AfterApplicationStart(object sender, EventArgs e)

{

  // Add your custom actions

   CMS.SettingsProvider.SqlHelperClass.OnBeforeExecuteQuery += new CMS.SettingsProvider.SqlHelperClass.BeforeExecuteQueryEventHandler(BeforeExecuteQuery);

}

 

static void BeforeExecuteQuery(CMS.SettingsProvider.QueryParameters query, CMS.IDataConnectionLibrary.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;

       }

   }

}

 

Post-processing queries

 

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 register the event in the AfterApplicationStart method in ~/App_Code/Global/CMS/CMSApplication.cs. 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.

 

///

/// Fires after the application start event

///

public static void AfterApplicationStart(object sender, EventArgs e)

{

  // Add your custom actions

   CMS.SettingsProvider.SqlHelperClass.OnAfterExecuteQuery += new CMS.SettingsProvider.SqlHelperClass.AfterExecuteQueryEventHandler(AfterExecuteQuery);

}

 

static void AfterExecuteQuery(CMS.SettingsProvider.QueryParameters query, CMS.IDataConnectionLibrary.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;

       }

   }

}

 

Page url: http://devnet.kentico.com/docs/devguide/index.html?pre_and_post_processing_queries.htm