In Part 4 we will be covering the DAL updates in order to properly integrate core taxonomy in your custom module. As with the other parts of this series, I ask that you please read them prior to reading this blog entry. While reading this particular entry, you may want to refer to parts 2 and 3 even though you have read them before (since lots of what is happening in this part references both of them).
You can find the previous parts of the series here:
- Part 1
- Part 2
- Part 3
DAL Updates
The DAL, or Data Abstraction Layer, in DotNetNuke (simply put) allows your module’s business logic and user interface to communicate with the data store via the use of an abstract and concrete set of providers (where most modules have a SQL Server implementation as the concrete provider only, like the Core does). As you probably noticed in Part 2, the Content Items and their create/update stored procedures are accessed via the Core API and it’s DAL (as well as Content Type). Since we need items in the data store to interact with within our DAL, we’ll start off by creating the table to hold our entity (for SQL Server 2005 or greater, which the core requires as of 5.2). Note: All items within the DAL section of this blog entry are contained in my module’s Components\Data\ directory (including my 01.00.00.SqlDataProvider and the Uninstall.SqlDataProvider file).
Sample Table Creation from SqlDataProvider:
1: CREATE TABLE {databaseOwner}[{objectQualifier}MyCompany_MyModule_Entry]
2: (
3: [EntryID] [int] IDENTITY(1,1) NOT NULL,
4: [Title] [nvarchar](200) NOT NULL,
5: [MyContent] [nvarchar](MAX) NULL,
6: [ModuleID] [int] NOT NULL,
7: [ContentItemID] [int] NULL,
8: [CreatedByUserID] [int] NULL,
9: [CreatedOnDate] [datetime] NOT NULL,
10: [LastModifiedByUserID] [int] NULL,
11: [LastModifiedOnDate] [datetime] NULL,
12: )
13: ON [PRIMARY]
14: GO
15:
16: ALTER TABLE {databaseOwner}{objectQualifier}MyCompany_MyModule_Entry
17: WITH NOCHECK
18: ADD CONSTRAINT PK_{objectQualifier}MyCompany_MyModule_Entry PRIMARY KEY CLUSTERED ( [EntryID] )
19: ON [PRIMARY]
20: GO
21:
22: ALTER TABLE {databaseOwner}{objectQualifier}MyCompany_MyModule_Entry
23: ADD CONSTRAINT FK_{objectQualifier}MyCompany_MyModule_Entry_{objectQualifier}Modules FOREIGN KEY ( [ModuleID] )
24: REFERENCES {databaseOwner}{objectQualifier}Modules ( [ModuleID] ) ON DELETE CASCADE
25: ON UPDATE CASCADE NOT FOR REPLICATION
26: GO
27:
28: ALTER TABLE {databaseOwner}{objectQualifier}MyCompany_MyModule_Entry
29: ADD CONSTRAINT FK_{objectQualifier}MyCompany_MyModule_Entry_{objectQualifier}ContentItems FOREIGN KEY ( [ContentItemID] )
30: REFERENCES {databaseOwner}{objectQualifier}ContentItems ( [ContentItemID] ) ON DELETE CASCADE
31: ON UPDATE CASCADE NOT FOR REPLICATION
32: GO
Obviously, the columns you have will vary depending on your module and entity (discussed in Part 3). Likewise, this is formatted for a DotNetNuke SqlDataProvider file (utilizing the databaseOwner and objectQualifier). The big item to note here is the ContentItemID integer data type column that also serves as a foreign key to the ContentItems table. Nothing here, besides the inclusion of the ContentItemID column, should be new anyone who has developed a DotNetNuke module before. Next, lets create a stored procedure to retrieve some records from this table.
Sample Retrieval Stored Procedure Creation from SqlDataProvider:
1: CREATE PROCEDURE {databaseOwner}[{objectQualifier}MyCompany_MyModule_Entry_GetAll]
2: @ModuleID INT,
3: @PageSize INT,
4: @PageIndex INT
5: AS
6: BEGIN
7: DECLARE @RowStart INT
8: DECLARE @RowEnd INT
9:
10: SET @RowStart = @PageSize * @PageIndex + 1 ;
11: SET @RowEnd = @RowStart + @PageSize - 1 ;
12:
13: SELECT *
14: FROM ( SELECT EntryID,
15: Title,
16: MyContent,
17: MME.ModuleID,
18: MME.CreatedByUserID,
19: MME.CreatedOnDate,
20: MME.LastModifiedByUserID,
21: MME.LastModifiedOnDate,
22: ( SELECT TotalRecords = COUNT(*)
23: FROM {databaseOwner}{objectQualifier}MyCompany_MyModule_Entry MME
24: INNER JOIN {databaseOwner}{objectQualifier}ContentItems AS CI ON MME.ContentItemId = CI.ContentItemID
25: WHERE MME.ModuleID = @ModuleID
26: ) AS TotalRecords,
27: CI.ContentItemID,
28: CI.Content,
29: CI.ContentTypeID,
30: CI.TabiD,
31: CI.ContentKey,
32: CI.Indexed,
33: ROW_NUMBER() OVER ( ORDER BY CreatedOnDate DESC) AS RowNumber
34: FROM {databaseOwner}{objectQualifier}MyCompany_MyModule_Entry MME
35: INNER JOIN {databaseOwner}{objectQualifier}ContentItems AS CI ON MME.ContentItemId = CI.ContentItemID
36: WHERE MME.ModuleID= @ModuleID
37: ) AS EntryInfo
38: WHERE RowNumber >= @RowStart
39: AND RowNumber <= @RowEnd
40: END
41: GO
As with the previous SqlDataProvider snippet, this snippet is for use with SQL 2005 or greater (because of the NVARCHAR(MAX), as well as our use of paging). This stored procedure will retrieve all records associated with a particular ModuleID that also contain a valid Content Item (via the INNER JOIN on the ContentItems table via ContentItemID) that are within our paging parameters. Again, for those who have developed a DotNetNuke module previously, nothing here should be new here except for the addition of the ContentItems table. Please note, to keep the blog as small as possible I left out all other stored procedures I would normally create (Ex. Add, Update, Delete, Get, etc.). Note, similar to our entity naming discussed in Part 3, I used the singular form when naming my table and my retrieval stored procedure (in reference to our entity). With our table created and our retrieval stored procedure created we can move into some code in our DAL that interacts with the data store. First, we will create our IDataProvider interface as shown below.
Sample Portion of Abstract Provider, IDataProvider.cs:
1: using System.Data;
2: using System;
3:
4: namespace MyCompany.MyModule.Components.Data
5: {
6: public interface IDataProvider
7: {
8: IDataReader GetAllEntries(int moduleID, int pageSize, int pageIndex);
9: }
10: }
Something that may be different from your previous module development experience is that I am using an interface here. This is because I developed my module using the MVP style, using the WebForms MVP, so I can make my module testable. Besides this changeover, which you don’t need to do to utilize ContentItems in your own module, you would need to update your Update stored procedure to pass in the ContentItemID. While you could update your Add stored procedure as well here to pass in ContentItemID I do not do this because I don’t create my ContentItem (in the ContentItems table, via the Core API) until I have a primary key for my EntryID (my entity’s primary key) which I utilize while inserting into the ContentItems table. With those updates made, we can create our concrete provider.
Sample Portion of Concrete Provider, SqlDataProvider.cs:
1: public class SqlDataProvider : IDataProvider
2: {
3: private const string ModuleQualifier = "MyCompan_MyModule_";
4:
5: // Omitted some databaseowner and objectqualifier properties, as well as a private method for setting GetFullQualifiedName.
6:
7: public IDataReader GetAllEntries(int moduleID, int pageSize, int pageIndex)
8: {
9: return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Entry_GetAll"), moduleID, pageSize, pageIndex);
10: }
11: }
As with the previous snippets, nothing here should be new for DotNetNuke module developers (outside of the MVP style). With our DAL now setup, we can move into the controller section of our module which will finally answer the question of when to interact with the Core API’s Content Items.
Controller Changes
Although my previous DAL updates showed a data retrieval stored procedures (because I felt it was important to understand because of the JOIN), I am now shifting focus to the add method (which I didn’t show an example of above). The reason for the shift of focus is due to us needing to cover when to add and update our Content Items. For your information, this file is located in my module’s Components\Controllers directory. Before we dive into more code, lets look at the basic steps the Core takes for tab and module creation:
- Create Content Item in data store, which returns ContentItemID.
- Create Tab/Module in the data store, which returns TabID/ModuleID.
- Update Content Item in data store (so we can update the TabID/ModuleID value for the associated Content Item).
The reason I wanted to bring this up before diving in is because, depending on your module, you may not need to do step 3. For modules that are only utilizing a single Content Item per module instance, step 3 is not necessary. However, modules that have multiple Content Items per module instance (blogs, forums, articles, etc.), this step will be necessary so we can update our ContentItems table’s ContentKey with our entity’s primary key value (refer to Part 2 if you forgot what I am talking about here). Now, sticking with the assumption that you developing a module that contains multiple Content Items per module instance you may be thinking that you could change the order up to the following (which you can):
- Create Entry in data store, which returns EntryID.
- Create Content Item in the data store, setting ContentKey here, which returns ContentItemID.
- Update Entry in data store (so we can update ContentItemID value associated with Entry).
Which direction you take is up to you, the developer. In either case, you are making 3 calls to the data store (2 creates and 1 update). My personal preference is the second approach and the reason being is I don’t want a Content Item created if my entity creation fails (for whatever reason, thus we have an orphan Content Item). Also doing it this way I can avoid clearing cache within my module for the entity collection by only doing a cache clearing (for my module’s collections) via the Update method since I know creating an entity in the data store will immediately be followed by updating that very same entity in the data store (I am not certain on the specifics of how the core manages the Content Item’s cache). Keeping this in mind, lets take a look at a code sample.
Sample Add Entry Method in MyModuleController:
1: EntryInfo IMyModuleController.AddEntry(EntryInfo objEntry, int tabId) {
2: var entryID = _dataProvider.AddEntry(objEntry.Title, objEntry.MyContent, objEntry.ModuleID, objEntry.CreatedByUserID, objEntry.CreatedOnDate);
3:
4: objEntry.EntryID = entryID;
5:
6: var cntTaxonomy = new Taxonomy.Content();
7: var objContentItem = cntTaxonomy.CreateContentItem(objEntry, tabId);
8:
9: objEntry.ContentItemID = objContentItem.ContentItemID;
10:
11: return objEntry;
12: }
Line 2 is probably similar to what you are doing today where we just create a new row of data in the data store and return the primary key just created. Line 7, however, is a new addition for the taxonomy integration. This calls the CreateContentItem function discussed in Part 2 of this blog series which returns the ContentItem object we just created. Next we update our objEntry’s ContentItemID so we can return the object back to where it was called from (so an update of the entity can be done). For most modules, this was originally called from the equivalent of EditEntry.ascx’s save method (MVP style is probably done from the EditEntryPresenter equivalent).
When it comes to updating the entities and associated content items, there are only two steps involved (The order in which this is done is also up to you):
- Update Entry in data store
- Update Content Item in data store
Note: If you are retro-fitting an existing module with taxonomy integration, this may require a little more than I am covering in detail here. When I built this module I knew from the start I was going to integrate it with taxonomy and never deployed the module anywhere so I didn’t encounter this scenario. If you have already deployed your module and thus have to retro-fit it, you could either create the content items via: IUpgradeable, add a check each time an item loads and then create it if it doesn’t already exist, ask users to edit existing content entries (Ex. each article) which probably isn’t practical, or just do it on the fly when necessary (Ex. as users apply tags to your Content). However you do this is up to you but please note this could alter the INNER JOIN statement I use in the retrieval stored procedures (otherwise you would not get any results prior to creating Content Items and associating the ContentItemID with your EntityID).
Hopefully, at this point, things are finally starting to come together. Based on my experience in implementing taxonomy, what was covered here was the most time consuming part of the process simply because of all the DAL updates (You pretty much have to update everything related to your entity. Maybe in DNN 6, this won’t be the case anymore.). We have covered what has to change to implement taxonomy in your module throughout all layers of your module except for the user interface. However, before we can get to that we must first touch on Terms which will be the next entry in this blog series.