To read the article online, visit http://www.4GuysFromRolla.com/webtech/chapters/CSK/ch08.2.shtml

Return to the Building Websites with the ASP.Net Community Starter Kit Sample Chapter Page
Return to the Complete Listing of Sample Chapters

Module Construction Blueprint

We will take a bottom-up approach by starting with the database work, and finishing with presentation skins and themes. We will follow the naming conventions and patterns established by the existing modules in the CSK so that the code fits well with the rest of the framework. For example, the Books module retrieves book information from the Community_Books table to populate a BookInfo component.

Thus we will use a table called Community_Faqs to populate an FaqInfo component. However, you may want to consider adding a unique identifier in case a future version of the CSK contains the module you are building. For instance, if you work for ABC Inc. you might use Community_ABCFaqs as a table name to lower the possibility of future name collisions.

We will use the following steps to build the FAQ module. You can also follow these steps in a general sense to build your own modules:

The rest of this chapter will explain each step in more detail.

The Community_Faq Table

Content that is common to all modules such as title, description, and view count resides in the Community_ContentPages table. Additional module-specific content requires a second table for storage. For the FAQ module, we can store the FAQ question in the contentPage_title field of Community_ContentPages, and the FAQ introduction in the contentPage_description field. We still need to store the FAQ answer and the additional references text for the FAQ, so we will use the following DDL to create a table:

CREATE TABLE [Community_Faqs] (
[Faq_ContentPageID] [int] NOT NULL ,
[Faq_Answer] [ntext] NOT NULL ,
[Faq_Reference] [ntext] NULL,
CONSTRAINT [PK_Community_Faqs] PRIMARY KEY CLUSTERED
(
[Faq_ContentPageID]
),
CONSTRAINT [FK_Community_Faqs_Community_ContentPages]
FOREIGN KEY
(
[Faq_ContentPageID]
) REFERENCES [Community_ContentPages] (
[contentPage_id]
) ON DELETE CASCADE
)

The naming conventions and data types we use for the table are consistent with the other modules shipped with the CSK.

We store the answer and reference data in fields of type ntext in order to support large quantities of text (up to 1 GB). Also notice how Faq_Answer is a required field but Faq_Reference may contain a NULL value. Our primary key (Faq_ContentPageID) references the additional FAQ content in the Community_ContentPages table. The foreign key constraint will ensure our referential integrity for this relationship.

Another nice design feature is the use of cascading referential integrity restraints. The ON DELETE CASCADE clause in the foreign key constraint means we will not need to write any code to delete an FAQ record from Community_Faqs.

The CSK uses the Community_ContentPagesDeleteContentPage stored procedure to delete records from the Community_ContentPages table. When the procedure removes a record from the content table, SQL Server will automatically remove the corresponding entry from the FAQ table.

The Community_Faqs Stored Procedures

The next steps in our blueprint calls for us to create stored procedures to add an FAQ, edit an FAQ, retrieve a single FAQ, and retrieve a paged and sorted list of FAQs, for a total of four procedures. All of the data access in the CSK happens via stored procedures. There is no ad hoc SQL in the code, which is a good practice from both encapsulation and security standpoints. The first procedure we will write is to add new FAQ content.

Community_FaqsAddFaq

The stored procedure for adding a new FAQ to the database is shown now. We do not need to provide a parameter for every column in the two content tables. For example, we do not need to pass a parameter to populate the contentPage_ViewCount column. Many of the columns contain a sensible default value (contentPage_ViewCount defaults to 0) or allow NULL values (contentPage_dateCommented) for new content.

CREATE PROCEDURE Community_FaqsAddFaq
(
@communityID int,
@sectionID int,
@username nvarchar(50),
@topicID int,
@question nvarchar(100),
@introduction nvarchar(500),
@metaDescription nvarchar(250),
@metaKeys nvarchar(250),
@moderationStatus int,
@answer ntext,
@reference ntext
)
AS
DECLARE @ContentPageID int
DECLARE @pageType int
SET @pageType = dbo.Community_GetPageTypeFromName('Faq')
DECLARE @userID int
SET @userID = dbo.Community_GetUserID(@communityID, @username);
BEGIN TRAN
EXEC @ContentPageID = Community_AddContentPage
@communityID,
@sectionID,
@userID,
@question,
@introduction,
@metaDescription,
@metaKeys,
@pageType,
@moderationStatus,
@topicID

INSERT Community_Faqs
(
Faq_ContentPageID,
Faq_Answer,
Faq_Reference
)
VALUES
(
@ContentPageID,
@answer,
@reference
)
COMMIT TRAN
RETURN @ContentPageID

Notice the use of two UDFs supplied with the CSK. The first UDF retrieves the page type for the content. There is a distinct page type identifier for each module (Books, Articles, Downloads, and so on) in the CSK. We will discuss page types in more detail when we create our maintenance stored procedure. A second UDF allows us to retrieve the userID by passing a communityID and username.

Since we must insert the content items into two different tables we use a transaction to make the operation atomic. Inserting records into the Community_ContentPages table occurs by invoking the Community_AddContentPage procedure passing the FAQ question as the @Title parameter and the FAQ introduction as the @Description parameter. Community_AddContentPage returns the primary key value of the newly inserted record which we will in turn use in the INSERT command for Community_Faqs.

All of the procedures that add new content records must return the primary key value of the new record as a result.

The new contentPageID value will be useful in the upper layers of software, as we will see when we write the data-access component.

Community_FaqsEditFaq

The stored procedure we use to edit an existing FAQ uses a slightly different parameter list. Obviously, some columns are immutable after we add a content page to the system (such as the section identifier). The edit procedure listing is shown below:

CREATE PROCEDURE Community_FaqsEditFaq
(
@communityID int,
@contentPageID int,
@username NVarchar(50),
@topicID int,
@question NVarchar(100),
@introduction NVarchar(500),
@metaDescription NVarchar(250),
@metaKeys NVarchar(250),
@answer Text,
@reference Text
)
AS
DECLARE @UserID int
SET @UserID = dbo.Community_GetUserID(@communityID, @username)
EXEC Community_EditContentPage
@contentPageID,
@userID,
@question,
@introduction,
@metaDescription,
@metaKeys,
@topicID
UPDATE Community_Faqs SET
Faq_Answer = @answer,
Faq_Reference = @reference
WHERE Faq_ContentPageID = @contentPageID

Again, we use a stored procedure provided by the CSK to update the Community_ContentPages pages, and then follow with an UPDATE statement for the Community_Faqs table. Unlike the procedure to add a new FAQ record, there is no transaction present to keep the two table updates atomic. We are following a pattern established in the existing module edit procedures in the CSK - none of these use a transaction. Presumably the designers found the consequences of a failure during a content edit to be considerably smaller compared to the same during content addition. We should have slightly better system throughput by avoiding SQL resource locks.

Community_FaqsGetFaqs

The next stored procedure to write is the procedure to return all FAQs for a given section in a community. The Community_GetPagedSortedContent UDF, which we covered earlier in the book, should essentially dictate the parameter list required to retrieve content. All we need to do is augment the resultset of the UDF with some FAQ-specific columns and sort by the IndexID column the UDF computes.

CREATE PROCEDURE Community_FaqsGetFaqs 
(
@communityID int,
@username NVarchar(50),
@sectionID int,
@pageSize int,
@pageIndex int,
@sortOrder NVarchar(50)
)
AS
DECLARE @currentDate DATETIME
SET @currentDate = GetUtcDate()
SELECT
null Faq_Answer,
null Faq_Reference,
Content.*
FROM
dbo.Community_GetPagedSortedContent
(
@communityID,
@username,
@sectionID,
@currentDate,
@sortOrder,
@pageSize,
@pageIndex,
default
) Content
ORDER BY
IndexID

The stored procedure uses a couple of techniques to reduce the amount of code we need to write and to reduce the amount of maintenance required in future changes. First, we use Content.* in the SELECT list to return all columns in the UDF resultset. This code is consistent with the other stored procedures within the CSK. In terms of efficiency, it is better to explicitly list all the columns required instead of having the database engine figure out the available columns. However, in this case, the CSK designers decided to lean towards maintainability. With this code you can make certain types of schema changes to the Community_ContentPages (adding a column, for example) and not need to modify and test any of the stored procedures (almost 30) working with records from this table.

The second thing to point out is the addition of two empty columns (Faq_Answer and Faq_Reference) to the resultset. Later, we will write the FaqInfo component to hold results of both this stored procedure and the next stored procedure that retrieves a single FAQ. Since we want to use the same component for both operations, we need to populate the resultset with all the columns an FaqInfo object expects to see. As these two columns can be quite large, and would never display in a summary list of FAQs, we do not want to use up resources moving these columns around for the FAQ list, we just set the values to NULL.

Community_FaqsGetFaq

The stored procedure we write to retrieve the content for a single FAQ also has one other responsibility. It needs to increment the view count for the content page and indicate the user has now read this content page. Both these tasks are accomplished by executing the CSK's Community_ContentPagesTrackStats procedure. The entire procedure is shown here:

CREATE PROCEDURE Community_FaqsGetFaq
(
@communityID INT,
@username NVarchar(50),
@contentPageID int
)
AS
DECLARE @userID INT
SET @userID = dbo.Community_GetUserID(@communityID, @username)
-- Update ViewCount and HasRead Stats
EXEC Community_ContentPagesTrackStats @userID, @contentPageID
DECLARE @currentDate DATETIME
SET @currentDate = GetUtcDate()
SELECT
Faq_Answer,
Faq_Reference,
Content.*
FROM
dbo.Community_GetContentItem(
@communityID,
@userID,
@currentDate) Content
JOIN Community_Faqs (nolock)
ON ContentPage_ID = Faq_ContentPageID
WHERE
ContentPage_ID = @contentPageID

Note that this time we actually retrieve the value for the Faq_Answer and Faq_Reference columns, as they should display at this level of detail. This requires a join to our Community_Faqs table. You'll see that we include a locking hint of nolock, which allows dirty reads but avoids any contention for the content we retrieve.

  • Read Part 3!

    Buy this Book!
    Want to read the entire book? Pick up a copy of Building Websites with the ASP.Net Community Starter Kit from Amazon.com today!


  • Article Information
    Article Title: Building Websites with the ASP.Net Community Starter Kit Sample Chapter
    Article Author: Author Unknown
    Published Date: Friday, May 21, 2004
    Article URL: http://www.4GuysFromRolla.com/webtech/chapters/CSK/ch08.2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers