JSON GridView/Table using jQuery AJAX and Web Service

We all know that how big page size can grow when we use data bound controls so in those cases using native HTML tags is quite helpful and can make your page much more responsive without compromising functionality on the page.

Today we will look at creating Grid View like control using HTML table and subsequently populating data from database with the help of jQuery, AJAX and ASMX web service. We will also build paging functionality from scratch so page can fetch only applicable data and make efficient utilization of server resources and network bandwidth. Below is what our end product will look like:

For paging functionality, we will use stored procedure about which you can find more details here.

Web service code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Data.SqlClient;

using System.Web.Configuration;

using System.Data;

 

[WebService(Namespace = "http://www.DevTechie.com/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

// To allow this Web Service to be called from script.

[System.Web.Script.Services.ScriptService]

public class ADWDWebService : System.Web.Services.WebService {

// Create GetProduct method, which will

// return product collection of list

 

[WebMethod]

public List<Product> GetProductsByPageProc(int startRowIndex)

{

List<Product> myProducts = new List<Product>();

using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["connStr"].ConnectionString))

{

//Fetching data from Adventure works database

string sqlString = "Article_Product_Paging";

using (SqlCommand cmd = new SqlCommand(sqlString, conn))

{

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@startPageIndex", startRowIndex);

cmd.Parameters.AddWithValue("@endPageIndex", 9);

 

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

 

while (rdr.Read())

{

Product prod = new Product();

prod.ProductID = Convert.ToInt32(rdr["ProductID"]);

prod.Name = rdr["Name"].ToString();

prod.ProductNumber = rdr["ProductNumber"].ToString();

myProducts.Add(prod);

}

conn.Close();

return myProducts;

}

}

}

[WebMethod]

public int GetProductsCountProc()

{

using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["connStr"].ConnectionString))

{

//Fetching data from Adventure works database

int myProductsCount = 0;

string sqlString = "Article_Custom_Count";

using (SqlCommand cmd = new SqlCommand(sqlString, conn))

{

cmd.CommandType = System.Data.CommandType.StoredProcedure;

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (rdr.Read())

{

myProductsCount = Convert.ToInt32(rdr["Count"]);

 

}

conn.Close();

return myProductsCount;

}

}

}

 

}

 

// Create a class name Product and mark it as serializable

[Serializable]

public class Product

{

// Three public properties for holding product data

public int ProductID { get; set; }

public string Name { get; set; }

public string ProductNumber { get; set; }

}

 

 

ASPX Page Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="JSONGridView.aspx.cs" Inherits="JSONGridView" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

<title></title>

<style type="text/css">

*

{

font-family:Arial;font-size:small;

}

#Button1,#btnPrev,#btnNext,#PageNumberGo

{

background-color:lightblue;

border:dashed 1px Gray;

border-style:ridge;

text-transform:capitalize;

}

table

{

border-collapse:collapse;

}

table td,th

{

border:solid 1px Gray;

padding:10px;

text-align:left;

color:Gray;

}

table th

{

background-color:Gray;

color:White;

font-weight:bold;

}

#pagerDiv

{

background-color:Gray;

color:White;

padding:2px;

 

}

#PageNumber

{

float:right;

}

</style>

<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>

 

<script type="text/javascript">

// variable to hold current page count

var pageCounter;

// variable to hold total pages in recordset

var totalPageCount=0;

$('document').ready(function() {

pageCounter = 0;

// function to call count method which return total number of records in resultset

loadDataGrid(pageCounter);

$.ajax({

type: "POST",

url: "ADWDWebService.asmx/GetProductsCountProc",

contentType: "application/json",

async: false,

success: function(data) {

// divide total number of records by 10 (demo page count which can be set to any number)

totalPageCount = Math.round((data.d)/10);

}

});

// function call to pager method

TotalPages();

});

 

function loadDataGrid(count) {

// Create table header

var responseText = " <thead><tr><th>Name</th><th>Product ID</th><th>Product Number</th></tr></thead> <tbody >";

$.ajax({

type: "POST",

url: "ADWDWebService.asmx/GetProductsByPageProc",

// pass page counter variable value to service

data: "{ 'startRowIndex': '" + pageCounter + "' }",

contentType: "application/json",

async: false,

success: function(data) {

$.map(data.d, function(item) {

responseText += '<tr><td>' + item.Name + '</td><td>' + item.ProductID + '</td><td>' + item.ProductNumber + '</td></tr>';

});

responseText += " </tbody>";

$('#ServerResponse').html(responseText);

 

}

});

TotalPages();

}

// function to increament values of pagecount by 10 (any number can be choosen or you can also assign this number dynamically)

function IncreaseCounter() {

pageCounter += 10;

loadDataGrid(pageCounter);

$('#txtGetPageNumber').val('');

}

// funtion to decreament values

function DecreaseCounter() {

pageCounter -= 10;

loadDataGrid(pageCounter);

$('#txtGetPageNumber').val('');

}

// function to get page number from textbox

function PageFromText() {

pageCounter = ($('#txtGetPageNumber').val()-1)*10;

loadDataGrid(pageCounter);

}

// pager function

function TotalPages() {

$('#PageNumber').text('Page ' + Math.round((pageCounter + 10) / 10) + ' out of ' + totalPageCount);

$('#pagerDiv').width($('#ServerResponse').innerWidth()-4);

 

}

</script>

</head>

<body>

<form id="form1" runat="server">

 

<table id="ServerResponse">

 

</table>

 

<div id="pagerDiv">

<input id="btnPrev" type="button" value="Previous" onclick="DecreaseCounter()" />

<input id="btnNext" type="button" value="Next" onclick="IncreaseCounter()"/>

&nbsp;Goto Page:<input id="txtGetPageNumber" type="text" style="width:15px;" /> &nbsp;

<input id="PageNumberGo" type="button" value="Go" onclick="PageFromText()"/>

<span id="PageNumber"></span>

</div>

 

</form>

</body>

</html>

 

Page Preview:

Tags: , , ,

AJAX | ASP.NET | jQuery