"Web Development","Kentico"
Updating the Same Webpart on Multiple Page Templates in Kentico
This blog post will help guide on what to do when you have a Kentico site that has the same webpart on multiple page templates while needing to make the same change to every webpart. This can be particularly useful when upgrading. 

Before we start I want to state that this blog post is not about whether or not it is best practice in Kentico to have the same webpart on multiple page templates. There certainly are better ways to get certain things to stay the same on multiple page templates such as Inheritance.  

However, there can be many reasons why a Kentico site has the same webpart such as taking over the development from a different vendor, a former colleague’s work, or even your own work before you gained the expertise in Kentico.

In Kentico, there is no built-in way to edit the same webpart on multiple templates other than manually making the changes. If it is just a few page templates, making the changes manually is manageable. But what if there are 50-page templates?  Doing all that work manually would be time-consuming.

This is where SQL Scripts come to the rescue. In Kentico, the webparts of a page template are stored in the PageTemplateWebParts column of the CMS_PageTemplate table, as well as in the VersionXML column of the CMS_ObjectVersionHistory table.

As with any major or potentially breaking change, make sure to backup your database before starting.

Let’s say you want to set the “Where condition” on all built in Kentico Query Repeaters that have the id “queryrepeatername”. Let’s start by writing a query that will get the webpart XML of all page templates with that.  

Now, while the Format is XML, the data is stored as NVARCHAR, so to do XML functions it is necessary to cast the column as XML via:
CAST(PageTemplateWebParts AS XML)
For more information on how to use CAST visit the CAST and CONVERT (Transact-SQL) doc.  

To do an XML function to find templates that contain a webpart with the ID of “queryrepeatername”, you do it with the following function
.value('/page/webpartzone/webpart[@controlid="queryrepeatername"]')
For information on .value() and other XML Methods in SQL see https://docs.microsoft.com/en-us/sql/t-sql/xml/xml-data-type-methods

The following SQL Query returns in XML format all page templates with the control ID “queryrepeatername”.
SELECT 
CAST(PageTemplateWebParts AS XML) as XMLStuff, PageTemplateID
 FROM CMS_PageTemplate
WHERE CAST(PageTemplateWebParts AS XML).value('(/page/webpartzone/webpart[@controlid="queryrepeatername"])[1]', 'varchar(max)') IS NOT NULL
The Select statement casts the results as XML. The where condition checks the first webpart of any page template that contains the ID “queryrepeatername”, as well as the PageTemplate ID of the page templates. More on the page template ID later.

The above queries only show the XML encoding of the Kentico webparts stored for each page template that contains a webpart with the ID of "queryrepeatername". This allows you to see how many page templates contain that webpart with that ID, and allows you to verify by looking through the XML that it does contain the webpart you want to change. That information alone does not update it though.  

To start the process of updating it, you should first insert that output into a temp table.
SELECT 
CAST(PageTemplateWebParts AS XML) as XMLStuff, PageTemplateID
INTO #tmpXML
 FROM CMS_PageTemplate
WHERE CAST(PageTemplateWebParts AS XML).value('(/page/webpartzone/webpart[@controlid="queryrepeatername"])[1]', 'varchar(max)') IS NOT NULL
Now let’s update the where condition of that webpart in the temp table.  In this example, we are setting there where condition to 1=1.
UPDATE
#tmpXML
SET 
XMLStuff.modify('replace value of (/page/webpartzone/webpart[@controlid="queryrepeatername"]/property[@name="wherecondition"]/text())[1] with "1=1"')
Note:  If the value you want to replace that contains Kentico macros, you will need to either go to the Kentico Admin System and resign all macros, or include a valid macro signature as it would appear in the database for that macro. To find the macro signature for that macro you can manually enter it in one of the Kentico webparts on one of the page templates, save, then query the database for it.

For instance, if you want the macro to be something like “NodeID = 1178”, it will look like the following format in the database.
{ % CurrentDocument.NodeID |(user)YOURUSERNAME|(hash)LONGMACROSECURITYHASH% }
After this, you need to actually apply the update on the Kentico Page Template table.
UPDATE PT
SET PageTemplateWebParts = CAST(XMLStuff AS VARCHAR(MAX))
FROM CMS_PageTemplate PT
INNER JOIN #tmpXML tmpXML ON PT.PageTemplateID = tmpXML.PageTemplateID
After which you can drop the temp table.
DROP TABLE #tmpXML
The overall sample code we have for this part is
SELECT Cast(PageTemplateWebParts AS XML) AS XMLStuff, 
       PageTemplateID 
INTO   #tmpXML
FROM   CMS_PageTemplate 
WHERE 
Cast(PageTemplateWebParts AS 
XML).value('(/page/webpartzone/webpart[@controlid="queryrepeatername"])[1]', 
'varchar(max)') IS NOT NULL 

UPDATE #tmpXML
SET 
XMLStuff.modify('replace value of (/page/webpartzone/webpart[@controlid="queryrepeatername"]/property[@name="wherecondition"]/text())[1] with "1=1"') 

UPDATE PT 
SET    PageTemplateWebParts = Cast(XMLStuff AS VARCHAR(max)) 
FROM   CMS_PageTemplate PT
       INNER JOIN #tmpXML tmpXML 
               ON PT.PageTemplateID = tmpXML.PageTemplateID 

