DotNetNuke them!

(Not a) tutorial: Creating my first DNN scheduled task

2010-07-04T01:33:00.008+03:00

In this post, I’ll describe my (successful) attempt to create a basic email queue functionality using a custom DNN scheduled task.

Why not a tutorial? Because my intention is to show you what I have done, without telling you that this is the right (or the only) way to do it, or demonstrating this as a techically complete and correct solution. This work is far from finished – it just covers basic functionality and is not usable out of the box. But if you’re struggling with DNN scheduled tasks, reading below may provide you with a way to get started.

I found great help in this excellent post here, which you should read for more information on creating your own scheduled tasks in DNN. Actually, this article here is a simple application of the tutorial I found in that post. Read it, it’ll be extremely helpful.

Let’s get to the point.

Purpose:

I had the need to notify custom groups of DNN registered users upon submission of some forms created either with the XMOD Pro or the Datasprings Dynamic Forms module. Although both modules support sending emails upon submission, they are restricted to sending emails to specific people, lists, or roles.

I needed something more complex, such as a database query based on several tables, in order to determine who would get the notification. Both modules allow issuing queries to the database upon submission, so it seemed right to create my own email queue, consisting of a database table (the queue) and a scheduled task to send the mails.

What’s more, a queue can help where there is a bunch of emails to be sent – let’s say you need to send 1k emails upon the next form submission – you don’t have to send them all at once. You just send a dozen each time the scheduled task is ran, until the queue is empty.

Database objects

So – what did I need first? Of course, a table to hold my queue.

