ASP.NET 2.0 Everyday Apps For Dummies (2006)

Chapter 11: Building a Blog Application 387

description VARCHAR(255)		NOT NULL,
posts	INT	NOT NULL
		DEFAULT 0,
PRIMARY KEY(blogid)
)
GO
CREATE TABLE Posts (
postid	INT IDENTITY,
blogid	INT	NOT NULL,
postdate	DATETIME	NOT NULL
		DEFAULT CURRENT_TIMESTAMP,
subject	VARCHAR(255)	NOT NULL,
post	VARCHAR(MAX)	NOT NULL,
comments	INT	NOT NULL
		DEFAULT 0,
PRIMARY KEY(postid),
FOREIGN KEY(blogid) REFERENCES Blogs(blogid)
)
GO
CREATE TABLE Comments (
commentid	INT IDENTITY,
postid	INT	NOT NULL,
commentdate	DATETIME	NOT NULL
		DEFAULT CURRENT_TIMESTAMP,
username	VARCHAR(100)	NOT NULL,
comment	VARCHAR(MAX)	NOT NULL,
PRIMARY KEY(commentid),
FOREIGN KEY(postid) REFERENCES Posts(postid)
)
GO

CREATE TRIGGER tr_PostCount

ON Posts

AFTER INSERT

AS

DECLARE @blogid INT

SELECT @blogid = blogid

FROM inserted

UPDATE blogs

SET posts = posts + 1

WHERE blogid = @blogid

RETURN

GO

CREATE TRIGGER tr_CommentCount

ON Comments

AFTER INSERT

6

7

8

9

(continued)

388 Part V: Building Community Applications

Listing 11-1 (continued)

ˇ

AS

DECLARE @postid INT

SELECT @postid = postid

FROM inserted

UPDATE posts

SET comments = comments + 1

WHERE postid = @postid

RETURN

GO

Nine rapid-fire paragraphs draw out the pertinent details of this listing:

1 Sets the database context to master.

2 Deletes the existing Blog database if it exists.

3 Creates a database named Blog. The data file is stored in

C:\Apps.

4 Sets the database context to Blog.

5 Creates the Blogs table.

6 Creates the Posts table.

7 Creates the Comments table.

8 Creates a trigger named tr_PostCount. This trigger executes whenever a row is inserted in the Posts table. It defines a variable named @blogid which is set to the value of the blogid column in the inserted row. Then it executes an UPDATE statement that increments the posts column for the Blogs row that the new post was added to.

9 Creates a trigger named tr_CommentCount. This trigger executes whenever a row is inserted in the Comments table. It defines a variable named @postid which is set to the value of the postid column in the inserted row. Then it executes an UPDATE statement that increments the comments column of the appropriate row in the Posts table.

Adding test data

On the companion CD, you’ll find a script named InsertData.sql, which creates test data for the database. It creates two blogs, along with several posts and comments.

Chapter 11: Building a Blog Application 389

To run the InsertData.sql script, open a command window, change to the directory that contains the script, and run this command:

sqlcmd -S localhost\SQLExpress -i InsertData.sql

You’ll need to change the server name if it is other than localhost\ SQLExpress.

SQL statements for working with the database

The Blog application uses several SQL statements to retrieve and update data in the Blog database, as described in the following paragraphs:

To list the Blogs on the default.aspx, this SELECT statement is used:

SELECT [blogid], [name],

[description], [username], [posts]

FROM [Blogs]

ORDER BY [name]

The MyBlogs.aspx page uses a similar SELECT statement to retrieve the blogs for the current user:

SELECT [blogid], [name],

[description], [username], [posts] FROM [Blogs]

WHERE [username]=@username ORDER BY [name]

The Blog.aspx page uses the following SELECT statement to list the posts in a particular blog:

SELECT [postid], [blogid], [postdate], [subject]

FROM [Posts]

WHERE ([blogid] = @blogid) ORDER BY [postdate] DESC

The posts are sorted in descending date sequence, so the newest post is listed first.

The Blog.aspx page also uses this SELECT statement to retrieve the details for the selected post:

SELECT [postid], [blogid], [postdate], [subject], [post], [comments]

FROM [Posts]

WHERE ([postid] = @postid)

390 Part V: Building Community Applications

The Comments.aspx page uses this SELECT statement to retrieve all of the comments for a given post:

SELECT [commentdate], [username], [comment]

FROM [Comments]

WHERE ([postid] = @postid)

ORDER BY [commentdate]

The MyBlogs.aspx page uses this statement to create a new blog:

INSERT INTO [Blogs] ([username], [name], [description], [posts])

VALUES (@username, @name, @description, @posts)

To create a new post, the NewPost.aspx page uses this statement:

INSERT INTO [Posts]

([blogid], [subject], [post]) VALUES (@blogid, @subject, @post)

Finally, the Comment.aspx page uses the following INSERT statement to create a new comment:

INSERT INTO [Comments]

([postid], [username], [comment]) VALUES (@postid, @username, @comment)

Connecting to the database

As with the other applications in this book, the connection string for the Blog application is stored in the <connectionStrings> section of the web.config file:

<connectionStrings>

<add name=”BlogConnectionString” connectionString=”Data

Source=localhost\SQLExpress;

Initial Catalog=Blog;Integrated Security=True”/> </connectionStrings>

Note that if you’re not using SQL Server Express on your local computer, you’ll have to modify the connection string to provide the correct server name.

Building the Master Page

The Master page for the Blog application is shown in Listing 11-2. The codebehind file for this Master Page has no methods, so it isn’t shown here.

Chapter 11: Building a Blog Application 391

Listing 11-2: The master page (MasterPage.master)

<%@ Master Language=”C#” AutoEventWireup=”true” CodeFile=”MasterPage.master.cs” Inherits=”MasterPage” %>

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.1//EN” “http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd”>

<html xmlns=”http://www.w3.org/1999/xhtml” > <head runat=”server”>

<title>Blog-O-Rama</title> </head>

<body>

<form id=”form1” runat=”server”> <div>

<h1>Blog-O-Rama</h1> <asp:LoginName ID=”LoginName1”

runat=”server” FormatString=”Hello, {0}” />

<br />

<asp:LinkButton ID=”btnHome” runat=”server” Text=”Home” PostBackUrl=”~/Default.aspx” />

<asp:LoginStatus ID=”LoginStatus1” runat=”server” LogoutAction=”Refresh” />

<asp:LinkButton ID=”btnRegister” runat=”server” Text=”Register” CausesValidation=”False”

PostBackUrl=”~/Register.aspx” />

<asp:LinkButton ID=”btnMyBlogs” runat=”server”

Text=”My Blogs” CausesValidation=”False” PostBackUrl=”~/Admin/MyBlogs.aspx” />

<br /><br />

<asp:contentplaceholder

id=”ContentPlaceHolder1”

runat=”server”>

</asp:contentplaceholder>

</div>

</form>

</body>

</html>

1

2

3

4

5

6

7

392 Part V: Building Community Applications

Here’s a rundown on the key points of this listing:

1 If you’re using Visual Basic rather than C# for the application’s code-behind files, you should change the Language attribute to

C# and the AutoEventWireup attribute to false.

2 The LoginName control displays the name of the user if the user has logged in. The FormatString attribute adds the word Hello before the user name.

3 This link button returns the user to the home page

(Default.aspx).

4 The LoginStatus control displays a link button that lets the user log in or out.

5 This link button posts back to the Register.aspx page so new users can register.

6 This link button posts to the MyBlogs.aspx page. This page is in the Admin folder, which contains a web.config file that prohibits anonymous access. As a result, the user must log in to access this page. If the user is not already logged in, ASP.NET will automatically redirect the user to the Login.aspx page. To accomplish this, the web.config file in the Admin folder contains the following lines:

<system.web>

<authorization>

<deny users=”?” /> </authorization>

</system.web>

7 The ContentPlaceHolder control marks the location where the content for each page of the application will be displayed.

Building the Blog Home Page

The Blog Home page (Default.aspx) displays a list of the blogs that are available on the Blog Web site. You can refer back to Figure 11-2 to see how this page appears when the application runs.

The Blog Home page consists mostly of a GridView control that displays a list of the blogs in the Blogs table. The GridView control displays three columns. The first displays the name and description of the blog, with the name presented as a link button that posts to the Blog.aspx page and passes the ID of the selected blog as a query string field. The second column displays the name of the user that created the blog, and the third displays the number of posts that have been made to the blog.

Listing 11-3 presents the .aspx code for this page. A code-behind file isn’t required.

Chapter 11: Building a Blog Application 393

Listing 11-3: The Blog Home page (Default.aspx)

<%@ Page Language=”C#” MasterPageFile=”~/MasterPage.master” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” Title=”Blog-O-Rama” %>

<asp:Content ID=”Content1” Runat=”Server” ContentPlaceHolderID=”ContentPlaceHolder1” > <h3>Active Blogs</h3>

<asp:GridView ID=”GridView1” runat=”server” AllowPaging=”True” AutoGenerateColumns=”False” DataSourceID=”SqlDataSource1”> <Columns>

<asp:TemplateField>

<HeaderTemplate> Blog

</HeaderTemplate>

<ItemTemplate>

<b>

<asp:LinkButton ID=”LinkButton1” runat=”server”

Text=’<% #Bind(“name”) %>’ PostBackUrl

=’<% #Bind(“blogid”, “Blog.aspx?blog={0}”) %>’ />

<br />

<asp:Label ID=”Label2” runat=”server” Text=’<% #Bind(“description”) %>’ />

</ItemTemplate>

<HeaderStyle HorizontalAlign=”Left” /> <ItemStyle Width=”250px” />

</asp:TemplateField>

<asp:BoundField

DataField=”username”

HeaderText=”Owner” >

<HeaderStyle HorizontalAlign=”Left” /> <ItemStyle HorizontalAlign=”Left”

Width=”100px” /> </asp:BoundField> <asp:BoundField

DataField=”posts” HeaderText=”Posts” >

<HeaderStyle HorizontalAlign=”Left” /> <ItemStyle HorizontalAlign=”Left”

Width=”80px” /> </asp:BoundField> </Columns>

</asp:GridView>

1

2

3

4

5

6

(continued)

394 Part V: Building Community Applications

Listing 11-3 (continued)

<asp:SqlDataSource ID=”SqlDataSource1”

7

runat=”server” ConnectionString

=”<%$ ConnectionStrings:BlogConnectionString %>” SelectCommand=”SELECT [blogid], [name],

[description], [username], [posts] FROM [Blogs]

ORDER BY [name]”> </asp:SqlDataSource>

</asp:Content>

Seven important lines in this listing merit a little closer look:

1 You should change the Language, AutoEventWireup, and CodeFile attributes in the Page directive if you’re working with Visual Basic rather than C#. (It doesn’t really matter for this page, since no code-behind file is needed, but you should change it nonetheless.)

2 The <Content> element provides the content that’s displayed for the page.

3 The GridView control lists the blogs retrieved from the Blogs table by the SQL data source named SqlDataSource1. Note that paging is enabled for this GridView control. As a result, ten blogs will be listed at a time.

4 The first column defined for the GridView control is a template column. This column specifies two templates:

The header template displays the word “Blog” as the column heading.

The item template displays the blog name and title. The blog name is shown as a link button that uses binding expressions for the Text and PostBackUrl attributes. The binding expression for the Text attribute simply retrieves the name field from the data source. The binding expression for the PostBackUrl attribute is a little more complicated. It retrieves the blogid field from the data source and uses a format string to create a URL that specifies the blogid as a query string field. For example, if the blogid value is 3, the PostBackUrl attribute’s value will be Blog.aspx?blog=3.

5 The second column in the GridView control is bound to the username field in the data source.

6 The third column in the GridView control is bound to the posts field in the data source.

7 The data source uses a SELECT statement to retrieve the blogid, name, description, username, and posts columns from the

Blogs table.

Chapter 11: Building a Blog Application 395

Building the Blog Page

The Blog page displays one post from a selected blog (by default, the most recent) as well as a list of all the posts that have been made to the blog. The Blog page is passed a query string field to indicate which blog to display. In addition, a query string field is used to indicate which post should be displayed. If this query string is not present, the most recent post for the blog is displayed by default.

A code-behind file is used here to determine which post to display based on the post query string and to handle the Click events for the buttons that display comments for the post or enable a visitor to leave a comment. The following sections present the Blog.aspx file and both the C# and Visual Basic versions of the code-behind file.

The Blog.aspx page

Listing 11-4 presents the .aspx code for the Blog page. Most of the code for this page is straightforward, so you shouldn’t have any trouble following it.

Listing 11-4: The Blog.aspx page

<%@ Page Language=”C#”	1
MasterPageFile=”~/MasterPage.master”
AutoEventWireup=”true”
CodeFile=”Blog.aspx.cs”
Inherits=”Blog”
Title=”Blog-O-Rama” %>
<asp:Content ID=”Content1” Runat=”Server”	2
ContentPlaceHolderID=”ContentPlaceHolder1” >
<table border=”0”>	3
<tr>
<td width=”700”>
<asp:FormView ID=”FormView1” runat=”server”	4
DataSourceID=”SqlDataSource1”>
<ItemTemplate>
<asp:Label ID=”lblName” runat=”server”	5
Text=’<%# Bind(“name”,
“<h3>{0}</h3>”) %>’ />
<asp:Label ID=”lblDesc” runat=”server”	6
Text=’<%# Bind(“description”,
“<h4>{0}”) %>’ />
<asp:Label ID=”lblUser” runat=”server”	7
Text=’<%# Bind(“username”,

(continued)

396 Part V: Building Community Applications

Listing 11-4 (continued)

“<br/>By {0}</h4>”) %>’ />
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID=”SqlDataSource1”	8
runat=”server”
ConnectionString=
“<%$ ConnectionStrings:BlogConnectionString
%>”
SelectCommand=”SELECT [name], [description],
[username], [posts]
FROM [Blogs]
WHERE ([blogid] = @blogid)”>
<SelectParameters>
<asp:QueryStringParameter Name=”blogid”	9
QueryStringField=”blog” Type=”Int32” />
</SelectParameters>
</asp:SqlDataSource>
</td>
</tr>
<tr>
<td width=”350” valign=”Top”>
<asp:FormView ID=”FormView2” runat=”server”	10
DataKeyNames=”postid”
DataSourceID=”SqlDataSource2” >
<ItemTemplate>
<asp:Label ID=”lblSubject”	11
runat=”server”
Text=’<%# Bind(“subject”,
“<h1>{0}</h1>”) %>’ />
<asp:Label ID=”lblDate”	12
runat=”server”
Text=’<%# Bind(“postdate”,
“<h3>{0:F}</h3>”) %>’ />
<asp:Label ID=”lblPost”	13
runat=”server”
Text=’<%# Bind(“post”, “<p>{0}</p>”) %>’ />
<asp:Label ID=”lblComments”	14
runat=”server”
Text=’<%# Bind(“comments”,
“{0} comments.”) %>’/>
<asp:LinkButton ID=”btnViewComments”	15
runat=”server”
OnClick=”btnViewComments_Click”
Text=”View comments” />
<asp:LinkButton ID=”btnLeaveComment”	16
runat=”server”
OnClick=”btnLeaveComment_Click”
Text=”Leave a comment” />
</ItemTemplate>
</asp:FormView>