DROP TABLE #tmpXML
There is a second part to this. In some cases it might be ok to just update the Page Template table, but if do that and try checking out the Page Template in the Kentico Admin and choose “Undo Checkout” the changes you made in the database will be lost.

That is because Kentico also stores Version History. When you “Undo checkout” Kentico reverts it back to the latest version in the CMS_ObjectVersionHistory table.

Updating the version history is more complicated because Kentico stores Page Template Webparts as XML in the CMS_PageTemplate table, and Kentico also stores the data from the objects in the version CMS_ObjectVersionHistory table as XML. Which means that the Page Template Webpart data is double encoded. So a double cast is needed.  
CAST(CAST(VersionXML AS XML).value('(/NewDataSet/CMS_PageTemplate/PageTemplateWebParts)[1]', 'nvarchar(max)') AS XML)
The following query does a few things. First, it gets in a CTE the VersionObjectID, and the latest Version ID of that object, which in turn get pulls from the Page Template table to get the object ids of the things that need editing.

It then enters the needed data in a temp table. Double casting the XML’s XML.
WITH CTELatestVersionObjectDate 
     AS (SELECT VersionObjectID, 
                Max(VersionID) AS VersionID 
         FROM   CMS_ObjectVersionHistory 
         WHERE  VersionObjectType = 'CMS.PageTemplate' 
                AND VersionObjectID IN (SELECT PageTemplateID 
                                        FROM   CMS_PageTemplate 
                                        WHERE 
Cast(PageTemplateWebParts AS 
XML).value('(/page/webpartzone/webpart[@controlid="queryrepeatername"])[1]', 
'nvarchar(max)') IS NOT NULL) 
 GROUP  BY VersionObjectID) 
SELECT Cast(Cast(VersionXML AS 
            XML).value('(/NewDataSet/CMS_PageTemplate/PageTemplateWebParts)[1]', 
            'nvarchar(max)') AS XML) AS PageTemplateWebPartXML, 
       OH.VersionID 
INTO   #tmpXMLVersionHistory 
FROM   CMS_ObjectVersionHistory OH 
       INNER JOIN CTELatestVersionObjectDate LV 
               ON OH.VersionObjectID = LV.VersionObjectID 
                  AND OH.VersionID = LV.VersionID 
WHERE  VersionObjectType = 'cms.pagetemplate' 
Now update the where condition of the webpart stored in the temp table.
 UPDATE
#tmpXMLVersionHistory
SET 
PageTemplateWebPartXML.modify('replace value of (/page/webpartzone/webpart[@type="BDO.FMASliderQuery"]/property[@name="wherecondition"]/text())[1] with "1=1"')
Before updating the Kentico CMS_ObjectVersionHistory table, it needs to be re-encoded to be an XML’s XML. The following query does that.
SELECT REPLACE((SELECT CAST(PageTemplateWebPartXML AS NVARCHAR(MAX))
 AS [text()] FOR XML PATH('')),  '"', '"') as PageTemplateWebPartXML, VersionID
 INTO #tmpXMLVersionModifiedText
 FROM #tmpXMLVersionHistory
As you might have noticed, the XML function alone was not enough to fully encode it, some quotes got left unencoded, hence the replace function. In your experience, you might need to do other replace functions too as Kentico environments can vary. More on that later.

Now that the where condition has been replaced and encoded, it is time to update the CMS_ObjectVersionHistory table.
UPDATE OH
SET VersionXML = SUBSTRING(VersionXML, 0, (CHARINDEX('<PageTemplateWebParts>', VersionXML) + LEN('<PageTemplateWebParts>')))
+ MT.PageTemplateWebPartXML
+ SUBSTRING(VersionXML, (CHARINDEX('</PageTemplateWebParts>', VersionXML)), LEN(VersionXML))
FROM CMS_ObjectVersionHistory OH
INNER JOIN #tmpXMLVersionModifiedText MT ON OH.VersionID = MT.VersionID 
Now you can drop the temp tables.  
DROP TABLE #tmpXMLVersionHistory
DROP TABLE #tmpXMLVersionModifiedText
One known limitation of this is if the page was already checked out before running the script and you choose “Undo Checkout”. In that case, the changes made from the script will be lost on that page template. If that happens you can just manually update that one-page template.

While this should work and has worked for me, results can vary in different environments. Be sure to do tests after doing this. For instance, make sure the pages don’t error. Check Out random effected page templates to verify the where condition is as expected to verify the part on the Page Template table worked. Then click “Undo Checkout” after checking out the page template to make sure that the changes you made in the script are still there to verify the Version History table.

There might be some that were not affected due to slight differences in the webpart between the template such as a different Control ID, but as long as most templates were changed this saves a lot of time compared to manually making these changes.   

 

Share This Post:

Twitter Pinterest Facebook Google+
Click here to read more Kentico posts
Start a Project with Us

About the author

Ansel is the Hotfixer. Whenever a site needs to be hotfixed or upgraded, he gets it done lightning fast. He always seeks to find the right balance between performance and readability in all the code he writes. Between code writing and hotfixes, he makes the office a more enjoyable place to work by finding new music and desktop wallpapers for everyone to enjoy. Some think he’s crazy, but Bizstream knows him as The Hotfixer!

View other posts by Ansel

Subscribe to Updates

Stay up to date on what BizStream is doing and keep in the loop on the latest with Kentico.