As DotNetNuke becomes a more established CMS, DotNetNuke web designers will find that clients are requesting more often to re-skin or carry out maintenance tasks on already established DotNetNuke websites. These maintenance tasks and re-skinning jobs can be very time consuming. I would like to walk you through a typical scenario that I come across with my clients and provide you with some power tips for literally reducing weeks and months worth of work into seconds.
These examples are not just limited to DotNetNuke, these examples can apply to any CMS and you may find the techniques outlined here useful for managing other websites.
The Client
Let’s take a typical example: two years ago you created a DotNetNuke website for a client, the site used a table based skin and once you created the site the client then maintained the site adding content. The site now consists of 500 pages.
The client now wants to update the skin design for a new look, and they also wish to take advantage of a pure CSS skin for benefits such as reduced page size, search engine optimized pages, and accessibility.
Let’s go through some of the typical problems and lengthy administration tasks we would typically have to carry out.
Reduce weeks of administration work into seconds!
Typical problems
Junk HTML
When adding content into the Text/HTML modules, the client has formatted all of the content. So, the source code is full of junk HTML, ie.
Example Text
Listing 1: Typical junk HTML code
Task: We need to strip out all elements of junk HTML code in order for the new CSS skin to display correctly.
Typical time length: 20 minutes per page to manually strip out the code = 10,000 minutes.
Modules Displayed on all Pages
Currently a Links module is set to display on all pages. The Links module contains links to key areas of the website. Instead of using this Links module we can embed a menu skin object within our new CSS skin which will enable those links to be more easily maintained. The problem is that if you delete the Links module from one of the pages, it will only be deleted from that single page and will remain on the other 499 pages.
Task: We need to delete a module from 500 pages.
Typical time length: 3 minutes per page = 1500 minutes.
Shadow/Copied Modules
There are several instances of modules copied to various pages within the website. The containers for these individual copied modules will all need updating.
Task: We need to find out which pages the copied modules are located on and change the container for the copied modules on those pages. How do you find the pages with the copied modules?
Typical time length: 3 minutes per page = 1500 minutes.
Change the skin on individual pages
Typically on a DotNetNuke installation you would set the skin for all pages in the admin menu. For some pages however you will specify a different skin to use for an individual page.
Task: We need to identify which pages have a skin specified, go to the page settings for each of those pages and select the new CSS skin to use for the page. How do you find the pages that use a different skin?
Typical Time Length: 3 minutes per page = 1500 minutes.
Moving modules to new content panes
The old skin has a content pane named ‘Top Right’; the client wants to remove this pane and move those modules to the ‘Right’ pane.
Task: Visit each page, using the module settings move each page to the right pane.
Typical Time Length: 5 minutes per page = 2500 minutes.
17000 minutes of administration time
You should now get an idea of the typical tasks we are faced with and some of the major problems of time. So far, without creating the skin, just administration time of manually logging into DotNetNuke and making changes to the pages, modules, skins and containers, time is going to be around 17,000 minutes or 283 hours or 7 weeks of work.
This is clearly not an option.
Time to panic?
The first time I came across this problem, I spent a few days manually administrating the DotNetNuke portal when I suddenly realized that to complete the volume of work for this particular client it was going to take around three months of administration time, with a month deadline for the completion of the site I began to think that there must be ways of automating these repetitive tasks and speeding up the administration process.
First Steps
My first source of inspiration came from Kevin Schreiner of R2i where Kevin published a tip for fixing broken links when moving a site from a localhost installation to a live site and vice versa. The tip outlines how to use a Find and Replace SQL procedure to search for links within the Text/HTML modules and fix them to the correct link, further information can be read here:
- http://www.bi4ce.com/tabid/106/forumid/2/postid/86/view/topic/Default.aspx
It occurred to me that I could take advantage of this Find and Replace procedure to find junk HTML code in the Text/HTML modules and strip it out. All I needed to do was to identify the junk HTML.
Identify a Text/HTML module
We need to identify the ID of a Text/HTML module and view the data that is stored for this module directly within the DotNetNuke database tables. As an example, visit a page which contains junk HTML in the Text/HTML module. To identify the ID of the Text/HTML module on the page, go to the modules settings and view the URL for the settings page, a typical example is: http://www.awebsite.com/tabid/403/ctl/Edit/mid/346/Default.aspx
Here we can identify the ModuleID = 346 and the TabID = 403 (ID of the page)
(NOTE: In DotNetNuke the terms ‘Page’ and ‘Tab’ are interchangeable.)
Fig. 1: Module Settings Menu
View a Text/HTML module in the database
View your DotNetNuke database using Microsoft SQL Server, locate and view the content from the HtmlText table. Within this table you can view the data stored for every single Text/HTML module in your DotNetNuke installation. If we need to find the data for the Text/HTML module with the ModuleID of 346, we can do this with a simple query:
SELECT * FROM HtmlText
WHERE ModuleID = 346
Listing 2: Viewing the data of the Text / HTML module
In the results, the DesktopHtml column holds the text data for the module. We now need to view this data, identify the junk HTML and apply it to the Find and Replace query.
Fig. 2: Viewing the data for the Text / HTML module with the ID 346
Find and Replace
Over the period of 2 years, the client has used several text editor versions, such as the Free Text Box and FCK Editor. Because of this I discovered that the text editors had stored the junk HTML in different ways, so I had to analyse a lot of different modules content to create a definitive list of junk HTML code. For this client I found 80 different variations of HTML code that I needed to Find and Replace.
In listing 3 are some examples of the HTML code I removed using Kevin Schreiner’s Find and Replace procedure:
EXEC Replace_DesktopHtml
'<font size="1">',''
EXEC Replace_DesktopHtml
'</font>',''
EXEC Replace_DesktopHtml
'<BR>','<br />'
EXEC Replace_DesktopHtml
'<span style="FONT-FAMILY:
verdana;">',''
Listing 3: Example Find and Replace HTML to remove
If we analyze the first line from listing 3:
<font size="1">
This represents:
When we execute the Find and Replace procedure, the content inside the first set of apostrophes is the HTML content to find and the content inside the second set of apostrophes is the content to replace it with.
Therefore, in the first line from listing 3 we are finding the HTML content, but there isn’t any content inside the second set of apostrophes, so that HTML content will be deleted.
If we look at line 3 from listing 3:
'<BR>','<br />'
This represents:
Find:
and Replace with
Execute the Find and Replace query
Once I had identified the HTML code to Find and Replace I was able to quickly remove it by executing the Find and Replace procedure. What was a 10000 minute job was now reduced into a few seconds. Of course, checks need to be made to the pages to ensure the correct HTML has been removed and has not caused display problems.
I backed up my database at every stage when executing these queries and tested extensively.
Step Two
Having executed these SQL queries to speed up administration time removing junk HTML code, it occurred to me that I could use SQL to automate all of the remaining repetitive tasks.
Let’s go through some quick examples.
Pages, Modules, Skins and Containers
If you look through the DotNetNuke installation tables, the two important tables are:
- Tabs: This holds the data for each page’s settings, ie. Skin and container settings, title, description and keywords.
- TabModules: This holds the data for which module is displayed on each Tab (page).
Delete a module from all pages
So, the next problem was to delete the Links module which was set to display on all pages. This is now simple using SQL. First of all identify the ID of the Links module: go to the Links module settings page (view figure 1 for an example), eg:
- http://www.awebsite.com /tabid/36/ctl/Module/ModuleId/351/Default.aspx
We can now construct a SELECT query to check we are going to delete the correct data:
SELECT * FROM TabModules
WHERE ModuleID = 351
Listing 4: Selecting all copied instances of a Links module
Fig. 3: Viewing all instances of the Links module, in the TabID column you can see these modules are displayed on separate pages
This will display in the results every single instance of the Links module, we now know we are targeting the correct data, so we can change this to a DELETE query.
DELETE TabModules
WHERE ModuleID = 351
Listing 5: Deleting all copied instances of a Links module
This would have taken around 1500 minutes to delete this module manually from every single page, we have now deleted the module in seconds.
SQL is a Lifesaver!
From those examples you can see how SQL can reduce DotNetNuke administration time from weeks of work to seconds. All you need is a small amount of SQL knowledge and basic queries can solve many problems.
The technique I tend to use is to create a test page with modules and then analyze the data for the test page in the DotNetNuke database. From there I can create test SQL queries to manipulate the test page and once I am satisfied with how it works I then apply the SQL queries to the entire DotNetNuke installation.
From a little inspiration I was able to think outside the box and move away from manually administrating my DotNetNuke portals. So, next time you find yourself administrating a repetitive task, think to yourself, could I speed this up with a SQL query? 99% of the time I think you will be pleasantly surprised.