Let start with the Database:
From 03.01.00.SqlDataProvider
Legacy Table Definition:
CREATE TABLE {databaseOwner}[{objectQualifier}Links]
(
[ItemID] [int] NOT NULL IDENTITY(0, 1),
[ModuleID] [int] NOT NULL,
[CreatedByUser] [nvarchar] (100) NULL,
[CreatedDate] [datetime] NULL,
[Title] [nvarchar] (100) NULL,
[Url] [nvarchar] (250) NULL,
[ViewOrder] [int] NULL,
[Description] [nvarchar] (2000) NULL
)
ALTER TABLE {databaseOwner}[{objectQualifier}Links] ADD PRIMARY KEY NONCLUSTERED ([ItemID])
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}Links] ON {databaseOwner}[{objectQualifier}Links] ([ModuleID])
ALTER TABLE {databaseOwner}[{objectQualifier}Links] WITH NOCHECK ADD FOREIGN KEY ([ModuleID]) REFERENCES {databaseOwner}[{objectQualifier}Modules] ([ModuleID]) ON DELETE CASCADE NOT FOR REPLICATION
Problems:
- CreatedByUser - is defined as nvarchar(100) even though it actually contains a UserID. This is a legacy issue from back in the IBuySpy Portal where modules used to store Usernames in the CreatedByUser field ( changed to UserID in DNN 1.0 ). This column poses a significant performance hit when we do a join with the Users table - since the database needs to deal with associating an nvarchar(100) with an int ( the fact that behaviorally this field actually contains the "Last Modified By UserID" value rather than the "Created By userID" is irrelevant to this discussion ).
Legacy Stored Procedure:
CREATE procedure {databaseOwner}{objectQualifier}GetLink
@ItemId int,
@ModuleId int
as
select
{objectQualifier}Links.ItemId,
{objectQualifier}Links.ModuleId,
{objectQualifier}Links.Title,
'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Links.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
{objectQualifier}Links.ViewOrder,
{objectQualifier}Links.Description,
'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
{objectQualifier}Links.CreatedDate,
{objectQualifier}UrlTracking.TrackClicks,
{objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Links
left outer join {objectQualifier}Users on {objectQualifier}Links.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}UrlTracking on {objectQualifier}Links.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
left outer join {objectQualifier}Files on {objectQualifier}Links.URL = 'fileid=' +
convert(varchar,{objectQualifier}Files.FileID)
where {objectQualifier}Links.ItemId = @ItemId
and {objectQualifier}Links.ModuleId = @ModuleId
Problems:
- URL - since the URL field can contain a number of "values" ( www.domain.com, TabID, "FileID=##" ) it does a join with the Files table using expensive string functions to try and get the FolderName/FileName.
- CreatedByUser - using a join with the Users table it pulls back the FirstName + ' ' + LastName of the User into the CreatedByUser field ( an expensive join for the reasons noted above ). This value is used for audit purposes. However, using a hardcoded format for the Users name is not good practice for some Eastern cultures ( ie. Localization ).
Note:
- LEFT OUTER JOIN - these joins are more expensive than INNER JOINS; however, they are required because we do not enforce referential integrity between the Module tables and the Core tables ( loose coupling ). An example of why this is required is if a User is deleted but their UserID is stored in the Modules CreatedByUser field - we still want the Link records to be selected, and an INNER JOIN on Users would exclude the records from the query.
- Lazy Loading - we typically use the same business object definition for loading a single object as we do for loading a collection of objects. However this does not mean that all of the business object properties need to be fully initialized with values. In the case of many Modules, the GetRecords stored procedure is designed to get a collection of records from the database - but there is no need for the module to load all the field values as many are not used in the default view.
Modifications:
From 03.03.00.SqlDataProvider
The following statements are used to convert the CreatedByUser column from an nvarchar(100) to an int. Note: I prefer NOT to use SQL Enterprise Manager to generate a script in this case as it always generates a script which drops the ENTIRE table and then recreates it. When doing work in the core, this technique can cause serious problems if there are third party modules which have added contraints to core tables ( as the core does not know about them ). As a result I prefer to write my own SQL script which simply adds a new column, transfers the data, drops the old column, creates a new column with the old name, transfers the data back, and drops the redundant column.
/** Change CreatedByUser column to an int for performance **/
ALTER TABLE {databaseOwner}{objectQualifier}Links ADD
CreatedByUserID int NULL
GO
update {databaseOwner}{objectQualifier}Links
set CreatedByUserID = convert(int,CreatedByUser)
GO
ALTER TABLE {databaseOwner}{objectQualifier}Links
DROP COLUMN CreatedByUser
GO
ALTER TABLE {databaseOwner}{objectQualifier}Links ADD
CreatedByUser int NOT NULL CONSTRAINT DF_{objectQualifier}Links_CreatedByUser DEFAULT 0
GO
update {databaseOwner}{objectQualifier}Links
set CreatedByUser = CreatedByUserID
GO
ALTER TABLE {databaseOwner}{objectQualifier}Links
DROP COLUMN CreatedByUserID
GO
ALTER TABLE {databaseOwner}{objectQualifier}Links
DROP CONSTRAINT DF_{objectQualifier}Links_CreatedByUser
GO
The following stored procedure is optimized for performance. Notice that joins are minimized and efficient and there are no expensive string functions. Also notice the use of "with (nolock)" which instructs SQL Server to ignore locks and read directly from the tables.
CREATE procedure {databaseOwner}{objectQualifier}GetLink
@ItemId int,
@ModuleId int
as
select {objectQualifier}Links.ItemId,
{objectQualifier}Links.ModuleId,
{objectQualifier}Links.Title,
{objectQualifier}Links.URL,
{objectQualifier}Links.ViewOrder,
{objectQualifier}Links.Description,
{objectQualifier}Links.CreatedByUser,
{objectQualifier}Links.CreatedDate,
{objectQualifier}UrlTracking.TrackClicks,
{objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Links with (nolock)
left outer join {objectQualifier}UrlTracking on {objectQualifier}Links.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
where {objectQualifier}Links.ItemId = @ItemId
and {objectQualifier}Links.ModuleId = @ModuleId
There may be a need to join to the Users table in some Module circumstances. Since the CreatedByUser is now defined as an Integer, this should be fine as the database can now join these tables efficiently.
Data Access Layer:
Change the CreatedByUser parameter to an Integer for type safety.
Legacy DAL Method:
Public Overrides Function AddLink(ByVal ModuleId As Integer, ByVal UserName As String, ByVal Title As String, ByVal Url As String, ByVal ViewOrder As String, ByVal Description As String) As Integer
Return CType(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner & ObjectQualifier & "AddLink", ModuleId, UserName, Title, Url, GetNull(ViewOrder), Description), Integer)
End Function
Enhanced DAL Method:
Public Overrides Function AddLink(ByVal ModuleId As Integer, ByVal UserId As Integer, ByVal Title As String, ByVal Url As String, ByVal ViewOrder As String, ByVal Description As String) As Integer
Return CType(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner & ObjectQualifier & "AddLink", ModuleId, UserId, Title, Url, GetNull(ViewOrder), Description), Integer)
End Function
When the Modules were converted to Private Assembly projects, they were given their own DataProviders. Unfortunately, the constructor for the DataProvider references the ConnectionString setting in the web.config directly. This is bad practice as the DataProvider should reference the Core GetConnectionString() method so that they are insulated from config changes.
Legacy:
_connectionString = System.Configuration.ConfigurationSettings.AppSettings(objProvider.Attributes("connectionStringName"))
New:
_connectionString = Config.GetConnectionString()
( You will also need to add an Imports DotNetNuke.Common.Utilities )
Business Logic Layer:
Change LinkInfo so that the CreatedByUser property is an Integer ( Get/Set ):
Legacy
Private _CreatedByUser As String
Enhanced:
Private _CreatedByUser As Integer
( This results in some minor changes in the LinkController.vb class as well to deal with the Type change )
User Interface:
Links.ascx
The is no change required as it leverages all of the Core methods correctly already. In terms of supporting the new Secure Folder options, the following method provides this functionality with no code changes:
NavigateUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem,"Url"),DataBinder.Eval(Container.DataItem,"TrackClicks")) %>'
Public Function FormatURL(ByVal Link As String, ByVal TrackClicks As Boolean) As String
Return Common.Globals.LinkClick(Link, TabId, ModuleId, TrackClicks)
End Function
( the Link value passed for File links will "FileID=##" - since this is what is stored in the Links.Url field. The core LinkClick() method generates the proper link to use the File Server ).
EditLinks.ascx
The module no longer retrieves the users FirstName + ' ' + LastName for audit purposes - instead it retrieves a UserID value. However there is no change required to the module, as the AuditControl will take care of loading the User record and displaying the users DisplayName when required.
ctlAudit.CreatedByUser = objLink.CreatedByUser.ToString
ctlAudit.CreatedDate = objLink.CreatedDate.ToString
DotNetNuke has supported module caching for a long time - and there are some significant performance benefits of using it for semi-static information. This is the case in the Links module. However, the problem for administrators is that once module caching is enabled, they need to remember to manually Clear the Cache after doing content updates. This is not intuitive and leads to support issues ( ie. why did my update not take effect? ). As a result in DNN 3.x we added a core framework method in PortalModuleBase which can be used to programmatically refresh the module cache. Therefore, in any method where the underlying data is being changed ( add, edit, delete, and display settings ), the module should call the SynchronizeModule() method. This eliminates the need for an administrator to manually clear the cache.
Private Sub cmdDelete_Click(ByVal sender As Object, ByVal e As EventArgs) Handles cmdDelete.Click
Try
If itemId <> -1 Then
Dim links As New LinkController
links.DeleteLink(itemId)
SynchronizeModule()
End If
' redirect to home page
Response.Redirect(NavigateURL(), True)
Catch exc As Exception ' Module failed to load
ProcessModuleLoadException(Me, exc)
End Try
End Sub
Packaging:
In DotNetNuke 3.x we added a DefaultCacheTime to the ModuleDefinitions table. This field is used as a default value for CacheTime whenever a new instance of the module is added to a page ( generally this simplifies the application for the administrator as they do not understand what "module caching" means ). This field can be specified in the Module Manifest ( *.dnn ).
<modules>
<module>
<friendlyname>Links</friendlyname>
<cachetime>60</cachetime>
<controls>
Note: Module Caching is not applicable to all modules. It works well for modules which are not interactive.