CREATE TABLE [dbo].[DotSee_MailQueue]( 
    [MailID] [int] IDENTITY(1,1) NOT NULL, 
    [FromUser] [nvarchar](500) NOT NULL, 
    [ToUser] [nvarchar](500) NOT NULL, 
    [Subject] [nvarchar](500) NOT NULL, 
    [BodyHtml] [nvarchar](max) NULL, 
CONSTRAINT [PK_DotSee_MailQueue] PRIMARY KEY CLUSTERED 
( 
    [MailID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY] 
) ON [PRIMARY] 

GO

Yes, it’s a silly table but enough for my basic functionality: From, To, Subject, Body and an id. All the necessary information to send an email.

Then I needed a stored procedure to retrieve information from that table:

CREATE PROCEDURE [dbo].[DotSee_GetMailQueueItems] 
AS 
BEGIN 
SET NOCOUNT ON; 

Select top 10 [MailID],[FromUser],[ToUser],[Subject],[BodyHtml] FROM [dbo].[DotSee_MailQueue] 

END 

GO

Note: I prefix my tables with “DotSee_” since this is the name I have selected for my custom work (namespaces included, as you’ll see below). Don’t give it serious attention, it’s just my convention.

Setting up a project for my scheduled task

There are more than one ways to begin creating a scheduled job for DotNetNuke using Visual Studio, but there is a common denominator: Your job MUST be a DLL file in DotNetNuke’s bin directory. So you can go either with a Web Application Project or with a Class Library. I chose Class Library. Here’s what I did:

Created a new Class Library project.

Added a reference to DotNetNuke.dll from an existing DNN installation (and set Copy Local to false)

Added a reference to Microsoft.ApplicationBlocks.Data from the same DNN installation (in order to be able to use DNN’s own DAL for interacting with the database)

Set the build path INSIDE DNN’s bin folder to save me time copying and recopying the dll.

Went to the project’s properties and deleted the contents of the root namespace field. Yes, it should be empty for your dll to work. Don’t miss that.

Regarding (4), I did this work on a virtual machine, where I had a DNN installation and my class library project on the same computer. I suggest you follow some similar approach, since it is very easy to test and debug (you can debug only by attaching to the w3wp.exe process, but it’s better than nothing).

Writing the code

Two more things you must know: Your custom class must inherit DotNetNuke.Services.Scheduling.SchedulerClient and the only method that is called by DNN’s scheduler is DoWork().

So here’s my custom scheduler class:

Imports DotNetNuke.Services.Scheduling 
Imports DotNetNuke.Services.Exceptions 

Imports System 
Imports System.Web 
Imports System.IO 

Namespace DotSee.DnnScheduledMailNotifier 

    Public Class Mailer 
        Inherits SchedulerClient 

        ' requires a special constructor which 
        ' accepts a ScheduleHistoryItem 
        Public Sub New( _ 
            ByVal objScheduleHistoryItem _ 
            As ScheduleHistoryItem) 

            MyBase.New() 
            Me.ScheduleHistoryItem = objScheduleHistoryItem 

        End Sub 

        Public Overrides Sub DoWork() 
            Try 
                ' perform some tasks 
                MailWorker.DoWork() 

                ' report success to the scheduler framework 
                ScheduleHistoryItem.Succeeded = True 

            Catch ex As Exception 

                ScheduleHistoryItem.Succeeded = False 
                ScheduleHistoryItem.AddLogNote _ 
                    ("EXCEPTION" + ex.ToString()) 
                Errored(ex) 
                Exceptions.LogException(ex) 

            End Try 
        End Sub 
    End Class 

End Namespace

Apart from the MailWorker.DoWork() line (code coming right below), the class can be used as a template for writing your own scheduled tasks. (You will use your own namespace, of course). I put my specific code in a different class so that it would be easy to make the distinction between code needed for my specific need and the “template” code that every DNN scheduled task needs.

Here’s my MailWorker class (DotNetNuke.dll referenced was version 5.2.1, GetHostSettingsDictionary may not work with 4.x):

Imports DotNetNuke.Services.Mail
Imports DotNetNuke
Imports DotNetNuke.Data
Imports DotNetNuke.Entities.Host


Namespace DotSee.DnnScheduledMailNotifier


    Public Class MailWorker

        Private Sub New()

        End Sub

        Public Shared Sub DoWork()

            Dim d As Dictionary(Of String, String)
            d = Host.GetHostSettingsDictionary()

            Dim dnp As DataProvider
            dnp = DataProvider.Instance
            Dim dr As IDataReader

            Dim ids As New List(Of Int32)

            dr = dnp.ExecuteReader( _
                "DotSee_GetMailQueueItems", Nothing)

            If Not dr.Read() Then Return

            While dr.Read

                Mail.SendMail( _
                    dr("fromuser").ToString _
                    , dr("touser").ToString _
                    , "" _
                    , dr("subject").ToString _
                    , dr("bodyhtml").ToString _
                    , "" _
                    , "" _
                    , d("SMTPServer") _
                    , d("SMTPAuthentication") _
                    , d("SMTPUsername") _
                    , d("SMTPPassword") _
                    )

                ids.Add(CType(dr("mailid"), Int32))

            End While

            For Each id In ids
                dnp.ExecuteSQL( _
                    String.Format( _
                        "DELETE FROM DotSee_MailQueue WHERE mailid={0}" _
                        , id.ToString))
            Next

        End Sub

    End Class

End Namespace

What it actually does (in a brutal way, I should add) is check the table, get 10 items at a time (via the DotSEe_GetMailQueueItems stored procedure defined earlier), send an email for each of the items and then delete the corresponding records from the table. (Ok, I know the way it does it sucks a bit, but It’s only quick and dirty functionality, remember).

So when I compile this beauty, my DotSee.DNNScheduledMailNotifier.dll goes inside DNN’s bin folder, and now what I have to do is tell DNN that this is a scheduled task and that the scheduler should include it. Here’s how:

Host-Schedule and click “Add Item to Schedule”.

Set friendly name to whatever you want

Full class name and assembly should be your assembly name and your class name (the class containing the DoWork method) combined, as in AssemblyName.Classname. My Assembly’s name is DotSee.DnnScheduledMailNotifier and my class name is Mailer, so the full name is DotSee.DnnScheduledMailNotifier.Mailer.

Check the “schedule enabled” checkbox

Time lapse MUST have some value or else the task will not run at all.

That’s it! The task is ready to run. I tested it by manually inserting records into the DotSee_MailQueue table and it worked well, even with hundreds of records, sending 10 emails every 5 seconds.

So now I have my basic system ready. When I need to send emails to groups of people not defined by roles or other DNN constructs, I just have to write a good SQL query that fills the table with recipients that correspond to the criteria I need every time and presto!

Of course, there’s plenty of room for configuration / parameterization (for example, the number of emails sent each time or the type of email – html or plain text, or even a check for whether delivery was successful). But I guess you got the idea.

Thank you for your time.

ModuleMaster, an admin module that was really missing from DNN

2010-04-24T18:12:00.001+03:00

ModuleMaster is what I was looking for (and often considered developing myself). It can link modules – along with their settings – on the pages of your choice. Simple as that, but DNN doesn’t give you this option – you can either mark a module as “displayed in all pages”, in which case you only have linked modules to your original one – meaning that if you change something in one, it’ll change in every linked module, or copy the module by hand and then apply settings by hand (IPortable is not always well-implemented).

This module is a time-saver, especially for sites that have a number of pages already developed (and more especially if you’re using templates to create new pages). Imagine the scenario where the client asks you to apply a new text/html module, for example, in about 500 pages that you have already created, demanding to be able to edit each one of them independently of the others and needing to use a specific container. Count the DAYS it’ll take you to do it by hand. EDIT: Unfortunately, as I realised later, the module just creates “shadow” copies – i.e., links and not new instances, but remains useful whatsoever.

If anybody tries it, please post a comment here. I haven’t tried it yet, but looks very promising and it already has a couple of good reviews on SnowCovered.

Using page titles instead of names on the DNN breadcrumb skinobject

2010-03-26T13:39:00.001+02:00

Simple, but useful. A fellow developer needed to use the page title (and not the page name which is the default) on the breadcrumb skinobject of his skin. The reason was that his menu had limited real estate so he had to keep names short, but he needed to give more information on the navigation breadcrumb.

The solution is just so simple! In your skin’s .ascx file, you’ll see a definition like the following:

<dnn:BREADCRUMB runat="server" id="dnnBREADCRUMB" CssClass="Breadcrumb" RootLevel="0" Separator=" > " />

If you add the following attribute:

UseTitle="True"

You’ll have something like this:

<dnn:BREADCRUMB runat="server" id="dnnBREADCRUMB" CssClass="Breadcrumb" RootLevel="0" Separator=" > " UseTitle="True" />

And then your breadcrumb will be using page titles instead of names.

Enjoy.

Quick snippet: Show first n characters of a string without cutting words in half with OWS

2010-03-26T13:13:00.001+02:00

Just a quickie for those of you who use the free Open Web Studio DNN RAD environment: I needed to show only the first n characters of a string that I got via a query in OWS, but wanted to “stop” display at the end of a word, (that is, not being very strict on the n character limit).

If our field is, for example, called “description” and the character limit is, let’s say, 100, and we need to insert three dots (…) after the end of the string if it’s only part of the original, here’s the function (be sure to write it on a single line):

{IIF, "[FORMAT,"[description]", {LENGTH}]>100", "[FORMAT,"[description]", {LEFT:[FORMAT,"[FORMAT, "[description]", {LEFT:100}]",{LASTINDEXOF: }]}]...","[description]"}

Change the field name, the number of characters and the three dots at the end to whatever suits you.

Of course there are several ways to achieve that via your SQL query itself, but that’s a quick way to do it in OWS if you can’t modify the query (when you’re using a view that is used somewhere else, for example).

Enjoy.

Free search/replace admin module from Engage Software

2010-03-15T10:15:00.001+02:00

This one is cute. It,s called Engage F3 and it lets you search and replace text in Text/Html and Engage Publish modules, site-wide. It’s intended for admin use only.

You can download it for free from Snowcovered.

Free HTML Template Repeater module – TEXT/HTML replacement

2010-02-25T10:25:00.001+02:00

A simple idea, but someone had to conceive it and develop it. We often need to repeat a template made for a text/html module, without necessarily having the data stored in the database. Moreover, we need to give editors an easy way to enter data without having to know HTML to edit the markup.

There already are solutions, like Open Web Studio (formerly Listx) and the core Form and List module (formerly User Defined Table) module which can achieve similar results, but in a more complex and time-consuming way for the developer when it comes to simple, non-database-bound implementations like the one described above.

Enter HTML Template Repeater, created by Software’s Edge. You can define a template just as you would normally do in a Text/HTML module, defining placeholders for “variables” that you need to be replaced when the module output is rendered. The module provides you with a form to create “records” based on those variables and then you can repeat your template for as many records as you have entered. It even has paging functionality!

You can download the module from Codeplex, or see a video tutorial here. Another blog post / tutorial on the module is here.

ERD Data Model and Meta Data Dictionary for DNN CE 5.2.2

2010-02-25T10:15:00.001+02:00

Mr. Chodnicki from R2integrated has created a very useful data model and meta data dictionary for DotNetNuke Community Edition 5.2.2, which you can download by clicking here.

Displaying a “no search results” message when no search results are found

2009-08-30T20:30:00.001+03:00

In earlier (4.x) DNN versions, when you make a search and it yields no results you are presented with an empty search results page – without any message. It’s silly, but nobody ever included this as a feature.

Thanks to this post, I found a way to include a “no search results” message. Or course, there are plenty of ways to do that, but I think this is the fastest. You’ll have to edit the admin/search/searchresults.ascx file as follows:

First, find this line and add what’s written in big, bold letters:

<asp:HyperLink id=”lnkTitle” runat=”server” Name=”result” CssClass=”SubHead” NavigateUrl=’<%# FormatURL(DataBinder.Eval(Container.DataItem,”TabId”),
DataBinder.Eval(Container.DataItem,”Guid”)) %>’ Text=’<%# DataBinder.Eval(Container.DataItem, “Title”) %>’>
</asp:HyperLink>

Then add the following code directly under the last line of the file:

<div id=”NoResults”>
<h3 class=”red” style=”text-align:center”>No Search Results Found</h3>
</div>
<script language =”Javascript”>
var search;
search = document.getElementsByName(”result”);

if (search.length == 0) {
document.getElementById(”NoResults”).style.display=’block’;
}
else {
document.getElementById(”NoResults”).style.display=’none’;
}
</script>

Change the “No Search Results Found” string to whatever you like and you’ll have your “no results” message when your search yields nothing. Of course, you can also change the h3 tag with whatever you like, even include an image.

I don’t know what’s going on with 5.x versions since I haven’t tried it yet in one, but I think it’s still an interesting tweak for 4.x versions.

EDIT: This may not work if you leave the single and double quotes as they are in the code above. Better paste the code to Notepad first. Sorry about that, but I didn’t have the time to replace. (Live Writer can be very unfriendly sometimes :) )

DnnGallery.net – a place to showcase your DNN sites

2009-04-27T13:55:00.001+03:00

One of the questions I have always found hard to answer is “ok, but show me some ‘wow’ sites made with DotNetNuke”. I always had a hard time finding some sites till I read a post about www.dnngallery.net at the DotNetNuke forums.I think it’s pretty interesting, provided that the “Showcase” section at the official DotNetNuke site is dead and that there is no other place (none that I know of, at least) where one can see and rate some worthy DNN sites.

DnnGallery allows you to submit your DNN site and lets other users rate it. You will find some interesting implementations there, let’s hope that more DNN publishers participate in the future.

UDT 03.05.01 and incorrect filtering of script in text/html fields

2009-04-21T19:47:00.001+03:00

Those of you using the latest version of the old User Defined Table module (which is to be replaced by the Form and List module in DNN 5.x) may have noticed an annoying bug that makes the UDT module insist on filtering out script, object and embed tags even when you have specifically specified that you don’t want it to.

What does this mean? It means that you can’t embed YouTube videos, for example, because UDT will filter out the embed code’s tags.

When does it happen? Well, fortunately it doesn’t happen when you’re admin or host. But it does happen if you’ve given other users edit rights on UDT data. Any user that is not Admin or Host cannot write these tags in the HTML view of a Text/HTML field inside a User Defined Table. They are just filtered out, even when the relevant setting is unchecked.

To fix that, you’ll need to download the code for UDT 03.05.01. The problem lies in file EditControls.vb, line 52:

If ParentModule.Settings.ContainsKey(SettingName.ForceInputFiltering) Then
inputFilterScript = Not isAdmin
inputFilterTags = isAnonymous
End If

The “if” clause in line 52 just checks whether the setting exists, not its value. To fix that you need to make the code in line 52 as follows:

If ParentModule.Settings.ContainsKey(SettingName.ForceInputFiltering) AndAlso ParentModule.Settings(SettingName.ForceInputFiltering).Equals("True") Then
inputFilterScript = Not isAdmin
inputFilterTags = isAnonymous
End If

(I apologise for the line breaks, please consider that the IF clause should be in a single line)

What I’ve done is that I have added a second condition that actually checks the VALUE of the setting and applies the rule only if it’s true.

You can compile the code with that change and just replace the old dll file with the newly-compiled one in dotnetnuke’s Bin folder.

Please let me know if you’ve been having trouble with this, I’ve already got a compiled dll so I could send it to anyone who’s not into code much – although, as always, I must insist that you always try those things at your own risk!

Replacing default module titles in search results with the corresponding tab’s title

2009-04-03T19:43:00.001+03:00

How many times has it happened to you? You put some Text/HTML module here, a Links module there, maybe a third-party module and you forget to change its title, mostly because you’re using a container that doesn’t utilize it, or for any other reason.

This can lead to ugly search results, since DNN’s indexer stores the module’s title in the SearchItem table and uses it as the title for each one of your search results.

On the other hand, you’ve got some module titles you’ve explicitly set and you need to preserve for your search results. So, you’ve only got to replace the DEFAULT titles with something – in my script, I chose to replace them with the corresponding tab’s title, but you could alter it and make it display anything – or even delete the record if you like.

So here’s a trigger that checks whether the row being written in the SearchItem table is for a module having the default title, and if so, changes the title to the corresponding tab’s title.

CREATE TRIGGER tr_FixSearchItemTitle
   ON dbo.SearchItem
   AFTER INSERT,UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

--These three variables will come from the
--searchitem table.
declare @searchitemid int
declare @moduleid int
declare @title nvarchar(200)

--These two will come from the
--modules table.
declare @moduletitle nvarchar(200)
declare @moduledefid int

--This will come from the
--moduledefinitions table
declare @friendlyname nvarchar(200)

--This will come from the
--tabs table
declare @tabtitle nvarchar(200)

--Get inserted values
select
      @searchitemid = searchitemid
    , @title = title
    , @moduleid = moduleid
from
    inserted

--Find moduletitle and definition id of the
--module being inserted in searchitems table.
select
      @moduletitle = moduletitle
    , @moduledefid=moduledefid
from
    modules
where
    moduleid = @moduleid

--Find the friendly name from the
--moduledefinitions table
select
    @friendlyname = friendlyname
from
    moduledefinitions
where
    moduledefid = @moduledefid

--If the title of the module in the searchitem table
--is equal to the module definition's friendly name
--then we can safely suppose that the module has
--the default title.
if @friendlyname = @title
begin

    --Get the tab's title
    select
        @tabtitle = title
    from
        tabs
    where tabid in
        (
        --If we have multiple instances
        --of modules in several pages,
        --just get the first page. I know,
        --this might be ugly but I've not found
        --any other way.
        select
            top 1 (tabid)
        from
            tabmodules
        where
            moduleid = @moduleid
        )

--Replace the default title with the page's title.
update
    searchitem
set
    title = @tabtitle
where
    searchitemid = @searchitemid

end

END
GO

This trigger checks each entry in the SearchItem table at the time it’s inserted or updated and determines whether the module title being inserted is a default title. It achieves that by comparing the module’s title with the FriendlyName field of the ModuleDefinitions table – all core modules and all third-party modules I know use this value as the default title. This means the trigger will probably work with any combination of modules you’ve installed in your site.

There’s a catch, though: If you have the same module instance (not the same module, the exact same instance – that means you’ve used the “add existing module” option) in more than one page, it’ll get only one title for it – that is, if you’ve “added an existing module” to several pages, each search result that corresponds to such a module may have the wrong title – no problem if you’re not using multiple instances of modules.

Another catch is that you have to have recursive triggers disabled for your database, or this will execute forever – it will run itself again and again since it alters a newly inserted or updated record.

In order to see how this trigger handle things, you should delete everything from your SearchItem table and then do a reindex via the Host->Search Admin page, or else the trigger will run only for new or updated entries.

As always, use at your own risk!

Using the PortalSettings class in containers

2009-01-16T14:32:00.001+02:00

It’s simple, but useful: If you try to use the PortalSettings class in code you have added to a container’s .ascx file, you’ll find out that you can’t. The PortalSettings shared class is only directly accessible from skins.

That’s dissapointing, especially when you need to grab things like, for example, the current tab id (PortalSettings.ActiveTab.TabId) inside a container for some reeson.

The solution to this is to grab your PortalSettings contents from the HttpContext and use it like this:

In order to get, for example, the current tab id from your container, you can now use _portalSettings.ActiveTab.Tabid.

How to enable - disable caching programmatically and on-demand

2008-12-05T09:58:00.001+02:00

Sometimes, when you're doing something programmatically, like moving tabs around, you really don't want caching in your way in any form, since there's a great danger it'll affect the outcome. For some reason, programmatically clearing the cache may not be enough - you just don't need any caching around when you do certain stuff, and you don't want to oblige any host user to manually clear the cache. Here's a really simple way to disable caching programmatically.

'Get the initial caching level
'from the HostSettings arraylist
'(this is the easiest way)
Dim initialCachingLevel As String
initialCachingLevel = _
HostSettings.Item("PerformanceSetting").ToString

'Disable caching
Dim hc As New Entities.Host.HostSettingsController
hc.UpdateHostSetting("PerformanceSetting", "0")

'Do your stuff here

'Enable caching with
'the initial caching level
hc.UpdateHostSetting("PerformanceSetting", initialCachingLevel)

What I'm doing in the bit of code above is disable caching temporarily to do what I have to do, then return it to its previous state. That's equivalent to going to Host Settings-Performance, from the Host menu, change the caching level, click "update", do any job I have to do and then get back to the Host menu and return caching to its previous value.

The caching level is defined by the "PerformanceSetting" Host property which can take string values ranged from "0" to "3" representing different levels of caching:

0 - No caching

1 - Light caching

2 - Moderate caching

3 - Heavy caching

Some notes:

You can get the current caching level from the HostSettingsController too, but it's a bit more fussy since the GetHostSetting method returns an IDataReader. Since the Host's settings get copied to the publicly-available HostSettings hashtable, I thought it's easier to obtain the current setting from there.

Changing the value in the HostSettings hashtable will NOT affect your caching. You've got to use HostSettingsController.UpdateHostSetting.

You may also want to clear any cache left before you start doing your stuff, it helps in some cases. In this case, the DataCache class may prove very useful. Here's what you can do (you can choose not to execute some of these lines, depending on your needs):

DataCache.ClearTabsCache(0)
DataCache.ClearPortalCache(0, True)
DataCache.ClearHostCache(True)
DataCache.ClearModuleCache()
DataCache.ClearTabPermissionsCache(0)
DataCache.ClearUserCache( _
0, _
UserController.GetCurrentUserInfo().Username)

Some of the above statements may overlap, in the sense that they're actually subsets of other statements. For example, ClearHostCache() is considered equivalent to the Clear Cache option that exists in the Host Settings page. I personally prefer to issue ALL those statements, even if they overlap.

The DataCache class has some more methods but I think those mentioned here are enough to give you a good start. You'll probably find out the rest yourself, in an as-needed basis.

The above code applies only to portal 0. Whenever you see a boolean parameter there, it's indicating "cascade" cache clearing, which I always want set to true. Also, the ClearUserCache() method needs the currently logged on user's name, which I get by using UserController.GetCurrentUserInfo() which returns a UserInfo object containing, among others, the user's name.

