JSON : Gridview Loading Data when div scroll down by Using JQuery


loading data on page scrolling down by using jquery

In this article I'm going to explain how to load data on gridview control by using Jquery. In some cases to improve the performance of application we are loading data on demand basis, rather than load all the records at a time.

Create HTML Design

Design div tag and place a gridview control inside that and set the div style property of overflow to auto.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="GridviewLoadingData.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Jscript/jquery-2.2.3.min.js"></script>
    <script type="text/javascript">
      //Add JQuery| Javascript Script Here
    </script>
</head>
<body>
    <form id="form1" runat="server">
        
    <div>
        <table class="Grid" cellspacing="0" rules="all" border="1" id="Table1" style="width: 400px; border-collapse: collapse;">
    <tr>
       <th scope="col" style="width: 200px;">Student Name</th>
       <th scope="col" style="width: 200px;">Address</th>
    </tr>
</table>
     <div id="dvGrid" style="height: 150px; overflow: auto; width: 417px">
   <asp:GridView ID="gvStudentInfo" runat="server" AutoGenerateColumns="false" CssClass="grid" Width="400">
       <Columns>
          <asp:BoundField DataField="Student_Name" HeaderText="Student Name" ItemStyle-Width="200" HeaderStyle-Width="200" />
          <asp:BoundField DataField="Addres" HeaderText="Address" ItemStyle-Width="200" HeaderStyle-Width="200" />
       </Columns>
   </asp:GridView>
</div>
    </div>
    </form>
</body>
</html>

Database

Creating Stored Procedure :

Since we are loading data on demand I prepare stored procedure with paging option, because we are loaded records based on index and page count.

CREATE PROCEDURE GetStudentsPageWise
@PageIndex INT, @PageSize INT, @PageCount INT OUTPUT
AS
/*
    pagging in procedure side
*/
BEGIN
    SET NOCOUNT ON;
    SELECT ROW_NUMBER() OVER ( ORDER BY [StudentID] ASC) AS RowNumber,
           [StudentID],
           [Student_Name],
           [Addres]
    INTO   #Results
    FROM   StudentInfo;
    DECLARE @RecordCount AS INT;
    SELECT @RecordCount = COUNT(*)
    FROM   #Results;
    SET @PageCount = CEILING(CAST (@RecordCount AS DECIMAL (10, 2)) / CAST (@PageSize AS DECIMAL (10, 2)));
    SELECT *
    FROM   #Results
    WHERE  RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 AND (((@PageIndex - 1) * @PageSize + 1) + @PageSize) - 1;
    DROP TABLE #Results;
END 

C# Coding - Add Namespaces

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace GridviewLoadingData
{
    public partial class WebForm1 : System.Web.UI.Page
    {

    }
}

C# Coding - Page Loading

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                gvStudentInfo.DataSource = GetStudentsPageWise(1, 10);
                gvStudentInfo.DataBind();
            }
        }

C# Coding - Binding Gridview Control

In the block of code based on PageIndex and PageSize as requested it's loading the records, but as per design we are fixing the height of the div tag is 150px; when the records height meets that range then scroll bar is enabled, whenever we scroll the header content will move to top, we can't able to see the header if we scroll down the records. To overcome this situation we can rearrange markup for fixed header like below.

 public static DataSet GetStudentsPageWise(int pageIndex, int pageSize)
        {
            string constring = ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constring))
            {
                using (SqlCommand cmd = new SqlCommand("[GetStudentsPageWise]"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                    cmd.Parameters.AddWithValue("@PageSize", pageSize);
                    cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataSet ds = new DataSet())
                        {
                            sda.Fill(ds, "Students");
                            DataTable dt = new DataTable("PageCount");
                            dt.Columns.Add("PageCount");
                            dt.Rows.Add();
                            dt.Rows[0][0] = cmd.Parameters["@PageCount"].Value;
                            ds.Tables.Add(dt);
                            return ds;
                        }
                    }
                }
            }
        }

C# Coding - Create WebServices

[WebMethod]
        public static string GetStudents(int pageIndex)
        {
            System.Threading.Thread.Sleep(2000);
            return GetStudentsPageWise(pageIndex, 10).GetXml();
        }

JQuery | Javascript :

Remove GridView Header Using JQuery :

this script is used to remove gridview control default header section and it will display gridview body without header

$(function () {
   //Remove the original GridView header
   $("[id$=gvStudentInfo] tr").eq(0).remove();
});

Load Data in Gridview while Scrolling

If we want to load database records (server side )using Jquery we have only one option i.e. using AJAX call, for that we require service method.
For the above scenario I already create a service method in my project you can see in above c# coding block.

Now, the next step is call the above (WebServices) webmethod using AJAX and append the records to gridview control. Use below code snippet for that.

<script type="text/javascript" src="../1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    var pageIndex = 1;
    var pageCount;
    $(function () {
        //Remove the original GridView header
        $("[id$=gvStudentInfo] tr").eq(0).remove();
    });
           
    //Load GridView Rows when DIV is scrolled
    $("#dvGrid").on("scroll", function (e) {
        var $o = $(e.currentTarget);
        if ($o[0].scrollHeight - $o.scrollTop() <= $o.outerHeight()) {
            LoadRecords();
        }
    });

    //Function to make AJAX call to the Web Method
    function LoadRecords() {
       pageIndex++;
       if (pageIndex == 2 || pageIndex <= pageCount) {

            //Show Loader
            if ($("[id$=gvStudentInfo] .loader").length == 0) {
                var row = $("[id$=gvStudentInfo] tr").eq(0).clone(true);
                row.addClass("loader");
                row.children().remove();
                row.append('<td colspan = "999" style = "background-color:white"><img id="loader" alt="" src="loaderimage.gif"  /></td>');
                $("[id$=gvStudentInfo]").append(row);
            }
                    
            $.ajax({
                 type: "POST",
                 url: "CS.aspx/GetStudents",
                 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 to recieve XML response append rows to GridView
    function OnSuccess(response) {
       var xmlDoc = $.parseXML(response.d);
       var xml = $(xmlDoc);
       pageCount = parseInt(xml.find("PageCount").eq(0).find("PageCount").text());
       var students = xml.find("Students");
       $("[id$=gvStudentInfo] .loader").remove();
       students.each(function (index) {
       var student = $(this);
                 
       AppendNewRow ('[id$=gvStudentInfo]', '<tr><td>' + student.find("Student_Name").text() + '</td><td>' + student.find("Addres").text() + '</td></tr>');
       });

       //Hide Loader
       $("#loader").hide();
   }

   function AppendNewRow(table, rowcontent) {
       if ($(table).length > 0) {
          if ($(table + ' > tbody').length == 0) 
              $(table).append('');
          ($(table + ' > tr').length > 0) ? $(table).children('tbody:last').children('tr:last').append(rowcontent) : $(table).children('tbody:last').append(rowcontent);
       }
   }
</script>

View Demo :


0 Komentar untuk "JSON : Gridview Loading Data when div scroll down by Using JQuery"

Back To Top