Will "the Mighty" Strohl

HOW TO: Update Settings in All Instances of a Single DNN Module

Although we would never want it to happen, we occasionally need to update the Module Settings for all instances of a specific module throughout a DotNetNuke (DNN) portal.  There are times when a DNN web site grows to be so large that there are modules and pages all over the place, and the prospect of updating every single instance of a module is just not realistic.  That all changes with a simple SQL Query!

Let's take the Survey Module for instance.  Let's assume that we have individual instances of the Survey Module all over the site.  Maybe there are 100 total.  Let's further assume that when we added each instance, we changed the "Maximum Bar Graph Width" setting to "100px", but now we have a new skin that allows us to extend that width a little bit. 

The following query allows you to change that setting for each and every instance of the Survey Module on your DNN portal.

USE [YOUR_DATABASE_NAME]

DECLARE @ModDefId INT, @ModuleId INT SELECT @ModDefId = [ModuleDefID] FROM [dbo].[ModuleDefinitions] WHERE [FriendlyName] = 'Survey'
DECLARE [cModules] CURSOR FOR SELECT tm.[TabModuleID] FROM [dbo].[TabModules] tm WHERE tm.[ModuleId] IN (SELECT [ModuleID] FROM [dbo].[Modules] WHERE [moduledefid] = @ModDefId)
OPEN [cModules]
FETCH NEXT FROM [cModules] INTO @ModuleId
WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC('UPDATE [dbo].[TabModuleSettings] SET         [SettingValue] = ''150''         WHERE [TabModuleId] = ' + @ModuleId + ' AND         [SettingName] = ''surveygraphwidth''')
-- This one can be used to rollback our change
--    EXEC('UPDATE [dbo].[TabModuleSettings] SET --        [SettingValue] = ''100'' --        WHERE [TabModuleId] = ' + @ModuleId + ' AND --        [SettingName] = ''surveygraphwidth''')
    FETCH NEXT FROM [cModules] INTO @ModuleId END
CLOSE [cModules] DEALLOCATE [cModules]

Before using the above query, you will need to change the database name to match your own.  Also, I STRONGLY recommend backing-up your database prior to executing this or any other query. When the query completes successfully, be sure to reset the cache on the web site, otherwise you will not see your change reflected right away. There are a number of ways to do this. I will post on that next. ;)

Also to note, you can rollback by switching the commented Update statement with the uncommented one. This will make the graph width in the survey results by 150 pixels wide. The rollback query will restore the width to 100 pixels.

For all of you SQL Wizards out there, I am sure that there is a more efficient query to run in lieu of the CURSOR, but this query works, and it is not called by production code. So the negligible performance tradeoff is not relevant. :)

So? How does this relate to YOUR module?

In order to run this query to update one of your own modules, or a third party module, you need to know three things:

  1. The name of the module in the ModuleDefinitions table (SELECT * FROM [dbo].[ModuleDefinitions])
  2. The name of the setting for the module (this can be derived from the source code, or the TabModuleSettings table)
  3. The acceptable value(s) and/or value range(s) for the setting

Once you have the above information figured out, simply update the query above to meet your needs.



blog comments powered by Disqus