Peter Donker, Bring2mind, September 2005
Introduction
Since DotNetNuke (DNN) has introduced localization features, its popularity in Western European countries has significantly increased. This is based on observation from our sales and email inbox. Since its inception, DNN has been a mostly Anglophonic affair given the fact that all documentation and administration was English. The introduction of static localization allowing the translation of the management engine has changed this. It is now possible to roll out websites that are completely non-English.
However, one final hurdle remains: to be able to have truly multilingual sites. The current (3.1) DNN framework will allow for monolinguistic non-English sites, but not multilingual ones. The ability to have multilingual sites is especially important in countries that have more than one official language. Amongst these is , the country where Bring2mind is based.
Dynamic Localization
The ability to carry content in multiple languages requires what has become known as dynamic localization. The word dynamic, here, refers to content (i.e. text) that is changed by users, not the programmers. Commonly, current multilingual sites consist of multiple shadowing sites. It can often been seen in the url: http://www.somesite.com/en/index.html would refer to the English content entry point and …/de/index.html to the French content for instance. This is a static website solution. The central idea of a portal framework such as DNN, however is to offer content tailored to the visitor on a single page. This eliminates the need/ability to have the shadowing sites. Instead, the framework engine would detect the user’s preferred language and offer the correct content.
The challenge is that we not always will have (or want to have) content for all possible languages of visitors. Should the site then show a blank page? Preferably not. Preferably we would have some ‘cascading’ mechanism whereby we would try to offer the user (1) content in his/her language, (2) content in a similar language, or finally (3) content in the portal default language.
Technical Implications
Dynamic texts are commonly stored in the database backend of DNN. If we take as an example the text/html module that comes with the core distribution, we see that it uses the HtmlText table to store the contents of the module and the text itself is in the DesktopHtml field (there is also the DesktopSummary field holding text to be localized).
The goal of this paper is to propose a solution that is generic. That is, that all parts of the framework and external additions (modules) can use this. This rules out creating shadow tables for every table in the framework that has localizable texts. All these tables would need their own methods. Rather we’d strive for a single function entry point like the GetString function for static localization.
Proposal
The proposed solution uses a single table to store any string with a locale. These can be retrieved through a single function. A SQL procedure is used and includes the logic to gracefully cascade to find the best localized text.
SQL Data
CREATE TABLE {databaseOwner}{objectQualifier}PG_Strings (
[Locale] NVARCHAR (5) NOT NULL,
[ModuleId] INT NOT NULL,
[StringName] NVARCHAR (50) NOT NULL,
[StringValue] NTEXT
) ON [PRIMARY]
GO
ALTER TABLE {databaseOwner}{objectQualifier}PG_Strings ADD CONSTRAINT
PK_{objectQualifier}PG_Strings PRIMARY KEY CLUSTERED
(
[Locale], [ModuleId], [StringName]
) ON [PRIMARY]
GO
The above will create the table. I assume here one will be familiar with creation of add/update/list procedures. I just include the special function for retrieving the right string:
CREATE PROCEDURE {databaseOwner}{objectQualifier}PG_GetStringValue
(
@ModuleId Int,
@StringName NVarChar (50),
@Locale NVarChar (5),
@DefaultLocale NVarChar (5)
)
AS
BEGIN
IF (SELECT Count(StringName) AS Hit FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale = @Locale) > 0
BEGIN
SELECT StringValue FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale = @Locale
END
ELSE
BEGIN
IF (SELECT Count(StringName) AS Hit FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale = LEFT(@Locale, 2)) > 0
BEGIN
SELECT StringValue FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale = LEFT(@Locale, 2)
END
ELSE
BEGIN
IF (SELECT Count(StringName) AS Hit FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale LIKE LEFT(@Locale, 2)+'%') > 0
BEGIN
SELECT TOP 1 StringValue FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale LIKE LEFT(@Locale, 2)+'%'
END
ELSE
BEGIN
IF (SELECT Count(StringName) AS Hit FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale = @DefaultLocale) > 0
BEGIN
SELECT TOP 1 StringValue FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale = @DefaultLocale
END
ELSE
BEGIN
IF (SELECT Count(StringName) AS Hit FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale = '') > 0
BEGIN
SELECT TOP 1 StringValue FROM {objectQualifier}PG_Strings
WHERE ModuleId = @ModuleId
AND StringName = @StringName
AND Locale = ''
END
ELSE
BEGIN
SELECT TOP 1 '' AS StringValue FROM {objectQualifier}PG_Strings
END
END
END
END
END
END
GO
Functions
The following is a regular overloaded method to call the aforementioned procedure:
Public Shared Function GetString(ByVal ModuleId As Integer, ByVal StringId As String) As String
Return GetString2(ModuleId, StringId, Thread.CurrentThread.CurrentCulture.ToString.ToLower)
End Function
Public Shared Function GetString(ByVal ModuleId As Integer, ByVal StringId As String, ByVal Locale As String) As String
Dim objPortalSettings As PortalSettings = CType(HttpContext.Current.Items("PortalSettings"), PortalSettings)
Dim defaultLanguage As String = objPortalSettings.DefaultLanguage.ToLower
Return GetString2(ModuleId, StringId, Locale, defaultLanguage)
End Function
Public Shared Function GetString(ByVal ModuleId As Integer, ByVal StringId As String, ByVal Locale As String, ByVal DefaultLocale As String) As String
Return DataProvider.Instance().GetStringValue(ModuleId, StringId, Locale, DefaultLocale)
End Function
Public Shared Sub SetString(ByVal ModuleId As Integer, ByVal StringId As String, ByVal Locale As String, ByVal Value As String)
Dim objStrings As New StringsController
Dim objString As StringInfo = objStrings.GetString(ModuleId, Locale, StringId)
If objString Is Nothing Then
objString = New StringInfo
With objString
.ModuleId = ModuleId
.StringName = StringId
.Locale = Locale
.StringValue = Value
End With
objStrings.AddString(objString)
Else
With objString
.StringValue = Value
End With
objStrings.UpdateString(objString)
End If
End Sub
Then, we can also add some helper functions to deal with tables, for instance
Public Shared Sub LocalizeDataTable(ByVal ModuleId As Integer, ByRef Table As DataTable, ByVal Columns() As String)
Dim dr As DataRow, Col As String, Key As String
For Each dr In Table.Rows
For Each Col In Columns
Key = dr.Item(Col)
dr.Item(Col) = GetString2(ModuleId, Key)
Next
Next
End Sub
Using this solution
Getting table data from the database and localizing the result:
Dim dt As DataTable = DotNetNuke.Common.ConvertDataReaderToDataTable(Data.DataProvider.Instance().GetCategoriesByPortals(PortalId))
Bring2mind.Services.Localization.Localization.LocalizeDataTable(ModuleId, dt, New String() {"Name"})
ctlCategories.ItemList = dt
Setting a single string on screen:
lblDescription.Text = Bring2mind.Services.Localization.Localization.GetString(ModuleId, objCategory.Name)
Setting the localized string:
MyNewId = objCategories.AddCategories(objCategory)
Dim Key As String = "{Localized}Category.Name." & objCategory.id
objCategory.id = MyNewId
objCategory.Name = Key
Bring2mind.Services.Localization.Localization.SetString(ModuleId, Key, System.Threading.Thread.CurrentThread.CurrentCulture.ToString, dr.Item("Name"))
objCategories.UpdateCategories(objCategory)