Monday 8 August 2011

Ajax Rating Control with Database

Introduction:

Here I will explain how to use Ajax rating control with database to display average rating for particular article using asp.net

Description:

Previously I explained
how to use Ajax Collapsible panel . Now I will explain how to use Ajax rating control with Database in asp.net. In many sites we will see rating options for books, articles and movies etc by giving rating option to user we have chance to know about particular thing how much users are feeling comfort with particular thing. In Ajax we have a rating control by using that we can display the rating option easily. Here I am storing each user rating details into database and displaying the average rating based on number of users rating. To achieve this first design one table in your database and give name like RatingDetails if you want to give another name you can but you need to change table name in code also.

Column Name
Data Type
Allow Nulls
Id
Int(Set Identity=true)
No
Rate
int
Yes
After completion of design table in database add AjaxControlToolkit reference to your application after that add
<%@ Register Namespace="AjaxControlToolkit" Assembly="AjaxControlToolkit" tagPrefix="ajax" %>

To your aspx page and design your page likes this

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
<!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>Ajax Rating Sample</title>
<style type="text/css">
.ratingEmpty
{
background-image: url(ratingStarEmpty.gif);
width:18px;
height:18px;
}
.ratingFilled
{
background-image: url(ratingStarFilled.gif);
width:18px;
height:18px;
}
.ratingSaved
{
 background-image: url(ratingStarSaved.gif);
width:18px;
height:18px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<ajax:ToolkitScriptManager ID="ScripManager1" runat="server"/>
<div>
<asp:UpdatePanel ID="pnlRating" runat="server">
<ContentTemplate>
<table width="35%">
<tr>
<td width="27%">
<b>Average Rating:</b>
</td>
<td>
<ajax:Rating ID="ratingControl" AutoPostBack="true" OnChanged="RatingControlChanged" runat="server" StarCssClass="ratingEmpty" WaitingStarCssClass="ratingSaved" EmptyStarCssClass="ratingEmpty" FilledStarCssClass="ratingFilled">
</ajax:Rating>
<b> <asp:label ID="lbltxt" runat="server"/> </b>
</td>
</tr>
<tr>
<td colspan="2">
aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies
</td>
</tr>
</table>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
</body>
</html>
If you observe above code I define lot of properties to ajax:Rating now I will explain all the properties of Ajax rating control

AutoPostBack – This property should be true because we are storing rating details during rating item click.

OnChanged – This event is used to get how many stars user has selected.

StarCssClass – This cssclass is used for to display starts.

WaitingStarCssClass – This cssclass is used to show the starts color during saving the rating value.

EmptyStarCssClass – This cssclass is used to display empty starts color.

 FilledStarCssClass – This cssclass is used to display filled stars color.
 CurrentRating – This property is used to display Initial rating value (Number of starts to be filled initially).

MaxRating – This property is used to display maximum rating value (No. of starts here I am displaying only 5 if you want to increase starts value give property like this MaxRating=10).

ReadOnly – This property is used to make rating control read only.

RatingAlign – This property is used to set starts vertical or horizontal.

RatingDirection – This property is used to set the direction of stars(LeftToRight or TopToBottom or RightToLeft or BottomToTop).

Now in code behind add following namespaces

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
After completion of adding namespaces in code behind add following code


SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
BindRatingControl();
}
}
protected void RatingControlChanged(object sender, AjaxControlToolkit.RatingEventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("insert into RatingDetails(Rate)values(@Rating)",con);
cmd.Parameters.AddWithValue("@Rating", ratingControl.CurrentRating);
cmd.ExecuteNonQuery();
con.Close();
BindRatingControl();
}
protected void BindRatingControl()
{
int total = 0;

DataTable dt = new DataTable();
con.Open();
SqlCommand cmd = new SqlCommand("Select Rate from RatingDetails", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
if(dt.Rows.Count>0)
{
for(int i=0;i<dt.Rows.Count;i++)
{
total += Convert.ToInt32(dt.Rows[i][0].ToString());
}
int average = total/(dt.Rows.Count);
ratingControl.CurrentRating = average;
lbltxt.Text = dt.Rows.Count+"user(s) have rated this article";
}
}
Now in web.config file set your database connection because in above code I am getting connection from web.config
<connectionStrings>
<add name="dbConnection" connectionString="Data Source=SureshDasari;Initial Catalog=MySampleDB;Integrated Security=true"/>
</connectionStrings>

Demo


2 comments:

  1. Nice article..but i am getting error while executing in my system...in the line
    "total += Convert.ToInt32(dt.Rows[i][0].ToString());"
    Error: "Input string was not in a correct format. "

    Can u plz tell wher i wuld hav gone wrong??

    ReplyDelete
    Replies
    1. Hi,
      its very simple, value in 1st column of ith row is not numeric one.
      plz check which kind of data comes at that possition

      Delete