Implement 5 Star Rating System using GridView, jQuery Ajax and Web API

← PrevNext →

A 5 Star rating system is a tool that allows users to tell if a service or product is good, bad or worst. It also helps business owners to analyze their product or services based on the ratings and deliver a better product. I’ll show you how to add a simple 5 star rating system in a GridView control using jQuery Ajax and Web API.

Implement 5 Star Rating System in Asp.Net GridView

The example here shows a list of books in a GridView control, with an option to choose (click) rates. The rating options will show in each row in the grid. The Stars are images.

The Web API Controller in this example will have the necessary functions to save user selected ratings (from 1 to 5) and return the total and average ratings, stored in an SQL Server table. Obviously, if I am using Web API as a web service then the best way to communicate with service is to use jQuery Ajax.

Let’s get on with our example.

The SQL Server Tables

We will first create 2 tables in our SQL Server database. The first table will have the books list that we will show on the Grid. The second table will store the ratings for each book, based on book ids.

Here is the books table. dbo.Books

Now, create the second table called dbo.Books_Rating.

CREATE TABLE dbo.Books_Rating(
    RateID INT IDENTITY(1,1) NOT NULL,
    BookID INT NOT NULL,
    Rating INT NOT NULL
)
The CSS and Markup

Microsoft has provided us many useful tools and one it is the GridView. It’s a very flexible tool, and you can easily bind a database table to the GridView. I am using SqlDataSource control to populate data to the GridView control, when the page first loads and when you choose to navigate between the GridView pages. You can do it using jQuery Ajax that I have mentioned above in this article. I am just trying to keep the script simple, so that the focus remains on manipulating the 5 star rating system.

Related: Bind data with GridView using SqlDataSource in Asp.Net

<!DOCTYPE html>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>

    <style>
        .container {
            width:600px; 
        }
        .grid  {
            width:100%; 
            font:inherit; 
            background-color:#FFF; 
            border:solid 1px #525252;
        }
        .grid td  {
            padding:2px;
            border:solid 1px #C1C1C1;
            color:#333;
            text-align:center;
            text-transform:capitalize;
        }
        .grid th {
            padding:3px;
            color:#FFF;
            background:#424242;
            border-left:solid 1px #525252;
            text-align:center;
            text-transform:uppercase;
        }
        
        .rating {
            background:#FFF;
            border:none;
            display:block;
        }
        .rating img {
            display:inline-block;
            width:18px;
            height:17px;
            border:0;
            cursor:pointer;
        }

        /*SHOW FILLED STAR IMAGE WHEN MOUSE MOVES OVER THE IMAGES*/
        .rating img:hover {
            background:#FFF url(star-fill.png);
        }
    </style>
</head>
<body>
    <form runat="server">
    <div>
        <div class="container">
            <asp:GridView ID="GridView1" 
                runat="server" 
                AutoGenerateColumns="False" 
                CssClass="grid"
                AllowPaging="true" PageSize="5"
                DataSourceID="SqlDataSource1" DataKeyNames="BookID">

                <Columns>
                    <asp:BoundField DataField="BookID" HeaderText="Book ID" 
                        SortExpression="BookID" InsertVisible="False" ReadOnly="True" />
                    <asp:BoundField DataField="BookName" HeaderText="Book Name" 
                        SortExpression="BookName" />
                    <asp:BoundField DataField="Category" HeaderText="Category" 
                        SortExpression="Category" />

                    <asp:TemplateField HeaderText="Click to Rate">
                        <ItemTemplate>
                            <div style="display:inline-table;width:auto;" id='<%#Eval("BookID") %>'>
                                <%--THE RATING STARS--%>
                                <div class="rating">
                                    <img src="" alt="1" />
                                    <img src="" alt="2" />
                                    <img src="" alt="3" />
                                    <img src="" alt="4" />
                                    <img src="" alt="5" />
                                </div>
                                <%-- SHOW AVERAGE RATE --%>
                                <div style="text-align:center;margin:5px 0 0 0;"> 
                                    Avg. Rating: <span id="avgRate"><%#Eval("AverageRate")%></span>
                                </div>
                                <%-- SHOW TOTAL RATINGS --%>
                                <div style="text-align:center;margin:0 0 10px 0;"> 
                                    Based on <span id="totRatings" style="font-weight:bold;"><%#Eval("TotalRatings")%></span> Ratings
                                </div>
                            </div>
                        </ItemTemplate>
                    </asp:TemplateField>

                </Columns>
            </asp:GridView>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:DNAConnectionString %>" 
            
                <%--THE SQL QUERY THAT WILL FETCH TOTAL AND AVERAGE RATINGS.--%>
                SelectCommand="SELECT B.BookID, B.BookName, B.Category, 
                    COUNT(BR.Rating) TotalRatings, 
                    ISNULL(CAST(CAST(SUM(BR.Rating) AS NUMERIC(5,2)) / 
                        COUNT(BR.Rating) AS NUMERIC(3,1)), 0) AverageRate
                    FROM Books B
                    LEFT OUTER JOIN Books_Rating BR ON B.BookID = BR.BookID
                    GROUP BY B.BookID, B.BookName, B.Category">
            </asp:SqlDataSource>
        </div>
    </div>
        
    </form>
