If (you are a dev in hurry with a TL waiting for your output)
Then simply skip to script at bottom of this page, edit variables in first three lines as per your requirement and test results.
else
Keep Reading :)
Is it difficult to hold a glass of water for a minute! No, not at all. But how will it be if you are supposed to hold it for 100 hrs :) This post is about tackling one such case.
Problem statement:
Copy modules as reference from one DNN page to another using SQL script.
One of my friend who administers multiple DNN portals, was having around 7-8 html modules on a page and was copying them across some 50 odd existing pages I guess. Copying modules using dnn UI is pretty simple as
described here, but is very slow as you have to do it one by one and every-time it performs a certain set of steps. So he asked me to devise some alternative and i came-up with below.
Solution:
DNN keeps track of modules and their instances in below tables:
- dbo.DesktopModules and dbo.ModuleDefinitions - These tables track what all modules are installed in site.
- dbo.Modules - This table tracks instances of modules added on pages, means an entry is created in this table when you add a new instance of a module on some page
Apart from above three tables, there is another table named dbo.TabModules which keeps track of info that which module is placed on which tab(In dnn teminology, a tab represents a page). This table gets one entry for every instance of module whether it is reference copy of an existing module or added a fresh.
So after knowing above bit of information, I coded a small script which takes three parameters as below
name of source page : from where to copy
name of target page : where to paste
list of module titles : that need to be copied from source page to target page
With these parameters my script simply creates an entry in dbo.TabModules table and updates VersionGuid column for related modules for every entry of module title.
* name of a DNN page can be retrieved through page settings
** module title can be duplicate. Though script i wrote is smart enough to point you on such cases but I still advise to choose unique module titles while copying modules
*** I have also used a function "
udf_List2Table" to get table from pipe delimited list of module. This function can be found here at
my favorite SQL blog.
**** This script creates copy of existing modules, means changing content in one will automatically update that in other.
-------------------Script goes
below------------------------
DECLARE @CopyFromPage VARCHAR(1000) = 'Test Page' --U can get this from page settings
DECLARE @CopyToPage VARCHAR(1000) = 'Child Page 5'
DECLARE @OriginalModuleTitles
VARCHAR(MAX) = 'ModuleA||ModuleB||ModuleC||ModuleD||ModuleE' --Hoping u have added only one module with this title on
from page
DECLARE @OriginalModuleTitle
VARCHAR(1000)
DECLARE @TabIdFromPage INT
DECLARE @TabIdToPage INT
DECLARE @ModuleId INT
DECLARE @HostUserId INT
SELECT @TabIdFromPage = TabID FROM dbo.Tabs WHERE TabName = @CopyFromPage
SELECT @TabIdToPage = TabID FROM dbo.Tabs WHERE TabName = @CopyToPage
IF(@TabIdFromPage > 0 AND @TabIdToPage > 0)
BEGIN
--Convert pipe delimited module names to table
DECLARE @tempTable TABLE (moduleName varchar(1000))
INSERT INTO @tempTable
SELECT * FROM udf_List2Table(@OriginalModuleTitles, '||')
WHILE EXISTS (SELECT * FROM @tempTable)
BEGIN
SELECT TOP 1 @OriginalModuleTitle =
moduleName FROM
@tempTable
IF EXISTS (SELECT * FROM dbo.TabModules WHERE TabID = @TabIdToPage AND ModuleTitle = @OriginalModuleTitle)
PRINT 'Module with title '''
+ @OriginalModuleTitle
+ ''' already exists in
page ''' + @CopyToPage
+ ''''
ELSE
BEGIN
IF ((SELECT COUNT(*) FROM dbo.TabModules WHERE TabID = @TabIdFromPage AND ModuleTitle = @OriginalModuleTitle)
= 1)
BEGIN
SELECT @ModuleId = ModuleID FROM dbo.TabModules WHERE TabID = @TabIdFromPage AND ModuleTitle = @OriginalModuleTitle
SELECT @HostUserId = UserID FROM dbo.Users WHERE Username = 'host'
DECLARE @NewVersionGuid
UNIQUEIDENTIFIER =
NEWID()
DECLARE @CreatedOnDate
DATETIME = GETDATE()
INSERT INTO [dbo].[TabModules]
([TabID], [ModuleID], [PaneName], [ModuleOrder], [CacheTime], [Alignment], [Color], [Border], [IconFile],
[Visibility], [ContainerSrc], [DisplayTitle], [DisplayPrint], [DisplaySyndicate], [IsWebSlice], [WebSliceTitle],
[WebSliceExpiryDate],
[WebSliceTTL], [CreatedByUserID], [CreatedOnDate], [LastModifiedByUserID],
[LastModifiedOnDate],
[IsDeleted], [CacheMethod], [ModuleTitle], [Header], [Footer], [CultureCode],
[UniqueId], [VersionGuid], [DefaultLanguageGuid],
[LocalizedVersionGuid])
SELECT
@TabIdToPage, [ModuleID], [PaneName], [ModuleOrder], [CacheTime], [Alignment], [Color], [Border], [IconFile],
[Visibility], [ContainerSrc], [DisplayTitle], [DisplayPrint], [DisplaySyndicate], [IsWebSlice], [WebSliceTitle],
[WebSliceExpiryDate],
[WebSliceTTL], @HostUserId, GETDATE(), @HostUserId,
GETDATE(), [IsDeleted], [CacheMethod], [ModuleTitle], [Header], [Footer], [CultureCode],
NEWID(), @NewVersionGuid, [DefaultLanguageGuid],
[LocalizedVersionGuid]
FROM [dbo].[TabModules]
WHERE TabID = @TabIdFromPage AND ModuleTitle = @OriginalModuleTitle
--Update version Guids for related modules
UPDATE dbo.TabModules SET VersionGuid = @NewVersionGuid WHERE ModuleID = @ModuleId
PRINT 'Module Copied
Successfully.'
END
ELSE
PRINT 'Module title you
entered either doesn''t exists in from page or you have multiple modules with
this title on from page.'
END
DELETE FROM @tempTable WHERE moduleName = @OriginalModuleTitle
END
PRINT 'You need to clear site
cache [Host >> Host Settings >> Clear Cache] to view results.'
END
ELSE
PRINT 'Either or both of Copy
to/from page name is incorrect'
-------------------Script ends here------------------------
You need to clear your DNN cache to view results of this script on your portal.
