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.
<!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.
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.
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; }
}
}
}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 NamespaceThe 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();
}
}
}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 NamespaceWebApiConfig 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 ClassFinally, 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>