ASP.NET 2.0 Everyday Apps For Dummies (2006)

				Chapter 10: Building a Web Forum 337
	Column name	Type		Description
	forumid	INT		Indicates the Forum that this
				topic is associated with. This
				column is a foreign key to the
				forumid column in the
				Forums table.
	name	VARCHAR(255)		The topic name.
	description	VARCHAR(255)		A short description of the
				topic.

The Threads table

The Threads table stores information about the discussion threads that are active on the system. Its columns are listed in Table 10-3.

Table 10-3	The Threads Table
Column name	Type	Description
threadid	INT IDENTITY	An identity column that uniquely
		identifies each thread. This is the
		primary key for the Threads
		table.
topicid	INT	The topic ID of the topic the
		thread belongs to. This is a for-
		eign key to the topicid
		column of the Topics table.
subject	VARCHAR(100)	The subject line for the thread.
replies	INT	A count of the number of replies
		that have been made to the
		thread. This number will be one
		less than the total number of
		rows in the Messages table for
		the thread.
author	VARCHAR(100)	The e-mail address of the user
		who created the thread.
lastpostdate	DATETIME	The date of the last message
		posted to the thread.

338 Part V: Building Community Applications

The Messages table

The Messages table stores the actual messages that have been posted to the Forum Web site. Its columns are listed in Table 10-4.

Table 10-4	The Messages Table
Column name	Type	Description
msgid	INT IDENTITY	An identity column that uniquely
		identifies each message. This is
		the primary key for the
		Messages table.
threadid	INT	The ID of the thread this mes-
		sage belongs to. This is a foreign
		key to the threadid column of
		the Threads table.
author	VARCHAR(100)	The e-mail address of the user
		who created the message.
date	DATETIME	The date the message was
		posted.
message	VARCHAR(MAX)	The text of the message.

Creating the Database

Listing 10-1 shows a SQL script named CreateForumDB.sql which creates the Forum database. To run this script, open a command prompt window and change to the directory that contains the script. Then enter this command:

sqlcmd -S localhost\SQLExpress -i CreateForumsDB.sql

Change the host name if you’re not using SQL Server Express on your own computer.

Listing 10-1: The CreateForumDB.sql script

USE master	1
GO
IF EXISTS(SELECT * FROM sysdatabases	2
WHERE name=’Forum’)
DROP DATABASE Forum

Chapter 10: Building a Web Forum 339

GO

CREATE DATABASE Forum

ON (NAME=Product,

FILENAME = ‘C:\APPS\Forum.mdf’,

SIZE=10 )

GO

USE Forum
CREATE TABLE Forums (
forumid	INT IDENTITY,
name	VARCHAR(255)	NOT NULL,
description VARCHAR(255)		NOT NULL,
PRIMARY KEY(forumid)
)
GO
CREATE TABLE Topics (
topicid	INT IDENTITY,
forumid	INT,
name	VARCHAR(255)	NOT NULL,
description VARCHAR(255),
PRIMARY KEY(topicid),
FOREIGN KEY(forumid) REFERENCES Forums(forumid)
)
GO
CREATE TABLE Threads (
threadid	INT IDENTITY,
topicid	INT	NOT NULL,
subject	VARCHAR(100)	NOT NULL,
replies	INT	NOT NULL,
author	VARCHAR(100)	NOT NULL,
lastpostdate DATETIME,
PRIMARY KEY(threadid),
FOREIGN KEY(topicid) REFERENCES Topics(topicid)
)
GO
CREATE TABLE Messages (
msgid	INT IDENTITY,
threadid	INT	NOT NULL,
author	VARCHAR(100)	NOT NULL,
date	DATETIME,
message	VARCHAR(MAX)	NOT NULL,
PRIMARY KEY(msgid),
FOREIGN KEY(threadid) REFERENCES Threads(threadid)
)
GO

3

4

5

6

7

8

340 Part V: Building Community Applications

Eight quick comments draw out the pertinent details of this listing:

1 Sets the database context to master.

2 Deletes the existing Forum database if it exists.

3 Creates a database named Forum, placing the database in the folder C:\Apps.

4 Sets the database context to Forum.

5 Creates the Forums table.

6 Creates the Topics table.

7 Creates the Threads table.

