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

Database

For this article I have used Microsoft’s Northwind database. You can download it using the link provided below

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;

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

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();

}

 

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

            }

        }

    }

}

 

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 GetCustomers which 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