Using STUFF to turn multiple rows into comma delimited values for hierarchical data

Documents are stored in tree form in Kentico, we always deal with hierarchical data. For example, a list of post with tags. A nested repeater is always a good way to display parent/children data. However, it requires n+1 requests to the database which is not ideal in terms of performance.

If we just want a simple list from the children documents, STUFF function can be used to construct a sql query and all required data can be retrieved in one request as below:

SELECT     
View_Custom_Parent_Joined.DocumentName, 
STUFF((SELECT ', ' + View_Custom_Children_Joined.[DocumentName]
 FROM View_Custom_Children_Joined
 WHERE
    View_Custom_Children_Joined.NodeParentId =     View_Custom_Parent_Joined.NodeID
 ORDER BY      View_Custom_Children_Joined.NodeOrder FOR XML  PATH(''), TYPE).value('.', 'varchar(max)'),  1, 2, '') AS ChildrenList
FROM
View_Custom_Parent_Joined

Furthermore, we can store the query in a stored procedure or a view to fine tune the performance.

This approach is only for getting simple data from the child documents. Like the example above, it only gets the children document name.We can tweak the STUFF function a bit further. By adding NodeAliasPath and Html tags, we can have a link. However, if you go further, the code becomes hard to maintain.

Passed!

I am CERTIFIED as a Kentico 7 Developer today! :)

Because of the exam, I went through lots of documentations and blog articles. I realised I overlooked many useful features in Kentico. Definitely, I will try on Rest Service, Connector, WebDAV and um .... may be Windows Azure! :)

Transformation?!

Undoubtedly, we would use transformations and CMSRepeater to display document type data under portal engine. How about ASPX template? if we follow developer guide, we would construct a CMSRepeater like below:

<cms:CMSRepeater id="Repeater_Sample" runat="server" path="./%" class="page.menu" transformation="page.menu.title"></cms:CMSRepeater>

However, CMSRepeater inherits from asp.net repeater control. Transformation content can be placed in the Item Template. For example,

<cms:CMSRepeater id="Repeater_Sample" runat="server" path="./%" class="page.menu">
  <ItemTemplate>
    <%#Eval("Title")%>
 </ItemTemplate>
</cms:CMSRepeater>

Which approach would you prefer? I found there are a couple of advantages to use the latter approach.

Save time

If you have a bad internet connection or local/staging hardware, editing transformation may drive you crazy. Using Item Template saves you lots of overheads. It is also easier to implement nested repeater, surely Intellisense would speed up the process. You may also use Header Template, Alternating Item Template, Footer Template and ... as well.

Versioning & Deployment

Kentico version 7 provides versioning on transformation. It is definitely helpful. However, it is not enough. I can use Git and utilise the advance version tools, such as branching, diff editor and etc to manage my codes.

Also, although content staging handles all transformation updates and publish to other Kentico instances easily, it would be hard to deploy only one of many changes under same document type. In this situation, Git branching could handle it.

If using transformation, it takes few extra steps to dump all virtual objects to physical files for compilation under pre-compiled environment.

Advantage of Transformation

Transformation allows you to edit the site directly through the CMS interface. You do not need to upload any files. If your site under pre-compile environment, you can use text‑based transformations. Direct edit still possible.


What do you think? How do you use CMSRepeater?