The tbh_Categories Table

Unsurprisingly, the tbh_Categories table stores some information about the article categories.

This system supports a single-level category, meaning that we cannot have subcategories. This is plenty for small to mid-size sites that don't have huge numbers of new articles on a wide variety of topics. Having too many categories in sites of this size can even hinder the user's experience, because it makes it more difficult to locate desired content. Enhancing the system to support subcategories is left as an exercise if you really need it, but as a suggestion, the DB would only require an additional ParentCategoryID column containing the ID of the parent category.

AddedDate and AddedBy are two columns that you will find in all our tables — they record when a category/article/comment/product/message/newsletter was created, and by whom, to provide an audit trail. You may have thought that instead of having an nvarchar column for storing the username we could use an integer column that would contain a foreign key pointing to records of the aspnet_Users table introduced in Chapter 4. However, that would be a bad choice for a couple of reasons:

1. The membership data may be stored in a separate database, and possibly on a different server.

2. The membership module might use a provider other than the default one that targets SQL Server. In some cases the user account data will be stored in Active Directory or maybe an Oracle database, and thus there would be no SQL Server table to link to.

The tbh_Articles Table

This table contains the content and all further information for all the articles in all categories. It is structured as follows.

The ReleaseDate and ExpireDate columns are useful because the site's staff can prepare content in advance and postpone its publication, and then let the site update itself at the specified date/time. In addition to the obvious benefit of spreading out the workload, this is also great during vacation periods, when the staff would not be in the office to write new articles but you still want the site to publish fresh content regularly.

