Wednesday, February 22

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 = newSqlConnection(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 = newSqlConnection(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 { getset; }
public string Name { getset; }
public string ProductNumber { getset; }
}


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:

Thanks Shibashish Mohanty

1 comment:

Please don't spam, spam comments is not allowed here.

.

ShibashishMnty
shibashish mohanty