</body>
The SelectCommand property of the SqlDataSource web server control has the SQL query that will return book details along with the Total and Average ratings.

Inside the GridView <Columns> tag, I have added a <asp:TemplateField> tag, with the header Click to Rate. This will help us add custom fields along with other default fields (created using SqlDataSouce).

Inside <ItemTemplate> tag, I have few DIV’s to add images showing 5 Stars in each row, and to display the total and average ratings.

5 Star Rating System GridView

The Web API (MVC 4)

I am assuming you have some knowledge about creating a Web API in Asp.Net. Don’t worry if you are not well versed with it. I have an article for newbie’s, with an extremely simple and yet useful example.

Related: AutoComplete Textbox using jQuery and Web API

The Model “Books.cs” (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace BooksApp.Models
{
    public class Books
    {
        private int m_TotalRatings;
        public int TotalRatings
        {
            get { return m_TotalRatings; }
            set { m_TotalRatings = value; }
        }

        private double m_AverateRatings;
        public double AvgRatings
        {
            get { return m_AverateRatings; }
            set { m_AverateRatings = value; }
        }
    }
}

Also Read: How to Add a Model in Asp.Net Web API project

Model "Books.vb" (Vb.Net)
Imports System.Web

Namespace BooksApp.Models
    Public Class Books
        Public Property TotalRatings() As Integer
            Get
                Return m_TotalRatings
            End Get
            Set(value As Integer)
                m_TotalRatings = value
            End Set
        End Property

        Private m_TotalRatings As Integer

        Public Property AvgRatings() As Double
            Get
                Return m_AverateRatings
            End Get
            Set(value As Double)
                m_AverateRatings = value
            End Set
        End Property

        Private m_AverateRatings As Double
    End Class
End Namespace
The Controller "BooksController.cs" (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Web.http;

using BooksApp.Models;
using System.Data.SqlClient;

namespace BooksApp.Controllers
{
    public class BooksController : ApiController
    {

        List<Books> BooksRatings = new List<Books>();

        string sConnString = "Data Source=pc;Persist Security Info=False;" +
            "Initial Catalog=DNA;User Id=sa;Password=;Connect Timeout=30;";

        public IEnumerable<Books> Get(int iBookID, int iUserRating)
        {

            if (SaveRatings(iBookID, iUserRating))
            {
                getTotalRatings(iBookID);
            }

            return BooksRatings;
        }

        // SAVE RATINGS IN THE TABLE.
        private bool SaveRatings(int iBookID, int iUR)
        {
            bool functionReturnValue = false;

            functionReturnValue = false;

            SqlConnection myConn = new SqlConnection(sConnString);
            myConn.Open();
            SqlCommand objComm = new SqlCommand("INSERT INTO dbo.Books_Rating (BookID, Rating) " + 
                "VALUES (" + iBookID + ", " + iUR + ")", myConn);

            dynamic i = objComm.ExecuteNonQuery();
            myConn.Close();

            if (i >= 1)
            {
                return true;
            }
            return functionReturnValue;

        }

        // RETURN AVERAGE AND TOTAL RATINGS.
        public void getTotalRatings(int iBookID)
        {
            string sQuery = "SELECT COUNT(BR.Rating) TotalRatings, " + 
                "ISNULL(CAST(CAST(SUM(BR.Rating) AS NUMERIC(5,2)) / COUNT(BR.Rating) AS NUMERIC(3,1)), 0) AverageRate " + 
                "FROM dbo.Books_Rating BR " + "WHERE BR.BookID = " + iBookID;

            SqlConnection myConn = new SqlConnection(sConnString);
            SqlCommand objComm = new SqlCommand(sQuery, myConn);

            myConn.Open();

            SqlDataReader reader = objComm.ExecuteReader();
            if (reader.Read())
            {
                BooksRatings.Add(new Books
                {
                    TotalRatings = Convert.ToInt32(reader["TotalRatings"]),
                    AvgRatings = Convert.ToDouble(reader["AverageRate"])
                });
            }

            myConn.Close();
        }
    }
}

Also Read: How to Add a Controller Class in Asp.Net Web API

The Controller "BooksController.vb" (Vb.Net)
Option Explicit On

Imports System.Web.http
Imports BooksApp.BooksApp.Models
Imports System.Data.SqlClient

Namespace BooksApp
    Public Class BooksController
        Inherits ApiController

        Dim BooksRatings As New List(Of Books)()
        Dim sConnString As String = "Data Source=pc;Persist Security Info=False;" & _
            "Initial Catalog=DNA;User Id=sa;Password=;Connect Timeout=30;"
	
        Public Function [Get](ByVal iBookID As Integer, ByVal iUserRating As Integer) As IEnumerable(Of Books)

            If SaveRatings(iBookID, iUserRating) Then
                getTotalRatings(iBookID)
            End If

            Return BooksRatings
        End Function

        ' SAVE RATINGS IN THE TABLE.
        Private Function SaveRatings(ByVal iBookID As Integer, ByVal iUR As Integer) As Boolean

            SaveRatings = False

            Dim myConn As New SqlConnection(sConnString)
            myConn.Open()
            Dim objComm As New SqlCommand("INSERT INTO dbo.Books_Rating (BookID, Rating) " & _
                                          "VALUES (" & iBookID & ", " & iUR & ")", myConn)

            Dim i = objComm.ExecuteNonQuery()
            myConn.Close()

            If i >= 1 Then
                Return True
            End If

        End Function

        ' RETURN AVERAGE AND TOTAL RATINGS.
        Public Sub getTotalRatings(ByVal iBookID As Integer)

            Dim sQuery As String = "SELECT COUNT(BR.Rating) TotalRatings, " & _
                "ISNULL(CAST(CAST(SUM(BR.Rating) AS NUMERIC(5,2)) / COUNT(BR.Rating) AS NUMERIC(3,1)), 0) AverageRate " & _
                "FROM dbo.Books_Rating BR " & _
                "WHERE BR.BookID = " & iBookID

            Dim myConn As New SqlConnection(sConnString)
            Dim objComm As New SqlCommand(sQuery, myConn)

            myConn.Open()

            Dim reader As SqlDataReader = objComm.ExecuteReader()
            If reader.Read() Then
                BooksRatings.Add(New Books() With { _
                    .TotalRatings = CInt(reader("TotalRatings")), _
                    .AvgRatings = reader("AverageRate") _
                })
            End If

            myConn.Close()
        End Sub
    End Class
End Namespace
WebApiConfig File

We need to modify our routing table in the WebApiConfig.cs file (WebApiConfig.vb for Visual Basic), as we are passing multiple parameters (bookid and rating) to the controller.

C#

namespace BooksApp
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{iBookID}/{iUserRating}",
                defaults: new { iBookID = RouteParameter.Optional,
                                iUserRating = RouteParameter.Optional }
            );
        }
    }
}