The Listed column is also very important, because it enables you to add articles that will be hidden from the main article list page, and from the RSS feeds. Why would you want to do this? Suppose that you have a category called Photo Galleries (we'll actually create it later in the chapter) in which you publish the photos of a past event or meeting. In such photo gallery articles you would insert thumbnails of the photos with links to their full-size version. It would be nice if the reader could comment and rate each and every photo, not just the article listing them all, right? You can do that if instead of linking the big photo directly you link a secondary article that includes the photo. However, if you have many photos, and thus many short articles that contain each of them, you certainly don't want to fill the category's article listing with a myriad of links to the single photos. Instead, you will want to list only the parent gallery. To do this, you set the Listed property of all the photo articles to false, and leave it true only on the article with the thumbnails.

The Country, State, and City fields enable you to specify an accurate location for those articles that refer to an event (such as parties, concerts, beer contests, etc.). You may recall that we created the same properties in Chapter 2 for the user's profile. If the location for the article matches a specific user's location, even partially, then you could highlight the article with a particular color when it's listed on the web page. You may be wondering why it was necessary to define the Country and State fields as varchar fields, instead of an int foreign key pointing to corresponding records of the tbh_Countries and tbh_States lookup tables. The answer is that I want to use the City field to support not only U.S. states, but states and provinces for any other country, so I defined this as free text field. It's also good for performance if we de-normalize these fields. Using a lookup table is particularly useful when there is the possibility that some values may change; storing the information in one location minimizes the effort to update the data and makes it easier to ensure that we don't get out-of-sync. However, the list of countries will not realistically change, so this isn't much of a problem. In the remote case that this might happen, you will simply execute a manual update for all those records that have Country="USA" instead of "United States", for example. This design decision can greatly improve the performance of the application.

You may be wondering why I decided to put the Votes and TotalRating columns into this table, instead of using a separate table to store all the single votes for all articles. That alternative has its advantages, surely: You could track the name and IP address of the user who submits the vote, and produce interesting statistics such as the number of votes for every level of rating (from one to 5 stars). However, retrieving the total number of votes, the total rating, and the number of votes for each rating level would require several SUM operations, in addition to the SELECT to the tbh_Articles table. I don't think the additional features are worth the additional processing time and traffic over the network, and thus I opted for this much lighter solution instead.

The tbh_Comments Table

This table contains the feedback (comments, questions, answers, etc.) for the published articles. The structure is very simple.

We will track the name of the user posting the comment, but she could even be an anonymous user, so this value will not necessarily be one of the registered usernames. We also store the user's e-mail address, so that the reader can be contacted with a private answer to her questions. Storing the IP address might be legally necessary in some cases, especially when you allow anonymous users to post content on a public site. In case of offensive or illegal content, it may be possible to geographically locate the user if you know her IP address and the time when the content was posted. In simpler cases, you may just block posts from that IP (not a useful option if it were a dynamically assigned IP, though).

The Article Class

Some of the public properties of the Article class are just wrappers for the underlying DB fields, but others return calculated values. The following table lists all the properties.

The methods listed in the following table are instance methods, and use the object's instance property values to update, delete, or approve an article, and other operations with it.

Besides these instance members, there are several static methods that allow the caller to retrieve a list of articles or a single article, create a new article, delete, approve, update, or rate an existing article, and more. As they are static, they don't use any instance properties, and they get all the data they need as input parameters.

This class uses the lazy load pattern, which means data is loaded only when requested and not when the object instance is created. There are quite a few variations on this pattern, and you should refer to a patterns book for complete coverage of the subject. For the Article class, you don't need the list of comments or the article's body unless you're inside the specific article page that shows all details of the article. In the page that lists the available articles, you don't need those details, so we won't waste time retrieving them from the DB. The Article class has the CategoryID and CategoryTitle instance properties, which are often all you need to know about the parent category. However, it also has a Category property that returns a full Category object with all the parent category's details. That object isn't retrieved when the Article object is created, but rather when that property is actually read. Also, once requested, we'll fetch the data and store it locally in case it is requested again from the same object instance. The implementation presented later in this chapter is very simple, but it can dramatically improve the performance of the application.

The GetArticles overloads that take parameters to specify the page of results do not expect the index of the page to retrieve. Rather, they take the number of the first row to retrieve, and the page size. I don't care for this personally, but it's a requirement of the ObjectDataSource (which will be used later in the user interface) to work with pagination. Because the DAL's Select methods expect the page index instead of the number of the first row to retrieve, we'll have to calculate the page index.

The Category Class

This class has instance properties that fully describe a category of articles, instance methods to delete and update an existing category, and static methods to create, update, or delete one category. I won't describe all of them here as they are pretty similar to the corresponding methods of the Article class. They're actually a little simpler because you don't need multiple overloads to support pagination and other filters. There are two properties, Articles and PublishedArticles, that use a couple of overloads of the Article.GetArticles static methods to return a list of Article objects. Like the Article.Comments property, these two properties also use the lazy load pattern, so articles are retrieved only once when the property is read for the first time.

The Comment Class

The Comment class has various overloads for the GetComments static method that, like Article.Get Articles, can take in input different parameters for the parent article ID and the pagination support. In addition to the data returned by the DAL it also exposes an Article property that uses the lazy load pattern to load all details of a comment's parent article as needed. Another property it exposes is EncodedBody, which returns the same text returned by the Body property, but first performs HTML encoding on it. This protects us against the so-called script-injection and cross-site scripting attacks. As a very simple example, consider a page on which you allow users to anonymously post a comment. If you don't validate the input, they may write something like the following:

<script>document.location = 'http://www.usersite.com';</script>

This text is sent to the server and you save it into the DB. Later, when you have to show the comments, you would retrieve the original comment text and send to the browser as is. However, when you output the preceding text, it won't be considered as text by the browser, but rather as a JavaScript routine that redirects the user to another web site, hijacking the user away from your web site! And this was just a basic attack — more complex scripts could be used to steal users' cookies, which could include authentication tickets and personal data, with potentially grave consequences. For our protection, ASP.NET automatically validates the user input sent to the server during a postback, and checks whether it matches a pattern of suspicious text. However, in that case it raises an exception and shows an error page. You should consider the case where a legitimate user tries to insert some simple HTML just to format the text, or maybe hasn't really typed HTML but only a < character. In that case, you don't want to show an error page, you only need to ensure that the HTML code isn't displayed in a browser (because you don't want users to put links or images on your site, or text with a font so big that it creates a mess with your layout). To do so you can disable ASP.NET's input validation (only for those pages on which the user is actually expected to insert text, not for all pages!), and save the text into the DB, but only show it on the page after HTML encoding, as follows:

<script> document.location = 'http://www.usersite.com'; </script>

This way, text inserted by the user is actually shown on the page, instead of being considered HTML. The link will show as a link but it will not be a clickable link, and no JavaScript can be run this way. The EncodedBody property returns the HTML encoded text, but it can't completely replace the Body property, because the original comment text is still required in certain situations — for example, in the administration pages where you show the text into a textbox, and allow the administrator to edit it.

