Thursday, July 18, 2013

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.
    Keep Reading :)

Is it difficult it 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.


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)

SELECT @TabIdFromPage = TabID FROM dbo.Tabs WHERE TabName = @CopyFromPage
SELECT @TabIdToPage = TabID FROM dbo.Tabs WHERE TabName = @CopyToPage

IF(@TabIdFromPage > 0 AND @TabIdToPage > 0)

       --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)
              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 + ''''
                     IF ((SELECT COUNT(*) FROM dbo.TabModules WHERE TabID = @TabIdFromPage AND ModuleTitle = @OriginalModuleTitle) = 1)

                           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])
                                  @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.'

                           PRINT 'Module title you entered either doesn''t exists in from page or you have multiple modules with this title on from page.'


              DELETE FROM @tempTable WHERE moduleName = @OriginalModuleTitle

       PRINT 'You need to clear site cache [Host >> Host Settings >> Clear Cache] to view results.'

       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.

No comments:

Post a Comment