Bind data to GridView with jQuery or JSON in ASP.Net

In this article I will explain how to do paging in ASP.Net GridView using jQuery AJAX

 

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

Below is the HTML Markup. It has an ASP.Net GridView gvCustomers and an HTML DIV control where the Pager will be populated for pagination

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" RowStyle-BackColor="#A1DCF2"

    HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White">

    <Columns>

        <asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />

        <asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name" />

        <asp:BoundField ItemStyle-Width="150px" 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.Web.Services;

using System.Configuration;

using System.Data.SqlClient;

 

VB.Net

Imports System.Data

Imports System.Web.Services

Imports System.Configuration

Imports System.Data.SqlClient

 

Populating GridView Dummy with Dummy Data

In Page Load event of the page I am populating the GridView with dummy data so that we can use its HTML Table structure to populate data using jQuery.

I have created a variable PageSize which will decide the number of records to be 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 (!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 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

 

Pagination SQL Server Stored Procedure

Following is the stored procedure which will perform pagination in SQL Server database and return per page data

CREATE PROCEDURE [dbo].[GetCustomers_Pager]

      @PageIndex INT = 1

      ,@PageSize INT = 10

      ,@RecordCount INT OUTPUT

AS

BEGIN

      SET NOCOUNT ON;

      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

 

 

WebMethod to handle jQuery AJAX calls

Below is the Web Method which is being called by the jQuery AJAX function which populates  the GridView with data and also does Pagination.

The PageIndex is passed as parameter from Client side based on what is Page is clicked by the user, while the PageSize we get from the static variable we have declared. Based on these parameters we can fetch the records Page wise.

The records fetched along with the Total Record Count are sent to Client Side as XML string.  

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

Below is the complete Client Side Implementation, here on load event of jQuery the GridView is populated with PageIndex 1 using the method GetCustomerswhich populates the ASP.Net GridView which was initially populated with dummy data.

Using the Record Count, the pager is populated inside the HTML DIV using the ASPSnippets_Pager jQuery Plugin.

<script type="text/javascript" src="http://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.d);

            },

            error: function (response) {

                alert(response.d);

            }

        });

    }

 

    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

Below is the necessary CSS styles which you need to put on your 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

Below is the screenshot of ASP.Net GridView with Client Side Paging using jQuery AJAX

Nguồn tham khảo: http://www.aspsnippets.com/Articles/Bind-data-to-GridView-with-jQuery-or-JSON-in-ASPNet.aspx