Extract Data using Asp.Net Web API C# and jQuery Ajax and Display the Data with Paging

Web developers using Asp.Net, commonly use the GridView control to display data in a tabular format. The GridView comes with many useful features, especially paging . Here in this post I’ll show you a simple example on how to display data in tabular format with paging by extracting data from an SQL Server database table using Asp.Net Web API and jQuery Ajax.

Extract or Get Data using jQuery Ajax and Web API and Show Data with Paging

Also Read: Useful Examples using Web API

I am not using a GridView control here. Though very useful, most of GridView’s features are available at the server side. Here however, I’ll create a table dynamically after extracting the data and apply paging to it. You can find many tools on the web today for displaying data using paging. This example here shows you how you can create a tool with similar features at the client side using jQuery Ajax.

Related: Dynamically Create Textboxes and Button and Save the Data to a Database

SQL Server Table

The API will extract data from an SQL Server database table called the Books. Here you can find the table.

After you have created the table, let’s build the Web API in Asp.Net, which will have a controller and a model.

Related: Build you first Web API in Asp.Net (MVC 4) – C# and Vb.Net

The Web API Model (Books.cs in C#)

Let’s begin by creating a Model for our API that will have few properties in it.

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; }
    }
}
The Model Books.vb (Vb.Net)
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
The Controller “BooksController.cs” (C#)
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();
        }
    }
}
The Controller “BooksController.vb” (Vb.Net)
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
The Markup

Now let’s design our page. I only have a <div> element on my web page (at the beginning), which will serve as a container. I’ll create a <table> with headers, rows and paging dynamically using jQuery and add the table to the container.

I have also created few CSS classes, which I’ll add to my elements dynamically through the script. The CSS especially, plays an important in the pagination algorithm using jQuery.

<!DOCTYPE html>
<html>
<head>
    <title>Extract Data from Database using jQuery Ajax and Web API and Display with Paging</title>
    <script src="http://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 {
            font:15px Verdana;
            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>
The Script
<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>

Well that’s it.

Conclusion

This is just an example, which helps in understanding the basics of creating a lightweight control using dynamically created HTML table to show data with “paging” option at the “client side”. We are extracting data from an SQL Server database using Asp.Net Web API and jQuery Ajax. You can use any other data source, for example XML.

Thank's for reading.

<<Previous - Check if Element <div> Contains a <p> Element in jQuery

Next - Binding Click Event to a Dynamically Created Element using jQuery>>



Related Posts:

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+
comments powered by Disqus

Join our Google Plus Community and be a part of a discussion!