Not able to implement pagination in JQGRID using ASP.NET

Question

I have implemented a simple code to bind data to jqgrid in asp.net, I initially had problem with sorting the grid but was able to overcome the same. Now my concern is I am not able to implement pagination in Jqgrid. It would be great if anybody can help me out with this. Here is my aspx code.

<%@ 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/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link href="ui.jqgrid.css" rel="stylesheet" type="text/css" />

    <script type="text/javascript" src="jquery-1.4.4.min.js"></script>

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

    <script src="grid.locale-en.js" type="text/javascript"></script>

    <script src="jquery.jqGrid.min.js" type="text/javascript"></script>

    <script src="json2.js" type="text/javascript"></script>

    <link href="jquery-ui-1.8.7.custom.css" media="screen" rel="stylesheet" type="text/css" />

    <script type="text/javascript">
        $(document).ready(function() {
            $('#submit').click(function() {
                $('#list').jqGrid({
                    datatype: function(postdata) {

                        var params = new Object();
                        params.page = postdata.page;
                        params.pageSize = postdata.rows;
                        params.sortIndex = postdata.sidx;
                        params.sortDirection = postdata.sord;

                        $.ajax({
                            url: 'Default.aspx/GetData',
                            type: 'POST',
                            data: JSON.stringify(params),
                            // dataType: "json",     
                            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);
                                    grid.clearGridData();
                                    for (var i = 0; i < gridData.length; 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"),
                    height: 200,
                    width: 600,
                    rowNum: 20,
                    rowList: [10, 20, 30],
                    rownumWidth: 40,
                    sortorder: 'desc',
                    loadonce: true,
                    records: 20,
                    viewRecords: true
                });
            });
        });
        //        }).navGrid('#pager', { search: true, edit: false, add: false, del: false, searchtext: "Search" });       
    </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" />
    <table id="list">
    </table>
    <div id="pager">
    </div>
    </form>
</body>
</html>

And this is my code behind page

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;

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 = GetConnectionString();
        string queryString = string.Empty;
        if (sortIndex == "")
        {
            queryString = "SELECT top " + pageSize + " ProductID,Name,ProductNumber FROM [AdventureWorks].[Production].[Product]";

        }
        else
        {
            queryString = "SELECT top " + pageSize + " ProductID,Name,ProductNumber FROM [AdventureWorks].[Production].[Product] ORDER BY " + sortIndex + " " + sortDirection;
        }
        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"];
        IList<Product> pd = new List<Product>();
        connection.Close();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Product p = new Product();
                p.ProductID = dt.Rows[i]["ProductID"].ToString();
                p.Name = Convert.ToString(dt.Rows[i]["Name"]);
                p.ProductNumber = Convert.ToString(dt.Rows[i]["ProductNumber"]);
                pd.Add(p);
                p = null;
            }

        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; }
}

Thanks in advance

score 0 · Answer 1 · edited May 23 '17 at 09:58

I wrote you already in the previous my answer many suggestion how to improve your code. Because you don't write any comment to my previous answer I will be short now.

I you need implement pagination, it is enough to use statement like SELECT TOP 10 only for the first page from 10 rows. If you need to return for example the 6-th page you need skip 50 first rows and then get the next 10. If the data which you return contain the id (ProductID in your case) you can do this for example with the following statement using common table expression (CTE):

WITH Previous (ProductID,Name,ProductNumber) AS (
    SELECT TOP 50 ProductID,Name,ProductNumber
    FROM [AdventureWorks].[Production].[Product]
)
SELECT TOP 10 T.ProductID,T.Name,T.ProductNumber
FROM [AdventureWorks].[Production].[Product] AS T
    LEFT OUTER JOIN Previous AS P ON T.ProductID=P.ProductID
WHERE P.ProductID IS NULL

If the returned data contain no id, then you can use ROW_NUMBER to implement pagination.

UPDATED: One more small remark. The usage of DataSet, DataTable and SqlDataAdapter in your example is not effective. The usage of command.ExecuteReader (SqlCommand::ExecuteReader) which returns SqlDataReader seems me better here.

Not able to implement pagination in JQGRID using ASP.NET

1 Answers1