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.
Initializing the FAQ Module
Every community module has a corresponding maintenance stored procedure to populate the database with settings required for the module to work. Specifically, we need to register content page types by inserting two records into Community_PageTypes: one for an FAQ section page (to display a list of FAQs) and one for an FAQ page (showing a single FAQ in detail). We call the procedure Community_MaintenanceInitializeFaqs, following the existing CSK naming convention. An excerpt registering the FAQ section page type is shown here:
IF NOT EXISTS (SELECT * FROM Community_PageTypes
WHERE pageType_Name='Faq Section')
BEGIN
INSERT Community_PageTypes
(
pageType_name,
pageType_description,
pageType_pageContent,
pageType_IsSectionType,
pageType_ServiceSelect
)
VALUES
(
'FAQ Section',
'Contains FAQs in a question and answer style format',
'ASPNET.StarterKit.Communities.Faqs.FaqSection',
1,
'Community_FaqsServiceSelect'
)
END
ELSE
PRINT 'WARNING: The FAQ Module has already been registered.'
| The CSK caches data from Community_NamePages so as to retrieve the data only once. If you make modifications to the table, you'll need to restart the web application for the changes to take effect in the CSK. |
The maintenance stored procedure also needs to register the named pages (static content) for the new module. Named pages for the FAQ section will include the page to add an FAQ and a page to edit an FAQ. You'll have to choose your page names at this point and use the same names later when you create the ASPX file.
Here's an excerpt from Community_MaintenanceInitializeFaqs to add a named page for adding FAQs:
IF NOT EXISTS (SELECT * FROM Community_NamedPages
WHERE namedPage_Path='/Faqs_AddFaq.aspx')
BEGIN
INSERT Community_NamedPages
(
namedPage_name,
namedPage_path,
namedPage_pageContent,
namedPage_title,
namedPage_description,
namedPage_sortOrder,
namedPage_isVisible,
namedPage_menuID
)
VALUES
(
'AddFaq',
'/Faqs_AddFaq.aspx',
'ASPNET.StarterKit.Communities.Faqs.AddFaq',
'Add FAQ',
'Enables users to add a new FAQ',
0,
1,
0
)
END
ELSE
PRINT 'WARNING: /Faqs_AddFaq.aspx has already been registered
as a NamedPage.'
The namedPage_pageContent parameter is the name of the class that the CSK will instantiate as the code-behind logic for the page. The name includes the full namespace qualifier ASPNET.StarterKit.Communities.Faqs.AddFaq.
The maintenance stored procedure needs to execute during the database setup. We will take a look at how to do this in Chapter 11.
FAQ Components
The C# code for our FAQ module will reside in the Engine\Modules\Faqs directory. First, we will write out helper components and place these in a Components directory. Each module in the CSK places components inside a distinct namespace below ASPNET.StarterKit.Communities, and the existing modules use the name of the module as the additional namespace qualifier (Faqs).
FaqInfo
FaqInfo class extends the ContentInfo class to offer data properties specific to an FAQ. The code for this class is shown as follows:
using System;
using System.Data.SqlClient;
namespace ASPNET.StarterKit.Communities.Faqs
{
public class FaqInfo : ContentInfo
{
public FaqInfo(SqlDataReader dr) : base(dr)
{
if(dr["Faq_Answer"] != DBNull.Value)
{
_answerText = (string)dr["Faq_Answer"];
}
if(dr["Faq_Reference"] != DBNull.Value)
{
_referenceText = (string)dr["Faq_Reference"];
}
}
public string AnswerText
{
get { return _answerText; }
set { _answerText = value; }
}
public string ReferenceText
{
get { return _referenceText; }
set { _referenceText = value; }
}
public string QuestionText
{
get { return base.Title; }
set { base.Title = value; }
}
public string IntroText
{
get { return base.BriefDescription; }
set { base.BriefDescription = value; }
}
private string _answerText;
private string _referenceText;
}
}
FaqInfo expects initialization with an instance of the SqlDataReader class. We will be writing the data-access code to create a SqlDataReader in our next class.
FaqUtility
Following the patterns set forth in the rest of the CSK, we will put all of our data-access routines into static methods of a utility class. There should be one static method available for each of the FAQ-related stored procedures (with the exception of the maintenance stored procedure, which we should not need to invoke during regular operations of the community site but only during setup). Each of these routines will need to map incoming variables to stored procedure parameters and execute the procedure.
Here's the AddFaq method:
public static int AddFaq(
string username,
int sectionID,
int topicID,
string question,
string introduction,
string answer,
string reference,
int moderationStatus)
{
SqlConnection conPortal = new SqlConnection(
CommunityGlobals.ConnectionString);
SqlCommand cmdAdd = new SqlCommand(
"Community_FaqsAddFaq", conPortal);
cmdAdd.CommandType = CommandType.StoredProcedure;
cmdAdd.Parameters.Add("@RETURN_VALUE",
SqlDbType.Int).Direction =
ParameterDirection.ReturnValue;
cmdAdd.Parameters.Add("@communityID",
CommunityGlobals.CommunityID);
cmdAdd.Parameters.Add("@sectionID", sectionID);
cmdAdd.Parameters.Add("@username", username);
cmdAdd.Parameters.Add("@topicID", topicID);
cmdAdd.Parameters.Add("@question", question);
cmdAdd.Parameters.Add("@introduction", introduction);
cmdAdd.Parameters.Add("@metaDescription",
ContentPageUtility.CalculateMetaDescription(introduction));
cmdAdd.Parameters.Add("@metaKeys",
ContentPageUtility.CalculateMetaKeys(introduction));
cmdAdd.Parameters.Add("@moderationStatus", moderationStatus );
cmdAdd.Parameters.Add("@answer", SqlDbType.NText);
cmdAdd.Parameters.Add("@reference", SqlDbType.NText);
cmdAdd.Parameters["@answer"].Value = answer;
cmdAdd.Parameters["@reference"].Value = reference;
conPortal.Open();
cmdAdd.ExecuteNonQuery();
int result = (int)cmdAdd.Parameters["@RETURN_VALUE"].Value;
SearchUtility.AddSearchKeys(conPortal, sectionID, result,
question, introduction);
conPortal.Close();
return result;
}
Notice that the AddFaq method also generates the search keys for the content using the SearchUtility class, and the newly created identifier of the content returned by the stored procedure we reviewed earlier.
The EditFaq method almost duplicates the AddFaq method except for calling a different stored procedure and using EditSearchKeys on the SearchUtility class to update the FAQ search keys.
One improvement you might consider making to the CSK is adding a try catch finally statement to ensure the database connection will always invoke the Close method, even in the face of an exception. The chances of an exception are small, but on a high volume community site, you cannot afford the opportunity to waste database connections.
The other two methods in FaqUtility are GetFaqs and GetFaqInfo. GetFaqs loops through records in a SqlDataReader to return an ArrayList of FaqInfo objects, while GetFaqInfo expects only a single record in the database results and returns a single new FaqInfo object. These two methods from the class are shown here:
public static ContentInfo GetFaqInfo(string username,
int contentPageID)
{
FaqInfo faq = null;
SqlConnection conPortal = new SqlConnection(
CommunityGlobals.ConnectionString);
SqlCommand cmdGet = new SqlCommand(
"Community_FaqsGetFaq", conPortal);
cmdGet.CommandType = CommandType.StoredProcedure;
cmdGet.Parameters.Add(
"@communityID", CommunityGlobals.CommunityID);
cmdGet.Parameters.Add("@username", username);
cmdGet.Parameters.Add("@contentPageID", contentPageID);
conPortal.Open();
SqlDataReader dr = cmdGet.ExecuteReader();
if (dr.Read())
faq = new FaqInfo(dr);
conPortal.Close();
return faq;
}
public static ArrayList GetFaqs(string username, int sectionID,
int pageSize, int pageIndex,
string sortOrder)
{
SqlConnection conPortal = new
SqlConnection(CommunityGlobals.ConnectionString);
SqlCommand cmdGet = new SqlCommand( "Community_FaqsGetFaqs",
conPortal);
cmdGet.CommandType = CommandType.StoredProcedure;
cmdGet.Parameters.Add("@communityID",
CommunityGlobals.CommunityID);
cmdGet.Parameters.Add("@username", username);
cmdGet.Parameters.Add("@sectionID", sectionID);
cmdGet.Parameters.Add("@pageSize", pageSize);
cmdGet.Parameters.Add("@pageIndex", pageIndex);
cmdGet.Parameters.Add("@sortOrder", sortOrder);
ArrayList faqs = new ArrayList();
conPortal.Open();
SqlDataReader dr = cmdGet.ExecuteReader();
while (dr.Read())
faqs.Add(new FaqInfo(dr));
conPortal.Close();
return faqs;
}
It is important for GetFaqInfo to use the return value and parameter list shown above. The framework should invoke these methods through a delegate and the signatures must match. We will see how this works when we write the content pages.
Our data-access layer is now complete. If you build a module in this fashion, you should be able to compile the solution at this time to resolve any errors. You might consider writing a driver page to exercise the four static methods in FaqUtility and verify the results by looking in the Community_Faqs and Community_ContentPages tables of the database.
Digg
del.icio.us
Newsvine
Facebook
Google
LinkedIn
MySpace
Reddit
Slashdot
StumbleUpon
Technorati
Twitter
Windows Live
YahooBuzz
FriendFeed
Comments
There are no comments yet. Be the first to comment!