Writing Articles with a WYSIWYG Text Editor

The first and most important challenge you face is that the site must be easily updateable by the client herself, without requiring help from any technical support people. Some regular employees working in the pub must be able to write and publish new articles, and make them look good by applying various formatting, colors, pictures, tables, etc. All this must be possible without knowing any HTML, of course! This problem can be solved by using a WYSIWYG (the acronym for "what you see is what you get") text editor: These editors enable users to write and format text, and to insert graphical elements, much like a typical word processor (which most people are familiar with), and the content is saved in HTML format that can be later shown on the end-user page "as is." There are various editors available, some commercial and some free. Among the different options I picked up FCK Editor (www.fckeditor.net), mainly because it is open source and because it is compatible with most Internet browsers, including IE 5.5+, Firefox 1.0+, Mozilla 1.3+, and Netscape 7+. Figure 5-4 shows a screenshot of an online demo from the editor's web site.

Figure 5-4

The editor is even localizable (language packs for many languages are already provided), and its user interface can be greatly customized, so you can easily decide what toolbars and what command buttons (and thus formatting and functions) you want to make available to users.

Uploading Files

The editor must be able to upload files, typically images for an article, or publish in a photo gallery, and maybe upload documents, screen savers, or other goodies that they want to distribute to their end users. An administrator of a site would be able to use an FTP program to upload files, but an editor typically does not have the expertise, or the credentials, needed to access the remote server and its file system. An online file manager might be very helpful in this situation. In the first edition of this book, an entire chapter was devoted to showing you how to build a full-featured online file manager that would enable users to browse and remove folders and files, upload new files, download, rename, copy and delete existing files, and even edit the content of text files. However, this would be overkill in most situations, as the administrator is the only one who needs to have full control over the files and folders and structure of the site, and the administrator will presumably use an FTP client for this purpose. Editors and contributors only need the capability to upload new files. To implement this functionality, we will develop a small user control that allows users to upload one file at a time, and when done, displays the full URL of the file saved on the server, so the user can easily link to it using the WYSIWYG editor. The control will be used in various pages: in the page to add and edit an article, and in the page to manage categories (as each category can have an image representing it); and later in book we'll use this in the pages that send newsletters and submit forum posts.

This user control, named FileUploader.ascx, will utilize the new ASP.NET 2.0 FileUpload control to select the file, submit it, and save it on the server. This control simply translates to an <input type="file" /> control, with server-side methods to save the image. Under ASP.NET 1.x there was no such control; you had to add the runat="server" attribute to a plain HTML control declaration.

One important design decision we need to consider is how to avoid the possibility that different editors might upload files with the same name, overwriting previous files uploaded by someone else. A simple, but effective, solution is to save the file under ~/Uploads/{UserName}, where the {UserName} placeholder is replaced by the actual user's name. This works because only registered and authenticated users will have access to pages where they can upload files. We do want to let users overwrite a file that they uploaded themselves, as they might want to change the file.

Article List User Control

You will need a way to quickly add the list of articles (with title, author, abstract, and a few more details) to any page. It's not enough to have entirely new articles; you also need to show them on existing pages so users will know about them! You'll need to show the list on the BrowseArticles.aspx page for end users and on the ManageArticles.aspx page for administrators. You may also want to show the article list on the home page. If you've got a good understanding of user controls, you may have already guessed that a user control is the best solution for this list because it enables us to encapsulate this functionality into a single code unit (the .ascx file plus the cs code-behind file), which enables us to write the code once and then place that user control on any page using one line of code.

This user control will be named ArticleListing.ascx. It produces different output according to whether the user is a regular user, an administrator, or an editor. If they belong to one of the special roles, each article item will have buttons to delete, edit, or approve them. This way, we can have a single control that will behave differently according to its context. Besides this, when the control is placed into an administration page, it must show all articles, including those that are not yet published (approved), or those that have already been retired (based on the date). When the control is on an end-user page, it must show only the active and published articles. The control will expose the following public properties (all Boolean), so that it's content and its behavior can be changed in different pages:

Producing and Consuming RSS Feeds

You've already learned from the Introduction that we're going to implement a mechanism to provide the headlines of the site's new content as an RSS feed, so that external (online or desktop-based) aggregator programs can easily consume them, adding new content to their own site, but also driving new traffic to our site. This process of providing a list of articles via RSS is called syndication. The XML format used to contain RSS content is simple in nature (it's not an accident that the RSS acronym stands for "Really Simple Syndication"), and here's an example of one RSS feed that contains an entry for two different articles:

<rss version="2.0">
 <channel>
  <title>My RSS feed</title>
  <link>http://www.contoso.com</link>
  <description>A sample site with a sample RSS</description>
  <copyright>Copyright 2005 by myself</copyright>

  <item>
   <title>First article</title>
   <author>Marco</author>
   <description>Some abstract text here...</description>
   <link>http://www.contoso.com/article1.aspx</link>
   <pubDate>Sat, 03 Sep 2005 12:00:34 GMT</pubDate>
  </item>
  <item>
   <title>Second article</title>
   <author>Mary</author>
   <description>Some other abstract text here...</description>
   <link>http://www.contoso.com/article2.aspx</link>
   <pubDate>Mon, 05 Sep 2005 10:30:22 GMT</pubDate>
  </item>
 </channel>
</rss>

As you see, the root node indicates the version of RSS used in this file, and just below that is a <channel> section, which represents the feed. It contains several required sub-elements, <title>, <link>, and <description>, whose names are self-descriptive. There can also be a number of optional sub-elements, including <copyright>, <webMaster>, <pubDate>, <image>, and others. After all thosefeed-level elements is the list of actual posts/articles/stories, represented by <item> subsections. An item can have a number of optional elements, a few of which (title, author, description, link, pubDate) are shown in the preceding example. For details on the full list of elements supported by RSS you can check this link, http://blogs.law.harvard.edu/tech/rss, or just search for "RSS 2.0 Specification.

One important thing to remember is that this must be a valid XML format, and therefore you cannot insert HTML into the <description> element to provide a visual "look and feel" unless you ensure that it meets XML standards (XHTML is the name for tighter HTML that meets XML requirement). You must ensure that the HTML is well formed, so that all tags have their closing part (<p> has its </p>) or are self-closing (as in <img.../>), among other rules. If you don't want the hassle of making sure the HTML is XML-compliant, you can just wrap the text into a CDATA section, which can include any kind of data. Another small detail to observe is that the value for the pubDate elements must be in the exact format "ddd, dd MMM yyyy HH:mm:ss GMT", as in "Thu, 03 Jan 2002 10:20:30 GMT". If you aren't careful to meet these RSS requirements, your users may get errors when they try to view your RSS feed. Some feed readers are more tolerant than others so it's not sufficient to make sure it works in your own feed reader — you need to meet the RSS specifications.

The RSS feed will be returned by the GetArticlesRss.aspx page, and according to the querystring settings, it will return the "n" most recent entries for a specific category, or for any category ("n" is a value specified in web.config, as explained in the configuration module design). Per standard convention, we'll use the orange "RSS" image icon as a link to the GetArticlesRss.aspx page.

Once you have an RSS feed for your site, you can also consume the feed on this site itself, on the home page, to provide a list of articles! The ArticleListing.ascx user control we already discussed is good for a page whose only purpose is to list articles, but it's too heavy for the home page. On the home page you don't need details such as the location, the rating, and other information. The new article's title and the abstract is enough — when users click on the title, they will be redirected to the page with the whole article, according to the link entry for that article in the RSS feed. We'll build our own RSS reader user control to consume our own RSS feed. The control will be generic, so that it will be able to consume RSS feeds from other sources as well, such as the site forum's RSS, the products RSS, or some other external RSS feeds. Its public properties are listed in the following table:

The only question left is how you can take the XML of the RSS feed and transform it into HTML to be shown on the page. Here are two possible solutions:

• Use an XSL stylesheet to apply to the XML content, and use an XSLT transform to write the templates that define how to extract the content from the XML and represent it with HTML.

• Dynamically build a DataTable with columns for the title, author, description, and the other <item>'s elements. Then fill the DataTable with the data read from the RSS feed, and use it as the data source for a Repeater, DataList, or other template-based control.

Personally, I strongly prefer the latter option, mostly because I find it much faster to change the template of a Repeater rather than to change the template in an XSL file. Additionally, with a DataTable I can easily add calculated columns, apply filters and sorting, and merge feeds coming from different sources (consider the case where you have multiple blogs or sources of articles and want to show their RSS feeds in a single box, with all items merged together and sorted by date). The DataTable approach is more flexible and easier to work with.