How to enable - disable caching programmatically and on-demand

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.

Read more...

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

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

Read more...

How to promote a regular DotNetNuke user to a superuser

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!

Read more...

Persisting large values in Module Settings when creating a custom module

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

Read more...

Creating pages based on templates - eventually!

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.

Read more...

Adding Javascript to the BODY tag in DNN

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:

<script runat="server">

  Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 

'Add your code here

    End Sub 

</script>

Read more...

Thickbox and Google Maps API - avoiding random thickbox initialization problems

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.

Read more...

Finding a tab's parent by level

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

Read more...

Copying settings between DNNArticle modules

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

Read more...

MSDN-style core DNN documentation

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

Read more...

Changing what appears on the title bar

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.

Read more...

Retrieving tabs using SQL, security-aware

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.

Read more...

The dbo.tabs.IconFile field issue

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

Read more...

Solving the Search Results problem when using the free MMLinks localizable module

(This article assumes that you have an advanced knowledge of what's going on inside the DotNetNuke portal framework and its Search indexer.)

For those of you who don't know what the MMLinks module is, go here to find out.

(waiting for you to read...)

Okay, now you know. With MMLinks, you can replace your standard Text/HTML modules with fully localizable ones, localizing both the title and the Text/HTML part. With one slight defect: Since DotNetNuke's search indexer doesn't know anything about the localized titles, it insists on presenting the default, non-localized module title in the Search Results page (that would be "MMLinks" if you leave it intact).

The reason for this is that localized titles for the MMLinks modules are kept in a different table: Delisoft_MMLinksTitleLocales. DotNetNuke doesn't take this table into consideration when indexing, so no localized titles in Search Results.

I tried to solve this problem by creating a trigger which affects the core SearchItem table. That's right, the table where all indexed items are kept. The trigger investigates whether the row being updated belongs to an MMLinks module instance (by checking the ModuleDefinitions table). If it does, it replaces the contents of the Title field with the localized MMLinks title. Since MMLinks modules produce one entry for each locale, voila. You've got localized MMLinks titles in Search Results (which, by the way, uses the contents of the SearchItem table).

I am providing this code in order to receive comments and / or improvements to it. Please do not use it anywhere near sensitive data or production databases. It may not work correctly.


So, first, a catch-all disclaimer to make the above statement even stronger :) :

CAUTION: THE TRIGGER HAS BEEN ONLY TESTED A COUPLE OF TIMES. IT IS SUGGESTED THAT YOU DO NOT USE THIS CODE IN A PRODUCTION ENVIRONMENT. IT IS STRONLGY ADVISED THAT YOU BACKUP YOUR DOTNETNUKE DATABASE FIRST. I DO NOT TAKE RESPONSIBILITY FOR ANY MALFUNCTION OR DATA LOSS THAT MAY RESULT FROM USING THE CODE PROVIDED HERE. USE AT YOUR OWN RISK.

And now, the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tool
-- Create date: March 2008
-- Description: A trigger which changes the title
-- field in the SearchItem table and replaces it
-- with the LOCALIZED title when using the MMLinks
-- module. USE AT YOUR OWN RISK.
-- =============================================
ALTER TRIGGER [dbo].[tr_Tool_setLocalizedTitleForMMLInksModule]
   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

 DECLARE @searchItemId int
 SELECT @searchItemId = searchItemId FROM Inserted

 DECLARE @moduleid int
 SELECT @moduleid = moduleid FROM Inserted

 -- Do work ONLY if entry in SearchItem table concerns an MMLinks module instance.
 IF EXISTS
  (
  SELECT
   ModuleId
  FROM
   dbo.Modules
  WHERE
   ModuleDefId in
    (
    SELECT
     ModuleDefId
    FROM
     dbo.ModuleDefinitions
    WHERE
     FriendlyName ='MMLinks'
    )
  AND ModuleId = @moduleid
  )
 BEGIN

  DECLARE @localetext nvarchar(150)

  --Get the localized text
  --In order to find the localized text, we use a trick.
  --MMLinks fillds the SearchKey column in the SearchItem table with the
  --moduleId concatenated with the locale, in the form xxx-yyy. We use this
  --information to track the correct entry in the MMLinksTitleLocales table.
  
  SELECT
   @localetext = LocaleText
  FROM
   dbo.Delisoft_MMLinksTitleLocales
  WHERE
   (CONVERT (varchar(5), ModuleId)+'-'+ LocaleKey) =
   (SELECT SearchKey FROM SearchItem WHERE SearchItemID = @searchitemid)

 
  --Once we have the localized text, do the update.
  IF @localetext IS NOT NULL AND rtrim(ltrim(@localetext))<>''
  BEGIN
   UPDATE
    dbo.SearchItem
   SET
    Title = @localetext
   WHERE SearchItemId = @searchitemid
  END

END


 SET NOCOUNT OFF
END

If you want to see immediate results, just delete everything in your SearchItem table and do a content re-index from the Host Search Admin menu.

I'm anxiously waiting for comments.

Read more...