kganesan on Problem implementing pagination in jqGrid using asp.net

Mon, 10 Jan 2011 12:45:04 +0200

Hi,
I am new to jquery, I am creating an application where in I want to bind my data table to jqgrid and implement sorting, searching and pagination in it.
After some hiccups i have implemented sorting and searching but I am still finding it difficult to implement pagination. Following is my web method and aspx page.

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

<%@ Import Namespace="System.Web.Script.Serialization" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Web.Services" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DT.....t;>
<html xmlns="http://www.w3.org/1999/xhtml&q.....t;>
<head runat="server">
<title></title>
<link href="ui.jqgrid.css" rel="stylesheet" type="text/css" />

<%--<link href="jquery-ui-1.8.2.custom.css" rel="stylesheet" type="text/css" />--%>

$.ajax({
url: 'Default.aspx/GetData',
type: 'POST',
data: JSON.stringify(params),
contentType: "application/json; charset=utf-8",
error: function(data, textStatus) {
alert('Error loading json');
},
success: function(data, st) {
if (st == 'success') {
var grid = $("#list");
var gridData = JSON.parse(data.d);
var total = gridData.length;
grid.clearGridData();
for (var i = 0; i < params.pageSize; i++) {
grid.addRowData(i + 1, gridData[i]);
}
}
}
});
},
colNames: ['Product ID', 'Product Name', 'Product Number'],
colModel: [
{ name: 'ProductID', index: 'ProductID', sort: true, width: 80, align: 'center', sorttype: "int" },
{ name: 'Name', index: 'Name', width: 120, align: 'center' },
{ name: 'ProductNumber', index: 'ProductNumber', width: 120, align: 'center'}],
pager: "#pager",
caption: 'Search: <input type="search" id="gridsearch" placeholder="Search" results="0" class="gridsearch" />',
height: 150,
width: 550,
rowNum: 10,
rowList: [10, 20, 30],
rownumWidth: 40,
sortorder: 'desc',
loadonce: true,
viewRecords: true,
gridComplete: function() {
searchColumn = jQuery("#list").jqGrid('getCol', 'ProductID', true) //needed for live filtering search
},
jsonReader: {
// root: "Rows",
page: "totalpages",
total: "Total",
records: "totalrecords",
repeatitems: false
// id: "ProductID"
}

});
jQuery('#gridsearch').keyup(function() {
var searchString = jQuery(this).val().toLowerCase()
jQuery.each(searchColumn, function() {
if (this.value.toLowerCase().indexOf(searchString) == -1) {
jQuery('#' + this.id).hide()
} else {
jQuery('#' + this.id).show()
}
})
});
});
});
</script>

</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" EnablePageMethods="true" runat="server">
</asp:ScriptManager>
<input type="button" id="submit" value="Fetch" title="Fetch" />
<center>
<table id="list">
</table>
<div id="pager">
</div>
</center>
</form>
</body>
</html>

my web method

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Collections.Generic;
using System.Text;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
using System.Web;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
[WebMethod]
public static string GetData(int page, int pageSize, string sortIndex, string sortDirection)
{
string connectionString = string.Empty;
string queryString = string.Empty;
int pageStartIndex;
int pageEndIndex;
if (page == 1)
{
pageStartIndex = 1;
}
else
{
pageStartIndex = ((page * pageSize) - pageSize) + 1;
}

pageEndIndex = (pageStartIndex + pageSize) - 1;
connectionString = GetConnectionString();
if (HttpContext.Current.Session["Data"] == null)
{
queryString = "SELECT Row_Number() over (order by ProductID ASC) as rowID, ProductID,Name,ProductNumber FROM (Select ROW_NUMBER() OVER (ORDER BY ProductID ASC) AS ROWID," +
"ProductID,Name,ProductNumber FROM [Production].[Product]) AS a";

DataSet ds = new DataSet();
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = connection.CreateCommand();

command.CommandText = queryString;
SqlDataAdapter da = new SqlDataAdapter(queryString, connectionString);
da.Fill(ds, "product");
DataTable dt = ds.Tables["product"];
connection.Close();
HttpContext.Current.Session["Data"] = dt;
}
DataTable dt1 = HttpContext.Current.Session["Data"] as DataTable;
IList<Product> pd = new List<Product>();
if (sortDirection == "" || sortDirection == "asc")
{

for (int k = 0; k < dt1.Rows.Count; k++)
{
Product p = new Product();
p.ProductID = dt1.Rows[k]["ProductID"].ToString();
p.Name = Convert.ToString(dt1.Rows[k]["Name"]);
p.ProductNumber = Convert.ToString(dt1.Rows[k]["ProductNumber"]);
pd.Add(p);
p.total = pd.Count;
p.page = Convert.ToInt32(pd.Count /pageSize);
p.records = pd.Count;
}
}
else
{

for (int k = dt1.Rows.Count - 1; k >= 0; k--)
{
Product p = new Product();
p.ProductID = dt1.Rows[k]["ProductID"].ToString();
p.Name = Convert.ToString(dt1.Rows[k]["Name"]);
p.ProductNumber = Convert.ToString(dt1.Rows[k]["ProductNumber"]);
p.total = pd.Count;
p.page = Convert.ToInt32(pd.Count / pageSize);
p.records = pd.Count;
pd.Add(p);

}
}
JavaScriptSerializer jsonSerz = new JavaScriptSerializer();
string serializedData = jsonSerz.Serialize(pd);
pd = null;
return serializedData;
}

static private string GetConnectionString()
{
return "Data Source=INMDCD0109\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI";
}
}
public class Product
{

public string ProductID { get; set; }

public string Name { get; set; }

public string ProductNumber { get; set; }

public int page { get; set; }

public int total { get; set; }

public int records { get; set; }

}

any help regarding this will be of great help.
Thanks in advance.

jQuery Grid Plugin - jqGrid - Topic: Problem implementing pagination in jqGrid using asp.net

kganesan on Problem implementing pagination in jqGrid using asp.net