Vb.Net

Public Class WebApiConfig
    Public Shared Sub Register(ByVal config As HttpConfiguration)
        config.Routes.MapHttpRoute( _
            name:="DefaultApi", _
            routeTemplate:="api/{controller}/{iBookID}/{iUserRating}", _
            defaults:=New With {.iBookID = RouteParameter.Optional,
                                .iUserRating = RouteParameter.Optional} _
        )
    End Sub
End Class

Finally, the jQuery Script with Ajax that will call the web service methods to update new ratings also retrieves total and average ratings.

The Script
<script>
    $(document).ready(function () {
        $('img').attr('src', 'star-empty.png');     // CLEAR ALL THE STARS.

        // SHOW STARS ACCORDING TO AVERAGE RATE OF EACH BOOK.
        $('#GridView1 tr').each(function (index, value) {
            var avg = $(this).find('#avgRate').text();
            $(this).find('.rating > img').each(function () {
                if ($(this).attr('alt') <= avg) {
                    $(this).attr('src', 'star-fill.png');
                }
            });
        });

        // SAVE USER SELECTED RATE IN DB.
        // GET TOTAL AND AVERAGE RATINGS FROM DB AND UPDATE THE ROW.
        $('img').click(function () {

            var userRating = $(this).attr('alt');
            var bookID = $(this).parent().parent().attr('id');

            $.ajax({
                type: "GET",
                url: 'http://localhost:38331/api/books/',
                data: { iBookID: bookID,
                    iUserRating: userRating
                },
                success: function (data) {
                    $.map(data, function () {

                        // REFRESH AVERAGE AND TOTAL RATINGS FOR THE ROW.
                        $('#' + bookID + ' #avgRate').text(data[0].AvgRatings);
                        $('#' + bookID + ' #totRatings').text(data[0].TotalRatings);

                        // UPDATE STARS RATING.
                        $('#' + bookID).find('.rating > img').each(function () {
                            if ($(this).attr('alt') <= data[0].AvgRatings) {
                                $(this).attr('src', 'star-fill.png');
                            }
                            else $(this).attr('src', 'star-empty.png');
                        });
                    });
                },
                error: function (XMLHttpRequest, textStatus, errorThrown) {
                    alert(textStatus);
                }
            });
        });
    });
</script>

That’s it. Hope this example helps you understand and implement a 5 Star rating system in your application. Ask, if you have any queries related to this article.

← PreviousNext →