Portal Powered APIs and Feeds

Trevor Fayas / February 14, 2017
Portal Powered APIs and Feeds

Portal Based APIs (JSON, XML, CSV, CAL)

Throughout my Kentico career and the dozens of sites I’ve built, one need seems to always come up: The ability to get information that is IN Kentico, out of Kentico. This takes the form of various things:

  1. API Feeds to fuel AJAX based requests on pages, making them functional without requiring page loading each time.
  2. External Partners requiring access to the data within Kentico
  3. Export of information into a file

All three of these have something in common. They get the data (source + ability to filter), and then format it for consumption. In this article, we're going to cover how to create your own API feeds within Kentico, managed through the Portal interface.

Why Portal API? Why not Coding?

Some may ask “Why would we create an API through the portal, instead of through a Web Service?” My opinion has always been, a CMS is there so you don't have to touch code to manage anything, only touch code to create needed functionality. So if you need a JSON feed of Dealers to run your AJAX based locator, and you create it in a Web Service, what happens when a new piece of information is added to your dealers that you now need in that JSON feed? You would have to go into your web service code, and add that new field, save, possibly push this to the live site, and if anything was in the App_Code folder, cause the site to recycle. What a pain!

But if the API was built and managed in the portal method, you would simply go to your Page Template, make your adjustments, and voila, done. No code, no pushing (except through Kentico's Staging Module), no hassle.

Tools Required

Webpart to API Converter – This tool is available through the Kentico marketplace, and was created by myself specifically for these purposes.

Optionally, JSON / XML / CAL Transformation Methods – These will be outlined in the article. While they are not required, they make generating the JSON, XML, and CAL feeds easier. Please see the article on how to add custom transformation methods to Kentico.

The Trinity of the API

Concept

The concept of how this all works is quite simple. We need 3 elements, the Source, the ability to Filter the Source, and the Display.

We use Kentico's existing tools (Repeaters, Repeaters with Custom Queries, etc) as the source of our information, we use Macros to get the Url Parameters to filter the sources (through Where Conditions, filters, etc), then we use Kentico's Transformation Engine to render the data into the display we want (JSON, XML, Cal, etc).

The last thing is the Webpart to API Converter takes that display and feeds only it back to the response with the proper content type.

Use the Source, Luke!

Puns aside, our first task is to get our Source of data.  Kentico has a plethora of tools to get data out of it's system (the source). Here are some common ones.

Repeaters / Hierarchy Repeaters

Repeaters are the bread and butter of Kentico. If whatever you are trying to export in your feed exists on the Content Tree (Pages), then this is most likely the tool you will be using to create your feed. Hierarchy Repeaters are a bit trickier to configure, but allow you to structure your data in a parent-child relationship (since both JSON and XML are capable of holding Parent-Child relationships).

Repeater with Custom Query / Universal Viewer with Custom Query

Since everything that exists in Kentico is stored in the database, you can essentially get any piece of data you want just by writing the correct Query. Using the Universal Viewer with Custom Query, you can even assign Parent-Child relationships and make Hierarchy Repeaters of Custom Query Data.

Filtering Repeaters

Repeaters can be filtered through a variety of means, but most of them consist of using Macros to get the filter parameters (from the URL), and then filtering the source through Where Conditions, Filters, and Fields.

Macro Syntax

First we should discuss the means of getting information.  Kentico's Macros allow you to have access to a wide variety of resources, but the primary one we'll be discussing is the QueryString.

 

// Shortcut, don't use if Url Parameter shares the same Property name
 

// Full Command, note the @%} is used because there is no need to sign your macro for QueryStrings

 

Where Conditions

For simple filtering, sometimes adding Macros to the "WHERE" condition in your repeater is all that's needed.  Below is an example of how to get a Year parameter from the URL and get only Blog Posts for that year.

 

DATEPART(year, BlogPostDate) = 1900 OR 1900 = 1900

 

A couple notes on the above:

  1. I utilized the "Convert" methods to ensure that the value passed through the QueryString was of a certain type.  If i didn't, then someone could pass jibberish and cause a SQL error.
  2. I made the year optional, because if you did not pass a year at all then the query would still be true (since the last portion would resolve to 1900 = 1900 which is true).
Also should be noted, by default any Url Parameter passed into a WHERE condition automatically has it's single quotes escaped, this is to prevent SQL injection attachs.  This can be disabled, but then you must be very careful to properly escape the quotes yourself!

Custom Filters / Filter Web Partners

You can attach Custom Filters to your repeaters in order excercise more control over your API filtering. All Filter web parts in the end render a Where Condition that is appended to the repeater's query.  This does require touching code to create, but that's okay since it doesn't require touching code to maintain it once it's built.  You shoudl use Custom Filters if the filters you want to provide the API numerous and complex.

Custom Data Source Fields

If you need even more flexibility and you build a Custom Data Source (and attach a basic repeater to it).  The benefit of this is a Custom Data Source is pretty much a Source and Filter combined into one.  You can create and assign your own fields for you to pass URL Parameter macros to, and control how they alter your source.

These are also great if you need to incorporate other sources of data, such as from files or external services (things that Kentico doesn't have built in Data Sources for).

Final Notes and Resources

I hope this guide helps! Below are a handful of helpful Custom Transformations that you can add to the default Kentico Transformations (ASCX). Additionally if you are using Text/XML, you can create Custom Macro Methods using this logic.

 

using System;
using CMS.SiteProvider;
using CMS.Helpers;
using CMS.DataEngine;
using CMS.EventLog;
using CMS.Localization;
using CMS.Membership;
using CMS.DocumentEngine;
using System.Web;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using CMS.Taxonomy;
using System.Threading;
using CMS.DocumentEngine;
namespace CMS.Controls
{
    /// <summary>
    /// Extends the CMSTransformation partial class.
    /// </summary>
    public partial class CMSTransformation
    {

        #region "HTML/XML"

        /// <summary>
        /// Removes HTML Comments from the string.
        /// </summary>
        /// <param name="input">The Input</param>
        /// <returns>The Output, minus any commented content</returns>
        public string RemoveHTMLComments(string input)
        {
            string output = string.Empty;
            string[] temp = System.Text.RegularExpressions.Regex.Split(input, "<!--");
            foreach (string s in temp)
            {
                string str = string.Empty;
                if (!s.Contains("-->"))
                {
                    str = s;
                }
                else
                {
                    str = s.Substring(s.IndexOf("-->") + 3);
                }
                if (str.Trim() != string.Empty)
                {
                    output = output + str.Trim();
                }
            }
            return output;
        }

        #endregion


        #region "JSON"

        /// <summary>
        /// Escapes anything needed to be escaped
        /// </summary>
        /// <param name="theString">The string to be encoded</param>
        /// <returns>The JSON encoded string, no quotes around it.</returns>
        public string CleanForJSON(string theString)
        {
            if (string.IsNullOrWhiteSpace(theString))
            {
                return "";
            }
            // May need to import System.Web.Helpers, often in C:\Program Files (x86)\Microsoft ASP.Net under MVC or WebPages folder then Assemblies. if you can't do that then you'll have to escape the quotes yourself.
            string quoted = System.Web.Helpers.Json.Encode(HTMLEncode(RemoveHTMLComments(theString)));
            return quoted.Substring(1, quoted.Length - 2);
        }

        #endregion


        #region "Multi-Culture"

        /// <summary>
        /// Gets all the culture variations in a json array of the given NodeID and column name
        /// </summary>
        /// <param name="NodeID">The Node ID</param>
        /// <param name="ColumnName">The column where the text is</param>
        /// <param name="NoArrayIfSingle">If true and only 1 culture exists, does not return an array object but just the "text", if false will return an array even if only 1 item. default true.</param>
        /// <param name="IncludeCultures">Optional delimited string of which cultures to include</param>
        /// <param name="ExcludeCulture">Optional delimited string of which cultures to exclude </param>
        /// <returns>The proper JSON string content</returns>
        public string GetAllCultureTextXml(int NodeID, string ColumnName, bool NoArrayIfSingle = true, string IncludeCultures = "", string ExcludeCulture = "")
        {
            Dictionary<string, string> CultureToText = GetNodeCultureToCultureText(NodeID, ColumnName, IncludeCultures, ExcludeCulture);

            if (CultureToText.Keys.Count == 1 && NoArrayIfSingle)
            {
                return "<![CDATA[" + RemoveHTMLComments(CultureToText[CultureToText.Keys.First()]) + "]]>";
            }
            else
            {
                return ConvertCultureTextToXmlJson(CultureToText, "xml");
            }
        }

        /// <summary>
        /// Gets all the culture variations in an xml array of the given NodeID and column name
        /// </summary>
        /// <param name="NodeID">The Node ID</param>
        /// <param name="ColumnName">The column where the text is</param>
        /// <param name="NoArrayIfSingle">If true and only 1 culture exists, does not return an xml object but just the text, if false will return an array even if only 1 item. default true.</param>
        /// <param name="IncludeCultures">Optional delimited string of which cultures to include</param>
        /// <param name="ExcludeCulture">Optional delimited string of which cultures to exclude </param>
        /// <returns>The proper xml string content</returns>
        public string GetAllCultureTextJSON(int NodeID, string ColumnName, bool NoArrayIfSingle = true, string IncludeCultures = "", string ExcludeCulture = "")
        {
            Dictionary<string, string> CultureToText = GetNodeCultureToCultureText(NodeID, ColumnName, IncludeCultures, ExcludeCulture);

            if (CultureToText.Keys.Count == 1 && NoArrayIfSingle)
            {
                return "\"" + CleanForJSON(CultureToText[CultureToText.Keys.First()]) + "\"";
            }
            else
            {
                return ConvertCultureTextToXmlJson(CultureToText, "json");
            }
        }


        /// <summary>
        /// Given the NodeID and column name, creates a Dictionary of the Culture and the Text in that column
        /// </summary>
        /// <param name="NodeID">The NodeID</param>
        /// <param name="ColumnName">The Column Name contianing the text</param>
        /// <returns>A Dictionary of the CultureCode and it's corresponding text</returns>
        private Dictionary<string, string> GetNodeCultureToCultureText(int NodeID, string ColumnName, string IncludeCultures = "", string ExcludeCulture = "")
        {
            Dictionary<string, string> CultureToText = new Dictionary<string, string>();
            List<string> IncludeCulturesList = new List<string>(IncludeCultures.ToLower().Split("|;,".ToCharArray(), StringSplitOptions.RemoveEmptyEntries));
            List<string> ExcludeCulturesList = new List<string>(ExcludeCulture.ToLower().Split("|;,".ToCharArray(), StringSplitOptions.RemoveEmptyEntries));
            foreach (TreeNode cultureVersion in TreeHelper.SelectSingleNode(NodeID).CultureVersions)
            {
                // Check include / exclude
                if ((IncludeCulturesList.Count > 0 && !IncludeCulturesList.Contains(cultureVersion.DocumentCulture.ToLower())) || (ExcludeCulturesList.Count > 0 && ExcludeCulturesList.Contains(cultureVersion.DocumentCulture.ToLower())))
                {
                    continue;
                }
                if (!CultureToText.ContainsKey(cultureVersion.DocumentCulture))
                {
                    CultureToText.Add(cultureVersion.DocumentCulture, cultureVersion.GetValue(ColumnName, ""));
                }
            }
            return CultureToText;
        }

        /// <summary>
        /// Converts a Dictionary of Culture to Text into XML or JSON
        /// </summary>
        /// <param name="CultureToText">Dictionary with the Culture Code as the key and the Text as the value</param>
        /// <param name="type">xml or json</param>
        /// <returns>the xml or Json object</returns>
        private string ConvertCultureTextToXmlJson(Dictionary<string, string> CultureToText, string type)
        {
            bool isXml = (type.ToLower() == "xml");
            string Results = "";
            Results += (isXml ? "" : "[");
            foreach (string cultureKey in CultureToText.Keys)
            {
                if (isXml)
                {
                    Results += string.Format("<Text culture=\"{0}\"><![CDATA[{1}]]></Text>", cultureKey, CultureToText[cultureKey]);
                }
                else
                {
                    Results += "{\"culture\":\"" + cultureKey + "\",\"text\":\"" + CleanForJSON(CultureToText[cultureKey]) + "\"},";
                }
            }
            if (!isXml)
            {
                Results = Results.Trim(" ,".ToCharArray());
            }
            Results += (isXml ? "" : "]");
            return Results;
        }

        #endregion

        #region "List handling"

        /// <summary>
        /// Converts the Values into an XML array of the object values
        /// </summary>
        /// <param name="Values">The delimited list of values</param>
        /// <param name="IsCategory">If these are category names</param>
        /// <param name="ElementName">The Containing Element Name for each object</param>
        /// <param name="Seperators">The seperator values, default is |;,</param>
        /// <param name="Columns">The Columns for Categories that are retrieved and rendered.</param>
        /// <returns>The XML of the object values</returns>
        public static string ListToXml(object Values, bool IsCategory, string ElementName, string Seperators = "|;,", string Columns = "CategoryName, CategoryDisplayName, TypeCode")
        {
            return ListToXml(ValidationHelper.GetString(Values, ""), IsCategory, ElementName, Seperators, Columns);
        }

        public static string ListToXml(string Values, bool IsCategory, string ElementName, string Seperators = "|;,", string Columns = "CategoryName, CategoryDisplayName, TypeCode")
        {
            return ListToXmlOrJson(ValidationHelper.GetString(Values, ""), "xml", IsCategory, ElementName, Seperators, Columns);
        }

        /// <summary>
        /// Converts the Values into a Json array of the object values
        /// </summary>
        /// <param name="Values">The delimited list of values.</param>
        /// <param name="IsCategory">If these are category names, this will do a lookup on the category and include additional Columns</param>
        /// <param name="Seperators">The seperator values, default is |;,</param>
        /// <param name="Columns">The Columns for Categories that are retrieved and rendered.</param>
        /// <returns>The JSON array object of the values.</returns>
        public static string ListToJson(object Values, bool IsCategory, string Seperators = "|;,", string Columns = "CategoryName, CategoryDisplayName, TypeCode", bool ArrayIfSingle = true)
        {
            return ListToJson(ValidationHelper.GetString(Values, ""), IsCategory, Seperators, Columns, ArrayIfSingle);
        }

        public static string ListToJson(string Values, bool IsCategory, string Seperators = "|;,", string Columns = "CategoryName, CategoryDisplayName, TypeCode", bool ArrayIfSingle = true)
        {
            return ListToXmlOrJson(ValidationHelper.GetString(Values, ""), "json", IsCategory, "", Seperators, Columns, ArrayIfSingle);
        }

        /// <summary>
        /// Converts a List of values into XMl or JSON
        /// </summary>
        /// <param name="Values">The values (seperated by some value)</param>
        /// <param name="Type">The Type, xml or json</param>
        /// <param name="IsCategory">If this is a category or not, if it's a category it will render category information</param>
        /// <param name="ElementName">The container element name, only applicable for XML</param>
        /// <param name="Seperators">The seperators, default is |;,</param>
        /// <param name="Columns">The columns to grab from the Manitowoc Category View to be rendered in the results.</param>
        /// <returns>The Xml or JSON content</returns>
        private static string ListToXmlOrJson(string Values, string Type, bool IsCategory, string ElementName = "", string Seperators = "|;,", string Columns = "CategoryName, CategoryDisplayName, TypeCode", bool ArrayIfSingle = true)
        {
            List<string> Items = new List<string>(Values.Split(Seperators.ToCharArray(), StringSplitOptions.RemoveEmptyEntries));
            bool isXml = Type == "xml";

            string Results = "";

            if (IsCategory)
            {
                Results += (isXml ? "" : (!ArrayIfSingle && Items.Count == 1 ? "" : "["));
                Items.ForEach(x => x = SqlHelper.EscapeQuotes(x));
                int totalRecords = 0;
                foreach (DataRow catInfoRow in QueryInfoProvider.ExecuteQuery("cms.category.selectall", null, string.Format("CategoryName in ('{0}')", string.Join("','", Items.ToArray())), "CategoryName", 1, Columns, -1, -1, ref totalRecords).Tables[0].Rows)
                {
                    Results += (isXml ? "<" + ElementName + ">" : "{");
                    foreach (DataColumn catInfoCol in catInfoRow.Table.Columns)
                    {
                        if (catInfoCol.ColumnName.ToLower() == "categorydisplayname" && catInfoRow[catInfoCol.ColumnName].ToString().Contains(""))
                        {
                            // Put sub elements to list out the display names by localization
                            string displayNameCode = catInfoRow[catInfoCol.ColumnName].ToString().Replace("{$", "").Replace("", "").Trim();

                            var resourceStringObj = ResourceStringInfoProvider.GetResourceStringInfo(displayNameCode);
                            if (resourceStringObj != null)
                            {
                                Results += (isXml ? "<CategoryDisplayName><Translations>" : "\"CategoryDisplayName\":[");
                                foreach (ResourceTranslationInfo translationObj in ResourceTranslationInfoProvider.GetResourceTranslations().WhereEquals("TranslationStringID", resourceStringObj.StringID))
                                {
                                    if (isXml)
                                    {
                                        Results += string.Format("<Text culture=\"{0}\"><![CDATA[{1}]]></Text>", CultureInfoProvider.GetCultureInfo(translationObj.TranslationCultureID).CultureCode, translationObj.TranslationText);
                                    }
                                    else
                                    {
                                        Results += "{\"culture\":\"" + CultureInfoProvider.GetCultureInfo(translationObj.TranslationCultureID).CultureCode + "\",\"text\":\"" + HTMLHelper.HTMLEncode(translationObj.TranslationText.Replace("\"", "\\\"")) + "\"},";
                                    }
                                }

                                Results = (isXml ? Results + "</Translations></CategoryDisplayName>" : Results.Trim(",".ToCharArray()) + "],");
                            }
                        }
                        else if (catInfoCol.ColumnName.ToLower() == "categorydescription")
                        {
                            Results += string.Format((isXml ? "<{0}><![CDATA[{1}]]></{0}>" : "\"{0}\":\"{1}\","), catInfoCol.ColumnName, catInfoRow[catInfoCol.ColumnName]);
                        }
                        else
                        {
                            Results += string.Format((isXml ? "<{0}>{1}</{0}>" : "\"{0}\":\"{1}\","), catInfoCol.ColumnName, HTMLHelper.HTMLEncode(ValidationHelper.GetString(catInfoRow[catInfoCol.ColumnName], "")));
                        }
                    }
                    Results = (isXml ? Results + "</" + ElementName + ">" : Results.Trim(",".ToCharArray()) + "},");
                }
                Results = (isXml ? Results : Results.Trim(",".ToCharArray()) + (!ArrayIfSingle && Items.Count == 1 ? "" : "]"));
            }
            else
            {
                Results += (isXml ? "" : (!ArrayIfSingle && Items.Count == 1 ? "" : "["));
                foreach (string Item in Items)
                {
                    Results += string.Format((isXml ? "<{0}>{1}</{0}>" : "\"{1}\""), ElementName, Item);
                    Results = (isXml ? Results : Results.Trim(",".ToCharArray()) + ",");
                }
                Results = (isXml ? Results : Results.Trim(",".ToCharArray()) + (!ArrayIfSingle && Items.Count == 1 ? "" : "]"));
            }

            return Results;
        }

        #endregion

    }

}

 

Comment

Trevor Fayas
About the Author

Trevor Fayar
Software Engineer II

Trevor Fayas is a Software Engineer II at Heartland Business Systems, and one of the lead Kentico Developers on staff.  He is ranked in the top 10 Kentico Developer Network Q&A Contributors, and has published multiple tools on the Kentico Marketplace.  Trevor’s passion is to help build and equip Kentico users and developers with the tools to really take their site to the next level.
 
Comments
Blog post currently doesn't have any comments.