More 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 an HTML table dynamically after extracting the data and apply paging to the table. You can find many tools on the web today for displaying data using paging. This example in the post shows you how to create a tool with similar features at the client side using jQuery Ajax.
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.
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 NamespaceThe 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 NamespaceThe 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="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>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
The example here helps understand 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 table using Asp.Net Web API and jQuery Ajax. You can use any other data source, for example XML.
Thank's for reading. ☺