In this article I will explain with an example, how to bind data to GridView with using jQuery or JSON in ASP.Net with C# and VB.Net.
This article will also illustrate how to implement Client Side Paging using jQuery AJAX.
Client Side Paging in GridView will be implemented using ASPSnippets_Pager jQuery Plugin.
 
 
Download ASPSnippets_Pager Plugin
You can download ASPSnippets_Pager jQuery Plugin using the link provided below.
Download ASPSnippets_Pager Plugin
 
 
Database
For this article I have used Microsoft’s Northwind database. You can download it using the link provided below.
Download Northwind Database
 
 
HTML Markup
The following HTML Markup consists of an ASP.Net GridView control with three BoundField columns.
There's also an HTML DIV which will be used to populate the Pages for Pagination.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" />
        <asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
        <asp:BoundField DataField="City" HeaderText="City" />
    </Columns>
</asp:GridView>
<br />
<div class="Pager"></div>
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;
 
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services
 
 
Populating GridView with Dummy data
Inside the Page Load event, the GridView is populated with dummy records.
Note: This is very important as empty GridView will not render in web page. For more details, please refer my article Dynamically create DataTable and bind to GridView in ASP.Net.
 
The variable PageSize will be used to determine the number of records displayed per Page in GridView.
Note: The dummy DataTable being populated has the same columns which will be returned from the SQL Query.
 
C#
private static int PageSize = 10;
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        BindDummyRow();
    }
}
 
private void BindDummyRow()
{
    DataTable dummy = new DataTable();
    dummy.Columns.Add("CustomerID");
    dummy.Columns.Add("ContactName");
    dummy.Columns.Add("City");
    dummy.Rows.Add();
    gvCustomers.DataSource = dummy;
    gvCustomers.DataBind();
}
 
VB.Net
Private Shared PageSize As Integer = 10
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        BindDummyRow()
    End If
End Sub
 
Private Sub BindDummyRow()
    Dim dummy As New DataTable()
    dummy.Columns.Add("CustomerID")
    dummy.Columns.Add("ContactName")
    dummy.Columns.Add("City")
    dummy.Rows.Add()
    gvCustomers.DataSource = dummy
    gvCustomers.DataBind()
End Sub
 
 
Stored Procedure for Paging
The following Stored Procedure makes use of Row_Number function to fetch records Page Wise from SQL Server Database Table.
The PageIndex and PageSize values are passed as parameter and the Stored Procedure returns the Total records of the Table using the RecordCount Output parameter.
Note: For more details, please refer my article Paging in SQL Server Stored Procedure with Total Row Count.
 
CREATE PROCEDURE [dbo].[GetCustomers_Pager]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
    SET NO COUNTON;
    SELECT ROW_NUMBER()OVER
    (
        ORDER BY [CustomerID] ASC
    ) AS RowNumber
        ,[CustomerID]
        ,[CompanyName]
        ,[ContactName]
        ,[City]
    INTO #Results
    FROM [Customers]
    
    SELECT @RecordCount = COUNT(*)
    FROM #Results
          
    SELECT * FROM #Results
    WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 AND (((@PageIndex - 1) * @PageSize + 1) +  @PageSize) - 1
    
    DROP TABLE #Results
END
 
 
The Web Method
The following WebMethod handles call made from the jQuery AJAX function.
Inside the WebMethod, the Paging Stored Procedure is called with the input parameters PageIndex and PageSize and results are populated into a DataSet along with the PageIndex, PageSize and RecordCount values.
The DataSet is converted into an XML string and sent to the jQuery AJAX function.
Note: Total Record Count is necessary to populate pager.
 
C#
[WebMethod]
public static string GetCustomers(int pageIndex)
{
    string query = "[GetCustomers_Pager]";
    SqlCommand cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    cmd.Parameters.AddWithValue("@PageSize", PageSize);
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
    return GetData(cmd, pageIndex).GetXml();
}
 