8 Creates the Messages table.

Adding Test Data

The companion CD also includes a script named InsertData.sql script, which creates test data for the database. It creates the forums and topics — for example, these:

forum	topic
Christmas	Decorating
	Light Displays
Halloween	Costumes
	Decorations
	Party Ideas

After the forums and topics are set up, you can create threads and messages yourself by running the Forum application.

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

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

Chapter 10: Building a Web Forum 341

SQL statements for working with the database

The Forum application uses several SQL statements to retrieve and update data in the Forum. Here’s a rundown on what the various SQL statements do:

These SELECT statements list the forums and topics on the home page:

SELECT [forumid], [name]

FROM Forums

ORDER BY [name]

SELECT [forumid], [topicid], [name], [description]

FROM Topics

ORDER BY [name]

Several pages use the following SELECT statements to retrieve names for the topic and thread being displayed:

SELECT [name], [description]

FROM [Topics]

WHERE ([topicid] = @topicid)

SELECT [subject]

FROM [Threads]

WHERE ([threadid] = @threadid)

The following query retrieves all threads for a given topic:

SELECT [threadid], [topicid], [subject], [replies], [author], [lastpostdate]

FROM [Threads]

WHERE ([topicid] = @topicid) ORDER BY [lastpostdate]

The following query retrieves all messages for a given thread:

SELECT [author], [date], [message]

FROM [Messages]

WHERE ([threadid] = @threadid)

ORDER BY [date]

The following statements insert rows into the Threads and Messages tables when the user creates a new thread:

INSERT Threads

(topicid, subject, replies, author, lastpostdate)”

VALUES(@topicid, @subject, @replies,uthor, @lastpostdate)

INSERT Messages

(threadid, author, date, message) VALUES(@threadid, uthor, @date, @message)

342 Part V: Building Community Applications

Finally, the following statements post a reply to a thread:

INSERT Messages

(threadid, author, date, message) VALUES(@threadid, uthor, @date, @message)

UPDATE Threads

SET replies = replies + 1

WHERE threadid = @threadid

Here, the UPDATE statement is required to update the replies column in the Threads table.

Connecting to the database

The connection string for the Forum Application is stored in the

<connectionStrings> section of the web.config file, like this:

<connectionStrings>

<add name=”ForumConnectionString” connectionString=”Data

Source=localhost\SQLExpress;

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

You may have to modify the connection string to match your server and database name.

Building the Master Page

The Forum application uses the simple Master Page that’s shown in Listing 10-2. As you can see, this Master Page includes a content placeholder — and nothing else. (When you develop your own Forum application, you’ll probably want to put additional information in the Master Page.)

Listing 10-2: The Master Page (MasterPage.master)

<%@ Master Language=”C#”

1

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”>

Chapter 10: Building a Web Forum 343

<title>Untitled Page</title> </head>

<body>

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

<asp:contentplaceholder 2 id=”ContentPlaceHolder1”

runat=”server”>

</asp:contentplaceholder>

</div>

</form>

</body>

</html>

Just two key points for this listing:

1 The Master directive indicates that the file is a Master Page.

Note that if you want to use Visual Basic rather than C# for the application’s code-behind files, you should change the AutoEventWireup attribute to false. (That won’t matter for this application; the Master Page doesn’t require a code-behind file.)

2 The ContentPlaceHolder control marks where to display the content for each page of the application.

Building the Forum Home Page

The Forum Home page (Default.aspx) displays a list of the forums and topics that are available on the Forum Web site. You can refer back to Figure 10-2 to see how this page appears when the application is run.

You might think a page that displays a list like this would be relatively simple — in fact, the forum and topics list are pretty tricky to create. To create this list, I used a pair of nested Repeater controls. The outer Repeater control displays the forums. For each forum, a second Repeater control lists the topics associated with that forum.

The following sections present the .aspx and code-behind files for this page.

344 Part V: Building Community Applications

The Default.aspx page

Listing 10-3 presents the .aspx code for the Forum Home page, which uses nested Repeater controls to display the topics for each forum provided by the application.

Listing 10-3: The Forum Home page (Default.aspx)

