Bind Asp.net GridView with jQuery or JSON using C# or VB.Net

In this post, I'm sharing with you how to bind Asp.Net GridView with Json  or jQuery Data. Code logic will be written in both languages i.e; (C-sharp & VB.Net)

Let's start development. Do the following parameters;

• Add a web page (.aspx)
• Drag & Drop GridView to that page
• Download Nothwind Database from here

In your HTML markup, you have Gridview control and now place an HTML DIV control where the Pager will be populated for pagination.

<asp:GridView ID="gvCustomer" runat="server" AutoGenerateColumns="false" 
    RowStyle-BackColor="#A1DCF2" HeaderStyle-BackColor="#3AC0F2" 
    HeaderStyle-ForeColor="White">
    <Columns>
        <asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="ID" />
        <asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Name" />
        <asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />
    </Columns>
</asp:GridView>
<br />
<div class="Pager"></div>

Here we need to add some 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

Now, to add pagination feature to gridview, I'm populating GridView with dummy data so that we can use its to populate data using jQuery. To show custom page size, I've created a variable "PageSize" which will decide the number of records to be displayed per page. 

NOTE: For dummy data population to GridView, make sure that you have to set same columns which will be returned from your database query.

--------------------------
          C#
--------------------------
private static int _PageSize = 10;

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDummyRow();
    }
}
 
private void BindDummyRow()
{
    var dummyTable = new DataTable();
    dummyTable.Columns.Add("CustomerID");
    dummyTable.Columns.Add("ContactName");
    dummyTable.Columns.Add("City");
    dummyTable.Rows.Add();

    gvCustomer.DataSource = dummyTable;
    gvCustomer.DataBind();
}
--------------------------
          VB.NET
--------------------------
Private Shared _PageSize As Integer = 10

Protected Sub Page_Load(sender As Object, e As EventArgs)
    If Not IsPostBack Then
        BindDummyRow()
    End If
End Sub

Private Sub BindDummyRow()
    Dim dummyTable = New DataTable()
    dummyTable.Columns.Add("CustomerID")
    dummyTable.Columns.Add("ContactName")
    dummyTable.Columns.Add("City")
    dummyTable.Rows.Add()

    gvCustomer.DataSource = dummyTable
    gvCustomer.DataBind()
End Sub

To do custom Pagination in GridView, add SQL Server Stored Procedure which will perform pagination and return per page data.

CREATE PROCEDURE [dbo].[GetCustomer_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

To handle jQuery Ajax call we use "WebMethod". The web method is being called by jQuery AJAX function which populated the Gridview with data & also does Pagination. In pagination, we handle these parameters;

• PageIndex: It is passed as parameter from client-side based on what is Page clicked by the user.
• PageSize: This is get from static variable we have declared. Based on these paramters we can fetch the records Page wise.

The records fetched along with TotalRecordCount are sent to client-side as XML string. 

NOTE: TotalRecordCount is necessary to populate pager.

------------------
       C#
------------------
[WebMethod]
public static string GetCustomerList(int pageIndex)
{
    string query = "[GetCustomer_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["NorthwindConnectionString"].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 GetCustomerList(pageIndex As Integer) As String
    Dim query As String = "[GetCustomer_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("NorthwindConnectionString").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

Now we have to add some client-side implementation. During loading, populate GridView with PageIndex = 1 using the method "GetCustomers" which populates the gridview which was initially filled with dummy data. Using RecordCount, the pager is populated inside div using Pager jQuery plugin.

<script type="text/javascript" 
        src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js">
</script>
<script type="text/javascript"
        src="http://aspsnippets.com/demos/296/ASPSnippets_Pager.min.js">
</script>

<script type="text/javascript">

    $(function () {
        GetCustomer(1);
    });

    $(".Pager .page").live("click", function () {
        GetCustomer(parseInt($(this).attr('page')));
    });

    function GetCustomer(pageIndex) {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetCustomerList",
            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*=gvCustomer] tr:last-child").clone(true);
        $("[id*=gvCustomer] tr").not($("[id*=gvCustomer] 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*=gvCustomer]").append(row);
            row = $("[id*=gvCustomer] 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>

Now finally add some CSS styling which makes Gridview better.

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

Hope you understand the flow. Cheers