Retrieve Data using Web API and jQuery Ajax and Show Data in Table with Paging

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace BooksApp.Models
{
    public class Books
    {
        public int BookId { get; set; }
        public string BookName { get; set; }
        public string Category { get; set; }
        public decimal Price { get; set; }
        public decimal Total { get; set; }
    }
}

Imports System.Web

Namespace BooksApp.Models
    Public Class Books
        Public Property BookID() As Integer
            Get
                Return m_BookID
            End Get
            Set(value As Integer)
                m_BookID = value
            End Set
        End Property
        Private m_BookID As Integer

        Public Property BookName() As String
            Get
                Return m_BookName
            End Get
            Set(value As String)
                m_BookName = value
            End Set
        End Property
        Private m_BookName As String

        Public Property Category() As String
            Get
                Return m_Category
            End Get
            Set(value As String)
                m_Category = value
            End Set
        End Property
        Private m_Category As String

        Public Property Price() As Decimal
            Get
                Return m_Price
            End Get
            Set(value As Decimal)
                m_Price = value
            End Set
        End Property
        Private m_Price As Decimal

        Public Property Total() As Decimal
            Get
                Return m_Total
            End Get
            Set(value As Decimal)
                m_Total = value
            End Set
        End Property
        Private m_Total As Decimal
    End Class
End Namespace

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.http;
using System.Web.http;

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

namespace BooksApp.Controllers
{
    public class BooksController : ApiController
    {

        // LIST OBJECT WILL HOLD AND RETURN A LIST OF BOOKS.
        List<Books> MyBooks = new List<Books>();

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

        public IEnumerable<Books> Get(int iPg)
        {
            GetTheBooks(iPg);
            return MyBooks;
        }

        public void GetTheBooks(int iPage)
        {
            int iPageSize = 5;      // CHANGE THE PAGE SIZE ACCORDING TO YOUR REQUIREMENT.
            decimal iTotalPage = 0;

            SqlConnection myConn = new SqlConnection(sConnString);

            // QUERY TO FETCH BOOKS AS PER THE PAGE COUNT.
            string sSql = +
            "SELECT *, (SELECT COUNT(*) FROM dbo.Books) TotalBooks " +
                "FROM (SELECT ROW_NUMBER() OVER ( ORDER BY BookID ) AS RowNum, * " +
                        "FROM dbo.Books " +
                        ") AS r " +
                        "WHERE RowNum >= ((" + iPageSize + " * " + (iPage == 1 ? 0 : iPage - 1) + " ) + 1) " +
                    "AND RowNum <= (" + iPageSize + " * " + iPage + ") " +
                "ORDER BY RowNum";

            SqlCommand objComm = new SqlCommand(sSql, myConn);
            myConn.Open();

            SqlDataReader reader = objComm.ExecuteReader();

            // POPULATE THE LIST WITH DATA.
            while (reader.Read())
            {
                iTotalPage = (int)reader["TotalBooks"];
                iTotalPage = Math.Round(iTotalPage / iPageSize, 0);

                MyBooks.Add(new Books { 
                    BookId = (int)reader["BookID"], 
                    BookName = reader["BookName"].ToString(),
                    Category = reader["Category"].ToString(),
                    Price = Convert.ToDecimal(reader["Price"]),
                    Total = iTotalPage
                });
            }

            myConn.Close();
        }
    }
}

Option Explicit On

Imports System.Net.http
Imports System.Web.http

Imports System.Data.SqlClient
Imports BooksApp.BooksApp.Models

Namespace BooksApp
    Public Class BooksController
        Inherits ApiController

        ' LIST OBJECT WILL HOLD AND RETURN A LIST OF BOOKS.
        Dim MyBooks As New List(Of Books)()

        Const sConnString As String = "Data Source=DEMO;Persist Security Info=False;" & _
           "Initial Catalog=DNA_Classified;User Id=sa;Password=sss;Connect Timeout=30;"

        Public Function [Get](iPg As Integer) As IEnumerable(Of Books)
            GetTheBooks(iPg)
            Return MyBooks
        End Function

        Public Sub GetTheBooks(ByVal iPage As Integer)

            Dim iPageSize As Integer = 5        ' CHANGE THE PAGE SIZE ACCORDING TO YOUR REQUIREMENT.
            Dim iTotalPage As Integer = 0

            Using con As SqlConnection = New SqlConnection(sConnString)

                ' QUERY TO FETCH BOOKS AS PER THE PAGE COUNT.
                Dim sSQL = & _
                "SELECT *, (SELECT COUNT(*) FROM dbo.Books) TotalBooks " & _
                    "FROM (SELECT ROW_NUMBER() OVER ( ORDER BY BookID ) AS RowNum, * " & _
                            "FROM dbo.Books " & _
                            ") AS r " & _
                    "WHERE RowNum >= ((" & iPageSize & " * " & IIf(iPage = 1, 0, iPage - 1) & " ) + 1) " & _
                        "AND RowNum <= (" & iPageSize & " * " & iPage & ") " & _
                    "ORDER BY RowNum"

                Dim objComm As New SqlCommand(sSQL, con)
                con.Open()

                Dim reader As SqlDataReader = objComm.ExecuteReader()

                ' POPULATE THE LIST WITH DATA.
                While reader.Read()
                    iTotalPage = reader("TotalBooks") / iPageSize

                    MyBooks.Add(New Books() With { _
                        .BookID = CInt(reader("BookID")), _
                        .BookName = reader("BookName").ToString(), _
                        .Category = reader("Category").ToString(), _
                        .Price = CDbl(reader("Price")), _
                        .Total = iTotalPage
                     })
                End While

                con.Close()
            End Using
        End Sub

    End Class
