To read the article online, visit http://www.4GuysFromRolla.com/articles/043004-1.aspx

Improving the Content Rater

By Scott Mitchell


Introduction


The content rater at MSDN. In an earlier article of mine, Creating a Content Rater, I showed how to create a content rater User Control like the one shown on the right. I implemented this content rate on my blog, ScottOnWriting.NET, about two weeks ago and have since had a number of readers rate various blog entries and leave comments.

As the number of ratings increased, I wanted to add some additional features for both myself and my visitors. First, I wanted to list the highest rated entries on each page. This way, visitors would be just one click away from any of the top rated entries. Second, I wanted some sort of Web-based interface that I could use myself to quickly see the ratings and comments for each of the entries. This article discusses these two enhancements, and builds upon the original article. (If you have not yet read the original article, be sure to do so before continuing!)

Displaying the Highest Rated Items


After enough people have rated content, it would be nice to show everyone the top rated content. (For some reason, MSDN doesn't do this, even though they have hundreds of people rating each article.) To accomplish this I first created a stored procedure, blog_TopRated, that returned the five highest rated items that had at least 3 votes. (I didn't want a blog entry with just one high vote to make it on the list...) The stored procedure's syntax follows:

SELECT TOP 5 
      ContentID, 
      Title, 
      AVG(CONVERT(decimal(4,2), Rating)) as AvgRating, 
      COUNT(*) as TotalCount
FROM blog_Ratings br
	INNER JOIN blog_Content bc ON
		br.ContentID = bc.ID
GROUP BY ContentID, Title
HAVING COUNT(*) > 2
ORDER BY AvgRating DESC

This query is specific to my blog's database structure, which uses the .Text blog engine. Each blog entry is stored in the blog_Content table. Each rating is stored in the blog_Ratings table. The blog_Ratings table has a foreign key field called ContentID that associated a rating with a particular blog entry.

The SQL query retrieves the ContentID, Title, average Rating, and total number of ratings for each entry that has three or more ratings. The HAVING clause is what ensures only those entries with more than two ratings will be returned. For more information on the GROUP BY and HAVING clauses, be sure to read: Using the GROUP BY Clause and SQL: HAVING Clause, or reference the SQL Books Online.

On my blog I display the five highest rated articles using an unordered list (<ul>). The title of each entry is listed as a hyperlink. Also, the average rating and total number of ratings are displayed. The following shows the complete source code and markup for the User Control. Note that the Repeater control was used to render the unordered list.

<%@ Control Language="c#" %>
<%@ OutputCache Duration="1800" VaryByParam="None" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script language="C#" runat="server">
   private void Page_Load(object sender, System.EventArgs e)
   {
      if (!Page.IsPostBack)
      {
         SqlConnection myConnection = new SqlConnection(Connection String);

         SqlCommand myCommand = new SqlCommand("blog_TopRated", myConnection);
         myCommand.CommandType = CommandType.StoredProcedure;         

         myConnection.Open();
         TopRatedDG.DataSource = myCommand.ExecuteReader();
         TopRatedDG.DataBind();
         myConnection.Close();
      }
   }
</script>

