Copy modules as reference from one DNN page to another using SQL script

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.