private static DataSet GetData(SqlCommand cmd, int pageIndex)
{
    string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds, "Customers");
                DataTable dt = new DataTable("Pager");
                dt.Columns.Add("PageIndex");
                dt.Columns.Add("PageSize");
                dt.Columns.Add("RecordCount");
                dt.Rows.Add();
                dt.Rows[0]["PageIndex"] = pageIndex;
                dt.Rows[0]["PageSize"] = PageSize;
                dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
                ds.Tables.Add(dt);
                return ds;
            }
        }
    }
}
 
VB.Net
<WebMethod>
Public Shared Function GetCustomers(pageIndex As Integer) As String
    Dim query As String = "[GetCustomers_Pager]"
    Dim cmd As New SqlCommand(query)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
    cmd.Parameters.AddWithValue("@PageSize", PageSize)
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
    Return GetData(cmd, pageIndex).GetXml()
End Function
 
Private Shared Function GetData(cmd As SqlCommand, pageIndex As Integer) As DataSet
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Using con As New SqlConnection(strConnString)
        Using sda As New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using ds As New DataSet()
                sda.Fill(ds, "Customers")
                Dim dt As New DataTable("Pager")
                dt.Columns.Add("PageIndex")
                dt.Columns.Add("PageSize")
                dt.Columns.Add("RecordCount")
                dt.Rows.Add()
                dt.Rows(0)("PageIndex") = pageIndex
                dt.Rows(0)("PageSize") = PageSize
                dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
                ds.Tables.Add(dt)
                Return ds
            End Using
        End Using
    End Using
End Function
 

Client Side Implementation
Inside the jQuery document ready event handler, first an AJAX call is made to the WebMethod and the records for the First page is fetched.
The value of the PageIndex, PageSize and RecordCount are used to populate the Pager inside the HTML DIV using the ASPSnippets_Pager jQuery Plugin.
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetCustomers(1);
    });
    $(".Pager .page").live("click", function () {
        GetCustomers(parseInt($(this).attr('page')));
    });
    function GetCustomers(pageIndex) {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetCustomers",
            data: '{pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: OnSuccess,
            failure: function (response) {
                alert(response.responseText);
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    }
 
    function OnSuccess(response) {
        var xmlDoc = $.parseXML(response.d);
        var xml = $(xmlDoc);
        var customers = xml.find("Customers");
        var row = $("[id*=gvCustomers] tr:last-child").clone(true);
        $("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();
        $.each(customers, function () {
            var customer = $(this);
            $("td", row).eq(0).html($(this).find("CustomerID").text());
            $("td", row).eq(1).html($(this).find("ContactName").text());
            $("td", row).eq(2).html($(this).find("City").text());
            $("[id*=gvCustomers]").append(row);
            row = $("[id*=gvCustomers] tr:last-child").clone(true);
        });
        var pager = xml.find("Pager");
        $(".Pager").ASPSnippets_Pager({
            ActiveCssClass: "current",
            PagerCssClass: "pager",
            PageIndex: parseInt(pager.find("PageIndex").text()),
            PageSize: parseInt(pager.find("PageSize").text()),
            RecordCount: parseInt(pager.find("RecordCount").text())
        });
    };
</script>
 

CSS
The following CSS classes will needed to be inherited on the web page.
<style type="text/css">
    body
    {
        font-family: Arial;
        font-size: 10pt;
    }
    .Pager span
    {
        text-align: center;
        color: #999;
        display: inline-block;
        width: 20px;
        background-color: #A1DCF2;
        margin-right: 3px;
        line-height: 150%;
        border: 1px solid #3AC0F2;
    }
    .Pager a
    {
        text-align: center;
        display: inline-block;
        width: 20px;
        background-color: #3AC0F2;
        color: #fff;
        border: 1px solid #3AC0F2;
        margin-right: 3px;
        line-height: 150%;
        text-decoration: none;
    }
</style>
 
 
Screenshot
Bind data to GridView with jQuery or JSON in ASP.Net
 
 
Demo
View Demo
 
 
Downloads
Download Code