In this article I will explain with an example, how to load GridView on scroll in ASP.Net using jQuery AJAX with C# and VB.Net.
Database
For this article I am making use of the Microsoft’s Northwind database. You can download it using the link below.
HTML Markup
The HTML Markup contains an HTML DIV and an ASP.Net GridView control inside it. I have placed GridView inside the HTML DIV so that we can implement scrolling.
<div id="dvGrid" style="height: 250px; overflow: auto; width: 517px">
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" CssClass="Grid" Width = "500">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText = "Customer Name" ItemStyle-CssClass="name" ItemStyle-Width="200" HeaderStyle-Width="200" />
<asp:BoundField DataField="City" HeaderText = "City" ItemStyle-CssClass="city" ItemStyle-Width="100" HeaderStyle-Width="100" />
<asp:BoundField DataField="Country" HeaderText = "Country" ItemStyle-CssClass="country" ItemStyle-Width="100" HeaderStyle-Width="100" />
<asp:BoundField DataField="PostalCode" HeaderText = "Postal Code" ItemStyle-CssClass="postal" ItemStyle-Width="100" HeaderStyle-Width="100" />
</Columns>
</asp:GridView>
</div>
Now here you need to consider few important things if you need a proper design layout.
1. You need specify the ItemStyle-Width and HeaderStyle-Width to each column of the GridView. Make sure both properties have same value for a specific column.
2. The total of all ItemStyle-Width of all columns must be set as Width of the GridView.
3. Finally the width of the HTML DIV must be Width of the GridView plus 17px. 17px is the width of the scrollbar. Example here 500 + 17 = 517px.
4. Set the height of the HTML DIV in such a way that by default it should display a scrollbar.
Note: I have set the property ItemStyle-CssClass for each column in GridView; this is done to identify the columns when the data will be populated using jQuery AJAX.
CSS Style for the GridView
The following CSS classes are used to provide look and feel for the ASP.Net GridView.
<style type="text/css">
.Grid td
{
background-color: #A1DCF2;
color: black;
font-size: 10pt;
font-family: Arial;
line-height: 200%;
cursor: pointer;
width: 100px;
}
.Grid th
{
background-color: #3AC0F2;
color: White;
font-family: Arial;
font-size: 10pt;
line-height: 200%;
width: 100px;
}
</style>
Stored procedure to fetch records on demand when the GridView is scrolled
You will need to execute the following stored procedure in the Northwind Database. The objective of creating the following stored procedure is to get records page wise based on Page Index using the customized paging within the database table using ROW_NUMBER() feature of SQL Server.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCustomersPageWise]
@PageIndex INT = 1
,@PageSize INT = 10
,@PageCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [CustomerID] ASC
)AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
,[City]
,[Country]
,[PostalCode]
,[Phone]
,[Fax]
INTO #Results
FROM [Customers]
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM #Results
SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
PRINT @PageCount
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
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
Binding the ASP.Net GridView
Below is the code to bind the GridView using the data fetched from the stored procedure we have created earlier.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
gvCustomers.DataSource = GetCustomersPageWise(1, 10);
gvCustomers.DataBind();
}
}
public static DataSet GetCustomersPageWise(int pageIndex, int pageSize)
{
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("[GetCustomersPageWise]"))
{
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, "Customers");
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;
}
}
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
gvCustomers.DataSource = GetCustomersPageWise(1, 10)
gvCustomers.DataBind()
End If
End Sub
Public Shared Function GetCustomersPageWise(pageIndex As Integer, pageSize As Integer) As DataSet
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("[GetCustomersPageWise]")
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 sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As New DataSet()
sda.Fill(ds, "Customers")
Dim dt As New DataTable("PageCount")
dt.Columns.Add("PageCount")
dt.Rows.Add()
dt.Rows(0)(0) = cmd.Parameters("@PageCount").Value
ds.Tables.Add(dt)
Return ds
End Using
End Using
End Using
End Using
End Function
The following screenshot displays the GridView after all the above steps are completed.
![Loading data in GridView while scrolling down in ASP.Net using jQuery AJAX]()
Freezing the ASP.Net GridView Header
Now we need to Freeze the GridView Header so that when we scroll the GridView the header row must remain fixed.
Hence for now we need do View Source of the Page and then copy the GridView Header Row HTML as shown below.
![Loading data in GridView while scrolling down in ASP.Net using jQuery AJAX]()
Then you need to copy the HTML Table of the GridView and the Header Row and paste it just above the HTML DIV that contains the GridView and complete the HTML table ending tag.
![Loading data in GridView while scrolling down in ASP.Net using jQuery AJAX]()
With the above steps your GridView should now look as following with two headers.
![Loading data in GridView while scrolling down in ASP.Net using jQuery AJAX]()
GridView Load Rows on demand when scrolled using jQuery AJAX
Now we will need to create a Web Method which will handle the jQuery AJAX calls when the GridView is scrolled.
C#
[WebMethod]
public static string GetCustomers(int pageIndex)
{
//Added to similate delay so that we see the loader working
//Must be removed when moving to production
System.Threading.Thread.Sleep(2000);
return GetCustomersPageWise(pageIndex, 5).GetXml();
}
VB.Net
<WebMethod()> _
Public Shared Function GetCustomers(pageIndex As Integer) As String
'Added to similate delay so that we see the loader working
'Must be removed when moving to production
System.Threading.Thread.Sleep(2000)
Return GetCustomersPageWise(pageIndex, 5).GetXml()
End Function
Now the following JavaScript code needs to be placed on the page, which will complete our task to create an ASP.Net GridView that loads data on demand when scrolled.
1. Firstly I have removed the default GridView header row as now it is not required since we need to use the fixed custom header that we have created.
2. Then I have attached a jQuery scroll event handler to the HTML DIV, so that we can trigger the process of fetching next set of records when the HTML DIV is scrolled.
3. When a jQuery AJAX request is made to the server Web Method, I am creating a dummy row with a loading GIF image and then appending it as the last row of the GridView, so that we can display some animation until the records are fetched and loaded.
4. When the jQuery AJAX call receives the response as XML string, the XML is parsed and the values are populated using the class names we have given to each GridView column.
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
var pageIndex = 1;
var pageCount;
$(function () {
//Remove the original GridView header
$("[id$=gvCustomers] 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()) {
GetRecords();
}
});
//Function to make AJAX call to the Web Method
function GetRecords() {
pageIndex++;
if (pageIndex == 2 || pageIndex <= pageCount) {
//Show Loader
if ($("[id$=gvCustomers] .loader").length == 0) {
var row = $("[id$=gvCustomers] 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="103.gif" /></td>');
$("[id$=gvCustomers]").append(row);
}
$.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 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 customers = xml.find("Customers");
$("[id$=gvCustomers] .loader").remove();
customers.each(function () {
var customer = $(this);
var row = $("[id$=gvCustomers] tr").eq(0).clone(true);
$(".name", row).html(customer.find("ContactName").text());
$(".city", row).html(customer.find("City").text());
$(".postal", row).html(customer.find("PostalCode").text());
$(".country", row).html(customer.find("Country").text());
$("[id$=gvCustomers]").append(row);
});
//Hide Loader
$("#loader").hide();
}
</script>
![Loading data in GridView while scrolling down in ASP.Net using jQuery AJAX]()
Demo
Downloads
