In this article we will see how we can store the values of rating in the database and fetch the rating value in the application.
Introduction
We have seen rating feature on most of the websites now - a - days and one of my friend asked is there any way to implement it by storing the values of the rating in the database and getting them back into the application.
Objective
- Understanding Ajax Star Rating Control.
- Storing the Values in the database.
- Fetching the Rating value
- Calculating average and showing it in Front end.
- Showing how many users rated the content.
Using the code
AJAX Star Rating Control :- Ajax Star rating Control allows us to rate specific content on the website.In Order to use the rating control we have to play with 5 major Properties of Rating Control and 1 important event.
- AutoPostback :- This property has to be set to true so that we can store the values of rating for each user.
- StarCSSClass :- This property will show the star on the web.
- WaitingStarCSSClass :- This property will show the star when you hover the mouse on the Rate Control.
- FilledStarCSSClass :- This property will show the rating on the stars with stars highlighted.
- EmptyStarCSSClass :- This property will show empty stars.
Event :- onchanged() event. (Storing the rating values in table)
This event will store the values rated by the user in database.The logic is quite simple we will be just inserting the values.To get the values we will use the Current rating property.To store the values in database it is a simple insert execution to be done in SQL.
Now lets start Implementing
- Create a New Empty website in Visual Studio 2010.
- Now we will add AJAX Rating Control.
- To embed Ajax Control toolkit please use this article as a resource and follow the steps from 1 - 12 in Using the Code section.Link for the article :- http://www.dotnetfunda.com/articles/article2344-upload-image-using-ajax-async-file-uploader-in-database.aspx
- Note :- Remember we are working with the Star Control, so after embedding the AJAX Control toolkit just drag and drop the Rate control.
- Now drag and drop 2 literals to show the total number of user rated the content and also to show the average rating given to the content.
User Interface :-
Rating While selecting the Stars
Raings fetched from database :-
Here is the Code snippet, Tables and Stored Procedures.
// Tables and Stored Procedures
-- Table for Storing Rating
create table RatingStore
(
ID int identity(1,1),
RatingValue int
)
-- Stored Proc for Inserting Rating
create proc InsertRating
(
@RatingValue int
)
as
begin
insert into RatingStore(RatingValue) values (@RatingValue)
end
-- Fetching Rating from Table
alter proc getRatings
as
begin
select RatingValue from RatingStore
end
// HTML Mark Up
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ajaxrating.aspx.cs" Inherits="ajaxrating" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.starempty
{
background-image: url(stars/rating_star.empty.gif);
width: 40px;
height: 40px;
}
.starfilled
{
background-image: url(stars/3.gif);
width: 40px;
height: 40px;
}
.starratingSaved
{
background-image: url(stars/ratingstar.jpg);
width: 40px;
height: 40px;
}
.style1
{
font-family: "Maiandra GD";
font-size: xx-large;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
<div>
<br />
<table >
<tr>
<td>
Rate this section
</td>
<td class="style4">
<asp:Rating ID="ajxRating" AutoPostBack="true" StarCssClass="starempty" WaitingStarCssClass="starratingSaved"
EmptyStarCssClass="starempty" MaxRating="5" FilledStarCssClass="starfilled" runat="server" onchanged="ajxRating_Changed">
</asp:Rating>
</td>
</tr>
<tr>
<td colspan="2" class="style1">
We Love Dotnet Funda.....
</td>
</tr>
</table>
</div>
<br />
<asp:Literal ID="xRatedUsersCount" runat="server"></asp:Literal> <br />
<asp:Literal ID="Averagerating" runat="server"></asp:Literal> <br />
</form>
</body>
</html>
// Code behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class ajaxrating : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Calling the function that gets rating from database.
BindRating();
}
}
protected void ajxRating_Changed(object sender, AjaxControlToolkit.RatingEventArgs e)
{
try
{
// This snippet will store the rating values.
SqlConnection sqlconn = new SqlConnection("Data Source=AG-KKC;Initial Catalog=DNF;Persist Security Info=True;User ID=sa;Password=sqluser");
sqlconn.Open();
// Calling the Stored Procefure
SqlCommand cmd = new SqlCommand("InsertRating", sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
// Passing the value of the selected rating using the currenttaing property
cmd.Parameters.AddWithValue("@RatingValue", SqlDbType.Int).Value = ajxRating.CurrentRating;
cmd.ExecuteNonQuery();
sqlconn.Close();
BindRating();
}
catch (Exception ex)
{
}
}
// Function to bind rating from database.
private void BindRating()
{
try
{
int ratingtotal = 0;
SqlConnection sqlconn = new SqlConnection("Data Source=AG-KKC;Initial Catalog=DNF;Persist Security Info=True;User ID=sa;Password=sqluser");
sqlconn.Open();
// calling the stored procedure for getting values from database.
SqlCommand cmd = new SqlCommand("getRatings", sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
// now we will loop through the rows and get the total values submitted by the user.
for (int getrating = 0; getrating < dt.Rows.Count; getrating++)
{
ratingtotal += Convert.ToInt32(dt.Rows[getrating][0].ToString());
}
// Over here we will get the average rating by dividing the total rating value by the count of users.
int average = ratingtotal / (dt.Rows.Count);
ajxRating.CurrentRating = average;
xRatedUsersCount.Text = dt.Rows.Count + " " + "users have rated this article";
Averagerating.Text = "Average rating for this article is" + " " + Convert.ToString(average);
}
}
catch (Exception ex)
{
}
}
}Explanation of BindRating() function :- Fetching rating values
In this function we get the total number of users who have rated the value and we will get the total ratings submitted by the user.Then we divide the total amount of rating divide by total count of user's and we will get the average rating for the Content.
Conclusion
To display the Average rating and Number of User rating the content are stored in a Literal Control.
Reference