Modifying the Wrox CMS
5.With the business and data access layer code written, the next step is to change the database. Open the Database Explorer in Visual Web Developer and create a new table. (Right-click the Tables node in your database and choose Add New Table.) Create a table with the following columns:
Column Name |
Data Type |
Allow Nulls |
Remarks |
|
|
|
|
Id |
int |
No |
Make this column an Identity column by |
|
|
|
setting Identity Specification to True on the |
|
|
|
Column Properties dialog. Also make this |
|
|
|
column the primary key by selecting the |
|
|
|
column and then clicking the Key icon on |
|
|
|
the toolbar. |
ContentId |
int |
No |
This column holds the Id of the content |
|
|
|
item being counted. |
PageViews |
int |
No |
This column holds the number of items the |
|
|
|
content item has been viewed. |
|
|
|
|
Save the table as PageView.
6.To ensure the ContentId column can only hold IDs of articles that exist in the Content table, you need to create a relation between the two tables. To do that, right-click the Database Diagrams node in the Database Explorer and choose Add New Diagram. If this is the first diagram you are adding to the database, Visual Web Developer offers to create a few required tables and procedures. Click Yes to have those objects created.
7.In the Add Table dialog, add the Content table and the PageView table you just created.
8.Drag the Id column of the Content table onto the ContentId column of the PageView table. A dialog appears that allows you to define the behavior of the relations. The defaults are fine, so click OK twice to create the relation.
9.When you now save the diagram pressing Ctrl+S, the relation is saved in the database as well.
10.Just like all the other data access code, the PageView table will be updated with a stored procedure. Right-click the Stored Procedures node and choose Add New Stored Procedure. Add the following code that inserts a new record in the PageView table only the first time a specific content item is requested, and updates that record on all subsequent requests. Call the procedure sprocPageViewUpdateSingleItem:
CREATE PROCEDURE sprocPageViewUpdateSingleItem
@contentId int
AS
IF NOT EXISTS (SELECT 1 FROM PageView WHERE ContentId = @contentId)
BEGIN
INSERT INTO PageView (ContentId, PageViews) VALUES (@contentId, 1) END
ELSE
BEGIN
UPDATE PageView SET PageViews = PageViews + 1 WHERE ContentId = @contentId END
Chapter 5
11.The final step in the page view counter process is to modify the ContentDetail.aspx page in the root of the site so it updates the PageView table in the database. In that page, right below the line that sets the BodyText label, add the following code:
litIntroText.Text = contentItem.IntroText litBodyText.Text = contentItem.BodyText Logging.LogContentItemRead(contentId)
End If
12.Finally, save any file you may have open, and then open the site in your browser. Select a content type, then a category, and click one of the content items. Repeat this process for a few other items in the site. When you now look in the PageView table in the database, you’ll see the content items you viewed, and the number of times you viewed each item.
The next step in the walkthrough is displaying the page count on each content detail page. The fix for this requires four changes: first you’ll need to create a PageView property on the Content class, and then add support for this property in the GetItem method in the ContentDB class. The third change is in the stored procedure that gets the content item from the database. Finally, you need to display the number of page views on the content detail page, so a visitor can see how popular a particular item is. The next portion of this walkthrough guides you through each of these steps:
1.Open the Content class in the BusinessLogic folder and add a private Integer field called _pageView at the top of the file. At the end of the file, before the public methods, add a public property called PageView that uses the _pageView backing variable:
Public Property PageView() As Integer
Get
Return _pageView
End Get
Set(ByVal value As Integer) _pageView = value
End Set
End Property
2.Open the file ContentDB.vb file in the DataAccess folder and in the section that stores the values from the database in the public properties for the Content item, add a line that assigns the PageView property a value only when the item returned from the database does not contain a NULL value, like this:
theContentItem.Visible = _ myReader.GetBoolean(myReader.GetOrdinal(“Visible”))
If Not myReader.IsDBNull(myReader.GetOrdinal(“PageViews”)) Then theContentItem.PageView = myReader.GetInt32(myReader.GetOrdinal(“PageViews”))
End If End If
3.Open the stored procedure called sprocContentSelectSingelItem and add the PageView’s column from the PageView table to the SELECT list. Don’t forget to add a comma after Content
.Visible. Then modify the FROM clause so it uses a LEFT OUTER JOIN to link the Content table to the PageView table. It’s important to use a left join because the first time the content item is viewed, there is no matching record in the PageView table. With a normal INNER JOIN, this would prevent the entire content item from being returned. With the left join, the content item is returned, regardless of the presence of a record in the PageView table. Your stored procedure should contain the following code:
Modifying the Wrox CMS
Content.Visible,
PageView.PageViews
FROM
Content
LEFT OUTER JOIN PageView ON Content.Id = PageView.ContentId
WHERE
4.Next you should modify the ContentDetail.aspx page. First, in the markup of the page, right below the <h1> tag type some descriptive text like “This page has been viewed times”. Next, drag an <asp:Literal> control from the toolbox between the words viewed and times. Call this literal control litPageView. You can format the text in any way you want; for example, you can wrap it in a <div> tag with a class applied, add <br /> tags before and after it, and so on.
5.Switch to the code-behind for the page and then assign the Text property of the Literal control the value from the PageView property of the Content object, just as is done with the other properties. However, because the PageView property is an Integer, and the Text property of the Literal control is a string, you’ll need to convert the PageView to a string first:
litBodyText.Text = contentItem.BodyText litPageView.Text = contentItem.PageView.ToString() Logging.LogContentItemRead(contentId)
6.Finally, save all files you may have open and browse to the site by pressing Ctrl+F5. Select a content type and a category and then open a content item. You’ll see the number of times the item has been viewed. Refresh the page a couple of times in your browser and you’ll see the page counter increase, as shown in Figure 5-1.
Figure 5-1
With this modification in place, you have a nice mechanism to track the usage and popularity of the content items you publish on your site. You can even extend this modification, by adding a page to the Management section that lists all the content items and the number of page views they have received.
6
Modifying the Wrox Blog
Since blogging is in fact a relatively simple concept, the Wrox Blog is already pretty feature complete. However, there are still a few modifications you can make to the Wrox Blog application:
1.Implement paging. The BlogEntries user control now displays all the blog entries that have been published within a certain category. If you blog a lot, this list may grow very long, making it hard to load and read the page. By using paging, where you show only, say, 20 records per page, things become a lot easier for the user.
2.Implement a detail page or view. The BlogEntries control now displays all the information of each blog entry, including its publication date, title, and body text. You could create a new panel on the BlogEntries control that displays the body text for a single blog item using GetBlogEntry. Then you can remove it from the blog list, and add a link like “See full blog” that shows the body text as well.
3.User Comments. One of the reasons why blogs are so popular is because everyone can create them, but more importantly, everyone can respond to them. User comments can really contribute to the impact a blog may have.
In the next section, you’ll walk through implementing the user comments feature. This feature requires three changes. First, you’ll need to change the BlogEntries control so it displays Add Comment and View Comments links. Next, you’ll need to create a form that allows a user to enter a simple comment that is saved in the database. The final change involves adding code that retrieves the comments from the database and displays them on the site.
1.Open the BlogEntries.ascx user control and just before the closing tag of the ItemTemplate of the DataList, add two link buttons used to show the comments and to enter a comment. Link the CommandArgument of each link to the ID of the blog entry, just as is done with the Edit link. You should end up with code like this:
<asp:LinkButton id=”lnkAddComment” runat=”server” CommandName=”AddComment” Text=”Add Comment” CssClass=”EditLink” CommandArgument=’<%#Eval(“Id”)%>’>
</asp:LinkButton>
<asp:LinkButton id=”lnkShowComments” runat=”server” CommandName=”ShowComments” Text=”Show Comments” CssClass=”EditLink”
CommandArgument=’<%#Eval(“Id”)%>’>
</asp:LinkButton>
Chapter 6
2.Add two new panels right below the pnlAddEditBlogEntry panel, and call them pnlAdd Comment and pnlShowComments. Make the panels hidden by default by setting their Visible property to False.
3.Write an event handler for the DataList control’s ItemCommand that checks the CommandName and then shows the appropriate panel. Note that this is a different handler than the EditCommand you saw earlier. Your code should look like this:
Protected Sub dlBlogEntries_ItemCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) _ Handles dlBlogEntries.ItemCommand
pnlAddEditBlogEntry.Visible = False pnlBlogEntries.Visible = False Select Case e.CommandName.ToLower()
Case “addcomment”
pnlAddComment.Visible = True ViewState(“BlogEntryCommentId”) = e.CommandArgument
Case “showcomments” pnlShowComments.Visible = True ‘ TODO
End Select End Sub
When you now open the Wrox Blog in your browser, you can click the Add Comment or Show Comments links. The list with blog entries will disappear, and show one of the (empty) panels instead.
Next it’s time to create functionality that allows a user to create a new comment. You should start by adding a table called Comments to the database, together with a stored procedure (or query) to insert the new comment.
1.Open up the database (either through Visual Web Developer for a SQL Server database or directly in Microsoft Access when you’re using an Access database for the blog) and add a new table called Comment with the following specifications:
Column Name |
Data Type |
Data Type |
|
|
SQL Server |
Microsoft Access |
Description |
Id |
int (Identity and |
AutoNumber |
This is the unique ID of |
|
primary key) |
(primary key) |
the comment and will |
|
|
|
automatically get a |
|
|
|
value whenever a new |
|
|
|
comment is inserted. |
PostersName |
nvarchar(50) |
Text (50) |
The name of the poster |
|
|
|
of the comment. |
Body |
nvarchar(MAX) |
Memo |
The comment itself. |
BlogEntryId |
int |
Number |
The ID of the blog |
|
|
|
entry this comment |
|
|
|
belongs to. |
|
|
|
|
Modifying the Wrox Blog
2.Next, add a stored procedure (if you’re using SQL Server) or a query (if you’re using an Access database) to the database called sprocBlogEntryInsertComment that inserts a new comment for a blog entry. For SQL Server, the code should look like this:
CREATE PROCEDURE sprocBlogEntryInsertComment
@postersName nvarchar(50), @body nvarchar(MAX), @blogEntryId int
AS
INSERT INTO
Comment
(
PostersName,
Body, BlogEntryId
)
VALUES
(
@postersName,
@body,
@blogEntryId
)
For Microsoft Access the query should look like this:
INSERT INTO Comment (PostersName, Body, BlogEntryId) VALUES(?, ?, ?)
3.Add some controls to the panel pnlAddComment you added earlier so a user can enter a name and the comment. You’ll need a text box for the poster’s name and one for the actual comment. If you want, you can also add validation controls to ensure that required fields are filled in. You also need a button that saves the comment in the database. The code for the Save button’s Click event should look like this:
Protected Sub btnAddComment_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles btnAddComment.Click
BlogManager.SaveComment(txtPostersName.Text, _
txtCommentBody.Text, Convert.ToInt32(ViewState(“BlogEntryCommentId”))) pnlAddComment.Visible = False
pnlBlogEntries.Visible = True txtPostersName.Text = “” txtCommentBody.Text = “” LoadData()
End Sub
4.The previous code calls a method on the BlogManager class in the business layer that simply forwards the data to a method in the data access layer. The business layer code should look like this:
Public Shared Sub SaveComment(ByVal postersName As String, _
ByVal body As String, ByVal blogEntryId As Integer)
BlogManagerDB.SaveComment(postersName, body, blogEntryId)
End Sub
Chapter 6
While the method in the data access layer looks like this:
Public Shared Sub SaveComment(ByVal postersName As String, _ ByVal body As String, ByVal blogEntryId As Integer)
Dim myFactory As DbProviderFactory = DbProviderFactories.GetFactory( _ AppConfiguration.ConnectionStringSettings.ProviderName)
Using myConnection As DbConnection = myFactory.CreateConnection()
myConnection.ConnectionString = _ AppConfiguration.ConnectionStringSettings.ConnectionString
myConnection.Open()
Dim myCommand As DbCommand = myConnection.CreateCommand()
myCommand.CommandText = “sprocBlogEntryInsertComment” myCommand.CommandType = CommandType.StoredProcedure
Dim param As DbParameter
param = myCommand.CreateParameter()
param.ParameterName = DalHelpers.ReturnCommandParamName(“postersName”) param.DbType = DbType.String
param.Value = postersName myCommand.Parameters.Add(param)
param = myCommand.CreateParameter()
param.ParameterName = DalHelpers.ReturnCommandParamName(“body”) param.DbType = DbType.String
param.Value = body myCommand.Parameters.Add(param)
param = myCommand.CreateParameter()
param.ParameterName = DalHelpers.ReturnCommandParamName(“blogEntryId”) param.DbType = DbType.Int32
param.Value = blogEntryId myCommand.Parameters.Add(param)
myCommand.ExecuteNonQuery() End Using
End Sub
This code inserts the new comment in the database and associates it with a blog entry through the BlogEntryId column. This code should look really familiar by now.
The final step in the process is displaying the comments that belong to a specific blog entry. To do that, you need to add a DataList control to the panel pnlShowComments and bind it to a DataSet returned from the business and data access layer whenever the Show Comments button is clicked.
1.Start by adding a DataList control to the panel pnlShowComments in the BlogEntries.ascx control. Add an ItemTemplate inside the DataList control’s tags and then add some data binding code to display the comment’s title and text, like this:
<asp:DataList ID=”lstComments” runat=”server”>
Modifying the Wrox Blog
<ItemTemplate>
<h2>Posted by <%#Eval(“PostersName”)%></h2>
<div><%#Eval(“Body”)%></div>
</ItemTemplate>
</asp:DataList>
2.Next, add a method to both the BlogManager and BlogManagerDB classes in the business layer and the data access layer, respectively, and call them GetComments. Make sure the method accepts a blogEntryId as an Integer and returns a DataSet. The method in the business layer should call the one in the data access layer, just as most of the other business layer methods in this chapter. GetComments in the data access layer should look like this:
Public Shared Function GetComments(ByVal blogEntryId As Integer) As DataSet Dim myDataSet As DataSet = New DataSet()
Dim myFactory As DbProviderFactory = DbProviderFactories.GetFactory( _ AppConfiguration.ConnectionStringSettings.ProviderName)
Using myConnection As DbConnection = myFactory.CreateConnection() myConnection.ConnectionString = _
AppConfiguration.ConnectionStringSettings.ConnectionString
myConnection.Open()
Dim myCommand As DbCommand = myConnection.CreateCommand()
myCommand.CommandText = “sprocCommentSelectList” myCommand.CommandType = CommandType.StoredProcedure
Dim param As DbParameter
param = myCommand.CreateParameter()
param.ParameterName = DalHelpers.ReturnCommandParamName(“blogEntryId”) param.DbType = DbType.Int32
param.Value = blogEntryId myCommand.Parameters.Add(param)
Dim myDataAdapter As DbDataAdapter = myFactory.CreateDataAdapter() myDataAdapter.SelectCommand = myCommand myDataAdapter.Fill(myDataSet)
myConnection.Close() Return myDataSet
End Using
End Function
3.Add a stored procedure or query to the database. The procedure should be called sprocGet Comments. For SQL Server, it should contain this code:
CREATE PROCEDURE sprocCommentSelectList
@blogEntryId int
AS
SELECT
PostersName,
Chapter 6
Body
FROM
Comment
WHERE
BlogEntryId = @blogEntryId
ORDER BY
Id DESC
For the Access database it should contain this code:
SELECT
PostersName,
Body
FROM
Comment
WHERE
BlogEntryId = ?
ORDER BY
Id DESC;
4.The final step in displaying the comments is to call the GetComments method whenever the ShowComments panel becomes visible. To do that, locate the code you added in the event handler for the ItemCommand of the blog list, and replace the TODO placeholder with the following code:
Case “showcomments” pnlShowComments.Visible = True lstComments.DataSource = _
BlogManager.GetComments(Convert.ToInt32(e.CommandArgument))
lstComments.DataBind() End Select
With this Comments feature, you have a full-blown blogging application that not allows you to create blog entries and share them with the world, but also enables your visitors to react to the things you have written. Have fun blogging!