End Namespace

<!DOCTYPE html>
<html>
<head>
    <title>Extract Data from Database using jQuery Ajax and Web API and Display with Paging</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>

    <style>
        th, td {
            border:solid 1px #EEE;
            padding:2px 3px;
            text-align:center;
        }
        div {
            width:500px;
            margin:0;
            padding:0;
        }
        .pageCountActive, .pageCountInactive {
            position:relative;
            float:left;
            width:auto;
            border:solid 1px #EEE;
            text-align:center;
            padding:2px 3px;
            margin:1px;
            cursor:pointer;
            color:#1464F4;
        }
        .pageCountInactive {
            color:#999;
            cursor:default;
        }
        .pageCountActive a {
            text-decoration:none;
        }
    </style>
</head>
<body>
    <div id="showData"></div>
</body>

<script>
    $(document).ready(function () {
        BindControls();
    });

    function BindControls() {

        var page = 1;           // START WITH FIRST PAGE OF THE GRID, WHEN THE WEB PAGE LOADS FOR THE FIRST TIME.
        performCrud(page);

        function performCrud(pg) {
            // CALL WEB API BY MAKING AN AJAX CALL. THE "URL" TAKES A PARAMETER (THE PAGE NUMBER).

            $.ajax({
                type: "GET",
                url: "/api/books/" + pg,
                success: function (data) {

                    var i = 0;          // JUST A COUNTER.
                    var myBooks = [];
                    var col = [];

                    $.map(data, function () {
                        myBooks.push(data[i]);      // GET ALL THE BOOKS (DATA).
                        i += 1;
                    });

                    // CREATE A TABLE (WHERE WE'LL THE DATA).
                    var table = document.createElement('table');

                    // GET VALUES FOR OUR TABLE HEADER. (THE HEADERS ARE THE DATABASE TABLE COLUMNS).
                    for (var i = 0; i < myBooks.length; i++) {
                        for (var key in myBooks[i]) {
                            if (col.indexOf(key) === -1) {
                                col.push(key);          // ADD THE HEADERS.
                            }
                        }
                    }

                    // TABLE ROW.
                    var tr = table.insertRow(-1);

                    // CREATE TABLE COLUMN HEADERS.
                    for (i = 0; i < col.length; i++) {
                        var th = document.createElement("th");
                        th.innerHTML = col[i];
                        tr.appendChild(th);
                    }

                    // ADD EXTRACTED DATA TO THE TABLE AS ROWS.
                    for (var i = 0; i < myBooks.length; i++) {

                        tr = table.insertRow(-1);

                        for (var j = 0; j < col.length; j++) {
                            var tabCell = tr.insertCell(-1);
                            tabCell.innerHTML = myBooks[i][col[j]];     // GET BOOK DETAILS FOR EACH HEADER.
                        }
                    }

                    // ADD THE TABLE WITH A CONTAINER.
                    var divContainer = document.getElementById('showData');
                    divContainer.innerHTML = "";
                    divContainer.appendChild(table);


                    // FINALLY CREATE THE PAGERS AT THE BOTTOM OF THE GRID.

                    var divPager = document.createElement('div');

                    for (i = 1; i <= myBooks[0].Total; i++) {
                        var divPageCount = document.createElement('div');
                        if (pg == i)        // CHECK IF THE PAGE IS ACTIVE.
                            divPageCount.innerHTML = '<span class="pageCountInactive">' + i + '</span>';
                        else
                            divPageCount.innerHTML = '<span class="pageCountActive" id=' + i + '>' + i + '</span>';

                        divPager.append(divPageCount);
                    }

                    divContainer.append(divPager);
                },
                error: function (XMLHttpRequest, textStatus, errorThrown) {
                    alert(textStatus);
                }
            });
        }

        // NOW MAKE THE PAGERS WORK. 
        // ON EACH ACTIVE PAGER'S CLICK EVENT, 
        // CALL FUNCTION WITH ITS ID, TO VIEW THE NEXT OR PREVIOUS PAGE.
        $(document).on('click', '.pageCountActive', function () {
            performCrud($(this).attr('id'));
        });
    }
</script>
</html>