Resolving "fileid=xxx" field values to actual file paths

2008-11-28T14:44:00.001+02:00

Following a previous article of mine, The dbo.tabs.IconFile field issue, I would like to share an easy way to resolve any field that holds values of the type "FileID=xxx" (where xxx is the primary key of a record in the Files table) into the actual file path. This is especially useful when you deal with the IconFile field in the Tabs table, as well as with fields of type Image in a User Defined Table, and probably in a lot more places too.

I have created a UDF (User Defined Function) which accepts a string value and looks whether it's of type "FileID=xxx". If it is, it constructs the full path (including the portal number) and returns that, otherwise it just returns the initial string with no modification at all. You can use this scalar-valued function inline, in your own SELECT statements, like this:

EXAMPLE

SELECT
tabid,
tabname,
dbo.ResolveFileField(iconfile)
FROM tabs

CODE

CREATE FUNCTION
dbo.ResolveFileField
(
    @file varchar(1000)
)
RETURNS varchar(1000)
AS
BEGIN

declare @retval varchar(1000)
declare @portalid int

if patindex ('FileID=%', @file) >0

begin

    declare @fileid int
    set @fileid =
        convert(
                int,
                replace
                    (
                    @file
                    , 'fileid='
                    , ''
                    )
                )

    select
        @retval =
            'Portals/'
            + convert
                    (
                      varchar(10)
                    , files.portalid
                    )
            + '/'
            + folders.folderpath
            + files.filename
    from
        files
    left outer join folders
    on files.folderid = folders.folderid
    where
        files.fileid=@fileid

end

else

begin
    set @retval = @file
end

return @retval

END
GO

How to promote a regular DotNetNuke user to a superuser

2008-11-27T13:00:00.001+02:00

I found this in Kevin Southworth's blog and I think it's worth mentioning since it may save your life in case you have forgotten your superuser's password. Using SQL, you can promote a regular DNN user to a superuser like this:

-- Promote regular user to SuperUser

DECLARE @username varchar(50)

SET @username = 'theUsernameToPromote'

UPDATE
    Users
SET
    IsSuperUser = 1
WHERE
    username = @username

DELETE FROM
    UserPortals
WHERE
    UserId =
    (SELECT UserID
    FROM Users
    WHERE username = @username)

Simply replace 'theUsernameToPromote' with the user name you need.

Thanks Kevin!

Persisting large values in Module Settings when creating a custom module

2008-11-23T01:59:00.001+02:00

So you want to create a simple custom module. At first, it seems easy. Just two or three settings to take care of, no need for complicated, custom tables and unistall/uninstall scripts. DNN's built-in module settings API seems to be enough. But suddenly, needs grow a lot. There are some large values you need to store and the ModuleSettings table won't allow large chunks of information per row. It seems that you have to switch to a custom table which will be holding your settings...

...or use my class :)

I came upon the need of storing just one large value (a serialized object, to be more specific) in a custom module I was creating. I didn't have any way to know how large the serialized (XML) sting would be, since the object was a collection, and I HAD to store it in module settings. I thought it wouldn't be worth to create a custom table and the accompanying code to store settings in a case like this, so I decided to extend DNN's ModuleController class instead.

What I did was to write a new ModuleControllerExtended class which inherits from ModuleController (so I could use it in its place) and add two methods to it:

1. UpdateLargeTabModuleSetting: This method is essentially an extension of the UpdateTabModuleSetting method of the ModuleController class which allows storing large string values in the ModuleSettings table by breaking them into smaller chunks and storing multiple name/value pairs. If a value is small enough to fit into a single row of the ModuleSettings table, then it's stored normally, as it would with the UpdateTabModuleSetting method.

When the value is large, it's stored in 2KB chunks in several rows using a numeric prefix (in the form of _x) for each row, based on the initial name given for the setting. For example, a setting named "SerializedObject" with a value sized at 5KB would be stored in 3 rows with names "SerializedObject_0", "SerializedObject_1" and "SerializedObject_2" accordingly.

The method also takes care to delete rows from the ModuleSettings table every time an update takes place to ensure that there are no leftovers should you specify a value of a smaller size (and probably fewer chunks) than the one that may be already stored.

2. ReadLargeTabModuleSetting: This one reads a large setting doing all the work needed to give you back its string value, but it can also read single-row settings. This is a shared method, and it was added to the class only for consistency. It does not extend any of the known methods of the ModuleController class, meaning you can always remove it from the definition of the class and use it as stand-alone code.

To use the method, you will need the hashtable containing the module's settings, which you can easily get by using the base class GetModuleSettings() method. You feed the method with the hashtable, the module's id and the name of the setting you want and you get a string value containing the "large" value for the setting you specified, or just a setting value should the setting be a "normal" one.

It's not really complicated and it should save you a lot of time when dealing with situations like the one I described above. I would love to hear your comments, though.

Here's the code:

Imports Microsoft.VisualBasic
Imports System.Collections.Generic

Public Class ModuleControllerExtended
    Inherits DotNetNuke.Entities.Modules.ModuleController

Public Sub New()
    MyBase.new()
End Sub

Public Sub UpdateLargeTabModuleSetting( _
   ByVal tabModuleSettings As Hashtable _
, ByVal tabModuleID As Int32 _
, ByVal settingName As String _
, ByVal settingValue As String)

    Dim cntDel As Int32 = 0
    Dim o As Object
    Dim continueDeleting As Boolean
    continueDeleting = True

    'Delete all multiple-value module settings, if they exist.
    While continueDeleting = True
        o = tabModuleSettings(settingName + "_" + cntDel.ToString)
        If Not o Is Nothing Then
            DeleteTabModuleSetting(tabModuleID, settingName + "_" + cntDel.ToString)
            cntDel += 1
        Else
            continueDeleting = False
        End If
    End While

    'Guard - if setting value is less than 2KB, update normally and exit
    If settingValue.Length < 2000 Then
        'Normal value
        UpdateTabModuleSetting(tabModuleID, settingName, settingValue)
        Exit Sub
    End If

    'If we get to this point, then setting value is more than 2KB.
    'Delete the original setting (if it exists) so as not to get confused.
    DeleteTabModuleSetting(tabModuleID, settingName)

    'Split the value in 2KB chunks
    Dim stringList As New List(Of String)
    Dim sb As New StringBuilder(settingValue)

    While sb.Length >= 2000
        stringList.Add(sb.ToString.Substring(0, 1999))
        sb.Remove(0, 1999)
    End While

    'Add the last chunk
    If sb.Length > 0 Then stringList.Add(sb.ToString)

    'Now do the update changing the setting name with the suffix _x (x=0,1,2,etc.) for
    'each update
    Dim cnt As Int32 = 0
    For Each s As String In stringList
        UpdateTabModuleSetting(tabModuleID, settingName + "_" + cnt.ToString, s)
        cnt += 1
    Next

End Sub

Public Shared Function ReadLargeTabModuleSetting( _
     ByVal tabModuleSettings As Hashtable _
    , ByVal tabModuleID As Int32 _
    , ByVal settingName As String) As String

    'Guard - if there is a single setting, just return that and exit
    Dim objTester As Object
    objTester = tabModuleSettings(settingName)
    If Not objTester Is Nothing Then
        Return (CType(objTester, String))
    End If

    'If we got to this point, there's a large value stored.
    'Loop through the records and reconstruct the value.
    Dim sb As New StringBuilder
    Dim cnt As Int32 = 0
    Dim o As Object
    Dim continueAdding As Boolean
    continueAdding = True

    While continueAdding = True
        o = tabModuleSettings(settingName + "_" + cnt.ToString)
        If Not o Is Nothing Then
            sb.Append(CType(o, String))
            cnt += 1
        Else
            continueAdding = False
        End If
    End While

    Return sb.ToString

End Function

End Class

Creating pages based on templates - eventually!

2008-11-07T17:14:00.003+02:00

All my attempts to use custom templates when creating new DNN pages for my portals were frustrating. Even though I exported the template from a page using the Export feature, the new template would not show up at all in the combo box when creating a new page, although it existed in the file system. Even the default template wouldn't show up!

Here's a simple solution to make those template files appear when you create a new page:

1. Go to Host Settings - Other Settings and locate the "File Upload Extensions" section. Add "template" (without the quotes) there and save your changes.

2. Click "Restart Application" in Host Settings.

3. Go to the File Manager and synchronize files with the "recursive" option set.

4. Optionally, clear the portal's cache in Host Settings (only if it doesn't seem to work differently).

Now you'll have access to your custom templates when creating new pages. Hope that helps.

Adding Javascript to the BODY tag in DNN

2008-10-24T17:31:00.002+03:00

When working with .ascx skin files, you will sometimes need to add some Javascript to the BODY tag, such as something that runs when the OnLoad event triggers.

Well, you can always add a BODY tag inside your .ascx file along with the Javascript you like and hope that the browser will understand that. Essentially, you'll end up having two BODY tags in your source code-one that is generated by DNN and one that is typed by you. Some browsers (like Chrome) understand what's happening and try to transfer the Javascript to the actual BODY tag when rendering the page, but others don't.

What you can do, though, is create a Page_Init event handler inside your .ascx file and put this code there:

Dim body As System.Web.UI.HtmlControls.HtmlGenericControl = CType(Page.FindControl("body"), System.Web.UI.HtmlControls.HtmlGenericControl)
body.Attributes("onload") = "blablablaJavascript"

Where blablablaJavascript is your Javascript. Of course, you can do that for other events (even for other tags) as well.

If you're using a single .ascx file with no code-behind (like most do), you'll want to define a Page_Init event there. You can do that like this:

Thickbox and Google Maps API - avoiding random thickbox initialization problems

2008-10-24T17:23:00.006+03:00

Thickbox is a very nice JQuery-based Javascript plugin that allows you to present images and other stuff (even whole pages!) in your page in a popup manner but without leaving the current page or opening a new one. Essentially, it's considered an evolution of the Lightbox script which only works with images.

Many developers use Thickbox with Google maps, in order to provide clickable links on Google Map bubbles that lead to enlarged photos or other info. Some of them (including me) noticed, to their surprise, that suddenly Thickbox wouldn't work correctly inside Google Maps. Specifically, it would work once in a while, and the only way to ensure it worked would be to click on a bubble, close it an then click on another. Then all Thickbox links inside bubbles would work.

There seems to be a problem with the new version of the Google Maps API that kills the functionality of Thickbox. According to this discussion on Google Groups, a way to ensure Thickbox is working correctly is to "lock" on version 2.122 of the Google Maps API inside the Javascript that creates the markers. That is, use: google.load("maps", "2.122"); instead of google.load("maps", "2.x");

I've been using Thickbox and Google Maps in some DNN sites and I was panicked to see this happening in the first place. Although this post is not exactly DNN-related, I think it'll be useful to all of you working with DNN out there. I was very happy to know that there wasn't anything wrong with my DNN development, it was only Google's API.

Hope it helps.

Finding a tab's parent by level

2008-09-26T19:07:00.001+03:00

This is a simple but useful user defined function I have implemented in order to recursively find a tab's parent on a specific level. Syntax is as follows:

select dbo.udfGetParentByLevel (level, tabid)

where level is the level you need (0,1,2 etc.) and tabid is the id of the tab for which you need to find the parent.

Nothing much, but can save you in certain scenarios.

CREATE FUNCTION udfGetParentTabByLevel
(
-- Add the parameters for the function here
@parentlevel int, @initialtabid int
)
RETURNS int
AS
BEGIN

declare @level int
declare @tabid int
declare @parentid int

select @level = level, @tabid=tabid, @parentid=parentid from tabs where tabid=@initialtabid

while @level > @parentlevel
begin
select @level = level, @tabid=tabid, @parentid=parentid from tabs where tabid=@parentid
end
-- Return the result of the function
RETURN @tabid

END
GO

Copying settings between DNNArticle modules

2008-09-19T15:07:00.001+03:00