<h3>Top Rated Entries</h3>
<asp:Repeater runat="server" id="TopRatedDG">
  <HeaderTemplate>
    <ul>
  </HeaderTemplate>
  
  <ItemTemplate>
    <li>
       <a href='posts/<%# DataBinder.Eval(Container.DataItem, "ContentID") %>.aspx'>
             <%# DataBinder.Eval(Container.DataItem, "Title") %>
       </a>
       (<%# DataBinder.Eval(Container.DataItem, "AvgRating", "{0:0.00}")%> | 
        <%# DataBinder.Eval(Container.DataItem, "TotalCount")%>)</li>
  </ItemTemplate>
  
  <FooterTemplate>
    </ul>
  </FooterTemplate>
</asp:Repeater>

A screenshot of the top rated items on ScottOnWriting.NET A couple things to note here. First, the Repeater creates an unordered list by creating a <ul> in the <HeaderTemplate> and a </ul> in the <FooterTemplate>. In the a <ItemTemplate> an <li> element is created for each of the five highest rated articles. A hyperlink is created, directing the user to posts/ID.aspx, where ID is the unique identifier of the blog entry. (.Text uses URL rewriting to map requests to posts/ID.aspx to a page that displays the specified blog entry...) Following the hyperlink, the average rating and total number of ratings are displayed. The screenshot to the right shows this "Top Rated Entries" section on ScottOnWriting.NET.

Displaying a Ratings Report
In addition to showing the visitors of my blog the five highest rated entries, I also wanted a page I could visit that would display all of the ratings. I wanted this report grouped by blog entry, showing all of the ratings for that particular entry beneath it. The screenshot below, which shows a screenshot of the actual screenshot of the page I created, illustrates the look I was after:

The ratings report screen.

To accomplish this I used a technique discussed in more detail in the FAQ Displaying Data Grouped By Category at DataWebControls.com. The SQL query to get the needed data is shown below:

SELECT bc.ID, 
       bc.Title, 
       bc.DateAdded as DateEntryAdded, 
       br.DateAdded, 
       br.Rating, 
       br.Comments 
FROM blog_Ratings br 
   INNER JOIN blog_Content bc ON 
      bc.ID = br.ContentID 
ORDER BY ID DESC, br.DateAdded DESC

This query gets all of the ratings from blog_Ratings, along with the related blog entries' Titles and DateAdded fields. The results are ordered by the blog entry's ID, which is essential to get the grouping working, as we'll see shortly.

The following shows the source code and markup for the ASP.NET Web page. Notice that it contains a Repeater control with an <ItemTemplate> that calls a helper function, DisplayTitleIfNeeded(). This function determines if the title has changed. If so, it means that we've reached ratings for a new blog entry, and a new <div> is created, and the Title field is displayed. For a more in-depth examination at how this manages to group data, refer to the DataWebControls.com FAQ:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server" language="C#">
   void Page_Load(object sender, EventArgs e)
   {
     SqlConnection myConnection = new SqlConnection(Connection String);
     myConnection.Open();

     SqlCommand myCommand = new SqlCommand(SQL query, myConnection);
     
     dlRatings.DataSource = myCommand.ExecuteReader();
     dlRatings.DataBind();
     
     myConnection.Close();
   }

   string lastUsedTitle = String.Empty;
   int titleCount = 0;

   string DisplayTitleIfNeeded(string title, string dateAdded)
   {
      string output = String.Empty;
      
      // Determine if this team has yet to be displayed
      if (title != lastUsedTitle)
      {
         // Set that the lastUsedTeam is the current team value
         lastUsedTitle = title;
         
         // Display the title
         output += "</div>";
         if (titleCount % 2 == 0)
           output += "<div style='background-color:#eeeeee;'>";
           
         output += "<big><b>" + title + "</b></big> (<i>posted " + 
                   dateAdded + "</i>)<br />";
         
         titleCount++;
      }

      return output;
   }
</script>

<h1>Blog Entry Ratings & Comments</h1>
<asp:Repeater runat="server" id="dlRatings">
  <HeaderTemplate><div></HeaderTemplate>
  <ItemTemplate>
    <%# DisplayTitleIfNeeded((string) DataBinder.Eval(Container.DataItem, "Title"), 
        ((DateTime) DataBinder.Eval(Container.DataItem, "DateAdded")).ToString()) %>
    <p><b>Date Added:</b> <%# DataBinder.Eval(Container.DataItem, "DateAdded") %>
    <br />
    <b>Rating:</b> <%# DataBinder.Eval(Container.DataItem, "Rating") %>
    <br />
    <b>Comments:</b> <%# DataBinder.Eval(Container.DataItem, "Comments") %
    ></p>
  </ItemTemplate>
  <FooterTemplate></div></FooterTemplate>
</asp:Repeater>

Conclusion


In an earlier article of mine, Creating a Content Rater, I showed how to create a content rater User Control similar to the one used on MSDN and CoDe Magazine's Web sites. After implementing this content rater on my own blog, I decided to add two enhancements: a list of the five highest rated blog entries for all visitors to see, and a complete list of all ratings for myself. In this article I shared the code and SQL queries used to implement these two features.

Happy Programming!

  • By Scott Mitchell

  • Article Information
    Article Title: ASP.NET.Improving the Content Rater
    Article Author: Scott Mitchell
    Published Date: April 30, 2004
    Article URL: http://www.4GuysFromRolla.com/articles/043004-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers