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?

How to edit "System Tables" in Kentico Site Manager

Kentico provides a good solution to manage system table schemas, views and stored procedures. There is an interface under the Development tab in the System tables section. I always add custom fields to the User tables through this interface to collect more member information.

Unfortunately, it seems not all system tables are on the list. if you cannot find the system table on the list, there is a little trick you can do. Go to database and open the cms_class table. Find the class of the table you want to show and then update the field ClassShowAsSystemTable to 1. I take DiscountCoupon table as an example

UPDATE CMS_Class
SET ClassShowAsSystemTable = 1
WHERE ClassName = 'ecommerce.discountcoupon'

Now, you can find Ecommerce - Discount coupon table is on the list now. Bravo!!

Usually, you can find the corresponding provider class to set or get the new field data in the API. For example,
DiscountCouponInfoProvider.Get("fieldname");
DiscountCouponInfoProvider.Set("fieldname", "fieldvalue");
UserInfoProvider.Get("fieldname");
UserInfoProvider.Set("fieldname", "fieldvalue")

Once you add or update fields in system tables, you can publish the changes to other Kentico instances through content staging. This makes deployment much easier! Unfortunately, content staging does not support views and stored procedures yet. Hopefully, they would be supported in the future! :)

SQL Script to rename a document type table

My team came across with a situation and required to change a document type table in database. Apparently, You can change document type namespace, But it would not rename the table. Finally, my team came up with below script.

DECLARE @OldTableName VARCHAR (MAX) 

DECLARE @NewTableName VARCHAR (MAX)DECLARE @ClassID INT 

SET @OldTableName = 'OLD_TableName'SET @NewTableName = 'New_TableName'

SELECT @ClassID= ClassID   FROM dbo .CMS_Class WHERE ClassTableName = @OldTableName

UPDATE dbo. CMS_Class SET 

ClassTableName = REPLACE(ClassTableName , 'Old_' ,'New_' ), 

ClassDisplayName = REPLACE(ClassDisplayName , 'Old ' ,'New ' ), 

ClassXmlSchema = REPLACE(ClassXmlSchema , 'Old_' ,'New_' ), 

ClassName = REPLACE(ClassName , 'Old.' ,'New.' ) 

WHERE ClassID = @ClassID 

EXEC sp_rename @OldTablename, @NewTableName

It is NOT done yet. You need to go back to that document type and save it. This would trigger Kentico to regenerate the view for the document type with the new namespace. You may also remove the old view.

We ran this script on Kentico v7 instance and did not test on other versions. Please make sure you back up database before running the script.

-----------

Updated: I've tried the script on Kentico v8. It works fine.