ZLDNN's DNNArticle is a really cool article module, loaded with a ton of features. Unfortunately, sometimes you'll have to add more than one modules on your site, and a ton of features typically comes with a ton of settings. In order not to have a hard time setting all the options from scratch, here is a script you can use to copy all those nifty settings you'll find in the "DNNArticle Settings" area from an already existing module.

You need to know three things: The source and destination module ids (NOT the tab ids, the actual module ids) and the tab id which will be used for the presentation of an article.

In the script given, I'm copying settings from module id 430 to module id 1232, with a view tab id of 986. Be sure to put your own numbers there.

Also, before running the script, make sure you have visited the settings area of the (newly added) DNNArticle module to be updated and have pressed "update" there (even if you haven't changed anything) so that the corresponding records are created in the ModuleSettings table.

The script is fairly straightforward, use at your own risk as always. Enjoy.

/*
------------------------------------------------------
Start of values to be changed each run
------------------------------------------------------
*/

-- This is the module id we are copying settings FROM
declare @originalmodule int
set @originalmodule=430

-- This is the module id we are copying settings TO
declare @moduletobeupdated int
set @moduletobeupdated = 1232

-- This is the view tab id for the module we are updating, in case it is
-- different than the one of the source module.
declare @viewtab int
set @viewtab=986

/*
------------------------------------------------------
End of values to be changed each run
------------------------------------------------------
*/

-- Some variables to hold table data
declare @settingname nvarchar(50)
declare @settingvalue nvarchar(2000)

-- Get a cursor and start updating
declare cur cursor fast_forward for
select
     settingname
    ,settingvalue
from
    modulesettings
where
    moduleid=@originalmodule

open cur
fetch next from cur into @settingname, @settingvalue

while @@fetch_status=0
begin
    if @settingname='ViewTab'
    begin
        update
            modulesettings
        set
            settingvalue=@viewtab
        where
            settingname=@settingname and moduleid=@moduletobeupdated

    end

else

begin

        update
            modulesettings
        set
            settingvalue=@settingvalue
        where
            settingname=@settingname and moduleid=@moduletobeupdated

end

fetch next from cur into @settingname, @settingvalue

end

close cur
deallocate cur

MSDN-style core DNN documentation

2008-08-06T06:41:00.003+03:00

Mr. Ernst Peter Tamminga has created a help (.CHM) DNN Core reference file, based on the inline code documentation comments found on the DNN source code. Pretty useful, if you want to have a quick browse at all the DNN classes. The latest download is based on DNN 4.8.4.

You'll find it here: http://www.codeplex.com/DNNHelpSystem

Changing what appears on the title bar

2008-07-04T18:07:00.003+03:00

DotNetNuke has two ways of setting the title bar, depending on whether you set the "Page Title" field in Page Properties. It either displays your page title as you have set it, or the full path to your page (similar to the breadcrumb) when you leave it blank.

Some third-party modules, though, do use the page title for their purposes so you may want to have a page title exactly the same as the page name (reminder: the page name is what you see in the navigation component, while the page title is what you see at the browser title bar).

But you don't want the page title to appear "naked" in the title bar. You may still want to display the full path, or even just the portal name along with the current page's title.

To do either:

- Open default.aspx.vb (you'll find it in the root folder) with your favourite editor, even with notepad.

- Locate the InitializePage() function.

- Find the following chunk of code:

' set page title
Dim strTitle As String = PortalSettings.PortalName
For Each objTab In PortalSettings.ActiveTab.BreadCrumbs
strTitle += " > " & objTab.TabName
Next
' tab title override
If PortalSettings.ActiveTab.Title <> "" Then
strTitle = PortalSettings.ActiveTab.Title
End If

The first part of the code (set page title) says: "Put the full path in the title"
The second part of the code (tab title override) says: "If the title is not empty, just show the title the user has entered and nothing else".

In order to show the portal name along with the title the user has entered, change the line:

strTitle = PortalSettings.ActiveTab.Title
to
strTitle = PortalSettings.PortalName + " > " + PortalSettings.ActiveTab.Title

In order to show the full path in any case (regardless whether the title is empty or not), DELETE the second part of the code all together (the last if...end if part). That way, the title will always be the full path to your page, comprised of the corresponding page titles of all the parent pages till the root page.

Retrieving tabs using SQL, security-aware

2008-06-25T18:36:00.001+03:00

There are times when you like to go deeper with DNN and retrieve certain information by means of an SQL Query or a stored procedure. That's what happened to me when I needed to create a custom combo box which would redirect the user to specific pages.

In order to achieve that functionality, I had to use a module which would permit me to issue SQL queries to DotNetNuke and then format the results as I wanted. As I've stated many times before, I find the ListX module pretty useful in these situations.

One thing I discovered, though, is that you need to have a deeper understanding on what's going on with security in order to issue an SQL query that selects ONLY the tabs that a registered user has the right to see. You see, DNN tabs are not always visible to all user roles, so when using SQL you must be careful not to retrieve tab records that lead nowhere for the current user.

I'm not going to go into details on what I've done with the ListX module, unless someone asks specifically for it. Instead, on this post, I'm going to focus on how to issue a SQL query that retrieves records from the Tabs table that are visible for the current user.

For this post, it is assumed that you already know the ID of the current user in some way.

If you issue something like:

SELECT tabid, tabtitle FROM Tabs

Then you'll have all tabs, even those that are deleted. So we first need to have a WHERE clause to exclude deleted tabs, like this:

SELECT tabid,tabtitle FROM Tabs WHERE isDeleted=0

That leaves as with all active tabs, even those that are accessible by the administrator. In order to make our query security-aware, we have to see what tables the Tabs table is related to, in the context of security permissions.

The related tables are the following:

TabPermission: A table which holds role permissions for every tab. Remember, DNN security works with roles. (We assume that we're not using the special "specific userid" feature that was introduced in later version of DNN).
UserRoles: A table which holds the participations of DNN users to DNN security roles
Users: A table which holds the actual DNN users.

The table that interests us more is the TabPermission table. Let's see the fields that interest us the most from this table:

TabId:

The tab id the record is referring to. There may be more than one records per tab id in the TabPermissions table, if the tab is available to multiple roles or even to one role with multiple permissions (DNN grants VIEW and EDIT permissions to tabs).

PermissionID:

The number you see here is the type of permission specified, and is related to the Permission.PermissionId field. If you do a SELECT * FROM Permissions you'll get, among other records, the following two records:

3 SYSTEM_TAB -1 VIEW View Tab
4 SYSTEM_TAB -1 EDIT Edit Tab

For our purposes, we will need permission id 3, i.e. VIEW permissions. Remember, we need the tabs that our user is allowed to see.

RoleID:

This is where things get a bit complicated. The RoleID field is related to the Roles.RoleId field (try a SELECT * FROM Roles to see what you'll get). But here, we don't need all the roles but just the roles our user is participating in. So we need to relate this field to the UserRoles table, which holds that information. If you do a SELECT * FROM UserRoles WHERE UserId=xxx (put your user id in the place of xxx), you'll get all role participations for the specified user.

(You'll notice that the UserRoles table also has the fields ExpiryDate, IsTrialUsed and EffectiveDate. For the sake of simplicity, we'll suppose that we don't have any roles with dates set, although it would be very easy to extend our final query to include only roles that are effective in the current time period.)

In the TabPermissions table, you'll notice two additional role ids that DO NOT exist on the Roles table: -1 and -2. -2 stands for "Host", and concerns pages accessible by the host user. -1 stands for "All Users" and concerns all non-administrative pages that are accessible from the "All Users" virtual role (this role actually does not exist anywhere).

Try doing the following:

--Fetch only admin pages accessed by the Administrators role
select * from tabpermission
inner join tabs
on tabs.tabid = tabpermission.tabid
where roleid=0

--Fetch only non-admin pages accessed by all users
select * from tabpermission
inner join tabs
on tabs.tabid = tabpermission.tabid
where roleid=-1

--Fetch only host pages
select * from tabpermission
inner join tabs
on tabs.tabid = tabpermission.tabid
where roleid=-2

And observe the different results.

So what do we need in order to get the tabs that are accessible from a single, specific user? We'll take our initial query:

SELECT
    tabid,tabtitle
FROM
    Tabs
WHERE
    isDeleted=0

And we'll amend it as follows:

declare @userid int
select @userid = 2

    select
        tabs.tabid, tabs.title
    from
        tabs

inner join (
    select
        distinct tabpermission.tabid
    from
        tabpermission
        left outer join
        userroles on tabpermission.roleid = userroles.roleid
        left outer join
        users on users.userid = userroles.userid
    where
        tabpermission.permissionid=3
        and (users.userid = @userid
        or tabpermission.roleid=-1 or @userid=1)
    ) as perm
on perm.tabid=tabs.tabid

where
tabs.isdeleted=0
order by tabs.tabid ASC

What we did was to initially declare a @userid variable and give it a value (4 for our example, whatever suits you for your DNN installation - if you're making an SP out of this you can just provide the user's ID value as a parameter to the SP).

We're using the @userid variable inside our JOIN statement, which actually joins the Tabs table with the TabPermission table, but restricts the TabPermission table results to where the permission type is VIEW (id 3) and our user id is equal to @userid (users.userid = @userid) OR there are view rights for all users (tabpermission.roleid=-1) OR our user is the administrator. (@userid=1), which overrides everything else. The DISTINCT clause is used to ommit propable multiple results for the same tab.

This will essentially get us the tab records that are accessible with VIEW permissions for the given user.

As I've mentioned before, some things are not covered in this query:

Permissions to a specific user id
Roles with start and expiry dates

But in my opinion it's a good starting point for anyone who needs to create a security-aware tab retrieval query in DNN.

Please let me know what you think.

The dbo.tabs.IconFile field issue

2008-05-16T17:04:00.003+03:00

The case is fairly simple: In a DNN installation, we needed the user to use the IconFile field (i.e. the "Icon" field in the Advanced Settings section of the Page Properties page to add a custom icon to each page they made. We would then use a query created in Bi4Ce's ListX module to present a list of pages in the form of an in-page navigation menu for a specific portion of the portal.

To our horror, we discovered (using DNN version 4.8.2), that the IconFile field in the Tabs table takes two types of values: Either a value like "FileID=xxx", where xxx is the id of a record in the Files table, or the full path of the image file (Portals/0 is implied, so the file path is starting with the next sub folder).

That is, if you've set the icon to be in portals/0/myimages/myimage.jpg, you either get an entry in the Tabs table like "FileId=2312", or an entry like "myimages/myimage.jpg". At this point we are not sure why this is happening, but the truth is we don't care. We just want the path to the actual file either way.

In the case of the fileid thing, you can get the path by joining the tabs table with the files table and then the files table with the folders table (the folder path itself is in the folders table) and voila. In the case where the full path exists in the IconFile field, you obviously don't need to do anything else.

We created a query which decides on what to do and produces a result set with two fields: tabid and img where img ALWAYS contains the full path to the image file (provided that one is set for the page), regardless of whether it's in the form "FileID=xxx" or in the full path form, and I'd like to share it with you.

You can easily make a view out of it and use it where appropriate. It hasn't been tested on multi-portal installations, maybe things will differ a bit there. Comments are always welcome. Here goes:


select 
 tabid
 , case 
  when ltrim(rtrim(iconfilepath))='' 
  then folderpath+filename
  else iconfilepath
  end 
 as img

from 
(
select 

   tabid
 , taborder
 , case
  when patindex ('FileID=%', iconfile) >0
  then convert(int, replace(iconfile, 'fileid=', '')) 
  else 0
  end 
 as iconfileid
 , case
  when patindex ('FileID=%', iconfile) =0
  then iconfile
  else ''
  end 
 as iconfilepath
 
from 
 tabs 
where 
 isdeleted=0 


) mytabs

left outer join files
on files.fileid = iconfileid
left outer join folders
on files.folderid = folders.folderid

order by 
 taborder asc