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.
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.




About Me

My photo
Delhi, India
Fun, music, travel and nature loving, always smiling, computer addict!!