ASP.NET Content Rater Web Site Part 2 Rating System

This is part two of the ASP.NET Content Rater Web Site series. In this tutorial we will add the functionality for the user to apply their ratings to the content in ASP.NET 4.0 and C#.

Connecting to the Database
To begin setting up our rating functionality, we will first need to connect to our database. We will need to add in a connection string to our Web.Config file. To do this, open up the Web.Config file for editing and add the following code in between the <configuration> and <system.web> tags:

<connectionStrings>     <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings>

We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!

Displaying the Content
Next, we need to display the content to our users so that they will be able to rate it. To do this, we will be creating a data table in code that we will populate with the appropriate data from our database. Then, we will display this in a table within the repeater that we added to our home page. First, we need to begin creating our data table when the page loads. To do this, open Default.aspx.cs up for editing and add the following code to the Page_Load event method:

//check to make sure we're not reloading everything on postbacks if (!Page.IsPostBack) {     //sqldatareader we will use to get data from our database     SqlDataReader rdr;     //datatable we will use to bind to our repeater     DataTable dt = new DataTable();     DataRow dr;     //we will have 3 columns in our table     dt.Columns.Add("Title");     dt.Columns.Add("Author");     dt.Columns.Add("Rating");     //create a connection to our database     SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);     //sql command to select our book data     SqlCommand cmd = new SqlCommand("SELECT * FROM Books", conn);     using (conn)     {         //connect to our db         conn.Open();         //send the sql query and store the results         rdr = cmd.ExecuteReader();         //loop through the selected books         while (rdr.Read())         {             //create a new row for each book             dr = dt.NewRow();             //set the title and author             dr["Title"] = rdr["Title"].ToString();             dr["Author"] = rdr["Author"].ToString();             //add the row             dt.Rows.Add(dr);             //update table             dt.AcceptChanges();         }     }     //check to see if a user is currently logged in, if they are we will need to display their ratings in our table     if (Page.User.Identity.IsAuthenticated)     {         //create a connection to our database         conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);         //sql command to select the ratings of our current user         cmd = new SqlCommand("SELECT * FROM Ratings WHERE UserId=@UserId ORDER BY BookId ASC", conn);         //add our current username to the query         cmd.Parameters.AddWithValue("@UserId", Page.User.Identity.Name);         using (conn)         {             //connect to our db             conn.Open();             //send the sql query and store the results             rdr = cmd.ExecuteReader();             //loop through the selected ratings             while (rdr.Read())             {                 //set the rating of the appropriate book                 dt.Rows[(int)rdr["BookId"] - 1]["Rating"] = rdr["Rating"];             }         }     }     //databind our datatable to our repeater     Repeater1.DataSource = dt;     Repeater1.DataBind(); }

This code will populate our data table with the titles and authors of the books from our database. Then, it checks to see if the user is logged in. If they are, the ratings for that specific user are then added to our data table.

Next, we need to add in a table to our repeater to which we will display this data. To do this, open up the Default.aspx page in source mode and add the following code to our repeater:

<HeaderTemplate>     <table>         <tr>             <td>                 <strong>Title</strong>             </td>             <td>                 <strong>Author</strong>             </td>             <td>                 <strong>Rating</strong>             </td>         </tr> </HeaderTemplate> <ItemTemplate>         <tr>             <td>                 <%# Eval("Title") %>             </td>             <td>                 <%# Eval("Author") %>             </td>             <td>                 <asp:DropDownList ID="DropDownList1" runat="server" Text='<%# Eval("Rating") %>' OnTextChanged="SubmitRating" AutoPostBack="True">                     <asp:ListItem></asp:ListItem>                     <asp:ListItem>1</asp:ListItem>                     <asp:ListItem>2</asp:ListItem>                     <asp:ListItem>3</asp:ListItem>                     <asp:ListItem>4</asp:ListItem>                     <asp:ListItem>5</asp:ListItem>                 </asp:DropDownList>             </td>         </tr> </ItemTemplate> <FooterTemplate>     </table> </FooterTemplate>

If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.

This code creates a table with three columns, a title, author, and rating column. Then, it adds the title and author of each book to the appropriate column. Finally, it adds drop down lists with the options of 1 through 5 in them with the current user's ratings selected.

Submitting Ratings
If you take a look at our repeater code, you will notice that the drop down list has its OnTextChanged property set to 'SubmitRating'. What this does is call the SubmitRating method whenever the user changes the index of one of the drop down lists. It is this method that we will need to add to actually submit the user's selected rating to the database. To do this, add the following method to our Default.aspx.cs class:

//check to verify that the user is logged in if (Page.User.Identity.IsAuthenticated) {     //store the dropdownlist from the sender object     DropDownList ddl = (DropDownList)sender;     //get the new rating     int rating = ddl.SelectedIndex;     //get the unique id of the dropdownlist to determine the BookId     //this works because the books are listed in order 1-7, and the      //dropdownlist are added in the same order and are given unique id's 1-7     int id = int.Parse(ddl.UniqueID.Substring(13, 2));     //check to make sure they selected a valid rating     if (rating != 0)     {         //create a connection to our database         SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);         //sql command to update if exists and insert if not         SqlCommand cmd = new SqlCommand("UPDATE Ratings SET Rating=@Rating WHERE BookId=@BookId AND UserId=@UserId IF @@ROWCOUNT=0 INSERT INTO Ratings(BookId, UserId, Rating) VALUES(@BookId, @UserId, @Rating)", conn);                          //add our rating, bookid, and userid to the sql command         cmd.Parameters.AddWithValue("@Rating", rating);         cmd.Parameters.AddWithValue("@BookId", id);         cmd.Parameters.AddWithValue("@UserId", Page.User.Identity.Name);         using (conn)         {             //connect to our db             conn.Open();             //send sql query             cmd.ExecuteNonQuery();         }     } }

This code checks to make sure that a valid user and rating have been set. Then, it uses a SQL command to insert a new entry if the rating does not exist in the database. If the rating already existed, it updates it to the new value.

Need help with cloud hosting? Try Server Intellect. We used them for our cloud hosting services and we are very happy with the results!

Testing
To test this out, go ahead and load up the website. Login to the account that you created earlier and begin giving the books ratings by selecting a number between 1 and 5 for each of them. Then, close the web site and reload it. Login again and notice that it populated the ratings based on what you entered previously. This is a simple way to allow your users to rate content on your web site.