<%@ Page Language=”C#”	1
MasterPageFile=”~/MasterPage.master”
AutoEventWireup=”true”
CodeFile=”Default.aspx.cs”
Inherits=”_Default”
Title=”Forum Listing” %>
<asp:Content ID=”Content1” Runat=”Server”	2
ContentPlaceHolderID=”ContentPlaceHolder1” >
<h3>Which forum would you like to visit?</h3>
<table border=0>	3
<asp:Repeater ID=”ForumRepeater” runat=”Server”	4
OnItemDataBound=”ForumRepeater_ItemDataBound” >
<ItemTemplate>	5
<tr bgcolor=”Gainsboro”>
<td>
<asp:Label ID=”lblForumName”
runat=”Server”
Text=’<% #Eval(“name”) %>’ />
</td>
</tr>
<asp:Repeater ID=”TopicRepeater”	6
runat=”Server” >
<ItemTemplate>	7
<tr><td>
<asp:LinkButton ID=”linkTopicName”	8
runat=”Server”
Text=’<% #Eval(“name”)%>’
PostBackUrl=’<% #Eval(“topicid”,
“Threads.aspx?topic={0}”)%>’/>
--
<asp:Label ID=”lblTopicDescr”	9
runat=”Server”
Text=’<% #Eval(“description”)%>’ />
</td></tr>
</ItemTemplate>	10
</asp:Repeater>	11
</ItemTemplate>	12
</asp:Repeater>	13
</table>	14
</asp:Content>

Chapter 10: Building a Web Forum 345

And here’s another set of 14 key points for this listing:

1 You’ll need to change the Language, AutoEventWireup, and CodeFile attributes in the Page directive if you’re working with Visual Basic rather than C#.

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

3 This <table> element marks the start of the HTML table used to display the list of forums and topics.

4 This is the first of the two Repeater controls on this page. Note that although this application uses data binding to provide the data for the Repeater controls, the data binding is done procedurally in code provided by the code-behind file. As a result, the

.aspx file provides no data source and the Repeater control does not specify the DataSourceID attribute.

The OnItemDataBound attribute specifies that the ForumRepeater_DataBound method should be called for each item in the Repeater control’s data source. If you’re working in Visual Basic, omit this attribute; the Handles clause is used instead to specify the method called when the ItemDataBound event occurs.

5 The <ItemTemplate> element for the first Repeater control begins with an HTML table row (<tr>) that includes a Label control. The Text attribute for the label uses the Eval method to display the name field from the data source.

The code-behind file specifies the data source at run time.

6 Next, the item template includes a Repeater control that displays the topics for the current forum. Like the first Repeater control, the data binding for this Repeater control is handled in the codebehind file.

7 The item template for the second Repeater control displays an HTML table row for each topic associated with the current forum.

8 A LinkButton control is used to display the topic name. The PostBackUrl address for the link button uses the Eval method to pass the ID for the selected topic to the Threads.aspx page as a query string. For example, if the user clicks the link button for a

topic whose topicid is 4, the PostBackUrl will be

Threads.aspx?topic=4.

9 This label displays the topic description.

346 Part V: Building Community Applications

10 This </ItemTemplate> tag marks the end of the item template for the inner Repeater control, which was started in Line 7.

11 This </asp:Repeater> tag marks the end of the inner Repeater control. It pairs with the <Repeater> tag in Line 6.

12 This </ItemTemplate> tag marks the end of the item template for the outer Repeater control, started in Line 5.

13 This </asp:Repeater> tag marks the end of the outer Repeater control. It matches up with the <Repeater> tag in Line 4.

14 This line marks the end of the HTML table started in Line 3.

The code-behind file for the Forum Home page

The code-behind file for the Forum Home page handles the details of retrieving the forum and topic information from the database. Then it binds the outer Repeater control to the list of forums. Finally, each time an item is bound for the outer Repeater control, this process binds the inner Repeater control and displays topics for the current forum.

Listings 10-4 and 10-5 show the C# and Visual Basic versions of this codebehind file.

Listing 10-4: The code-behind file for the home page (C# version)

using System; using System.Data;

using System.Configuration; using System.Collections; using System.Web;

using System.Web.Security; using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Web.Configuration;	1
using System.Data.SqlClient;	2
public partial class _Default : System.Web.UI.Page
{
DataSet ds;	3
protected void Page_Load(object sender,	4