Introduction
Basically you will learn the following through this article:
- How to initially bind specified number of records from SQL Server
database table to Gridview.
- How to bind some data in
Gridview and on every click of "Load More Databutton" fetch more data and load in Gridview.
- How to show wait/progress loading image using Ajax "
UpdateProgress" and "ProgressTemplate" while fetching more data from SQL Server table and binding to gridview.
Using the Code
Implementation: Let's create a demo website to demonstrate the concept.
First of all, create a database in SQL Server and name it, e.g. "DB_Student" and in this database, create a table with the following Columns and Data type as shown below and name this table "Tbl_Student".
| Column Name |
Data Type |
StudentId |
Int(Primary Key. So set is identity=true) |
StudentName |
varchar(100) |
Class |
varchar(50) |
Age |
Int |
Gender |
varchar(50) |
Address |
varchar(500) |
Create a stored procedure to get student details to be filled in GridView Data Control.
CREATE PROCEDURE [dbo].[GetStudentDetails_SP]
@topVal INT
AS
BEGIN
SELECT top (@topVal) * FROM dbo.Tbl_Student
END
Create another stored procedure to count the number of rows present in table:
CREATE PROCEDURE [dbo].[GetStudentDetailsCount_SP]
AS
BEGIN
SELECT COUNT(*) FROM dbo.Tbl_Student
END
Now let's connect our ASP.NET application with SQL Server database.
So in the <configuration> tag of web.config file, create the connection string as:
<connectionStrings>
<add name="conStr" connectionString="Data Source=LALIT;_
Initial Catalog=DB_Student;Integrated Security=True"/>
</connectionStrings>
Note: Replace the Data Source and Initial Catalog as per your database settings.
ASP.NET C# Section
Below is the HTML source of the Default.aspx page.
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs"Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<fieldset style="width:370px;">
<legend>Load more data on demand in GridView</legend>
<table>
<tr>
<td>
<asp:GridView ID="grdStudentDetails" runat="server" AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:BoundField HeaderText="Student Name" DataField="StudentName" />
<asp:BoundField HeaderText="Class" DataField="Class" />
<asp:BoundField HeaderText="Age" DataField="Age" />
<asp:BoundField HeaderText="Gender" DataField="Gender" />
<asp:BoundField HeaderText="Address" DataField="Address" />
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</td>
</tr>
<tr>
<td align="center">
<asp:Button ID="btnLoadMore" runat="server" Text="Load More Data"
onclick="btnLoadMore_Click" />
</td>
</tr>
<tr>
<td align="center">
<asp:UpdateProgress ID="UpdateProgress1" runat="server"
ClientIDMode="Static"DisplayAfter="10">
<ProgressTemplate>
<img src="ajax-loader.gif" alt="wait image" />
</ProgressTemplate>
</asp:UpdateProgress>
</td></tr>
</table>
</fieldset>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
</body>
</html>
Note: You need to search on the internet and download any wait/progress GIF image of your choice and name it "ajax-loader.gif" and paste in root folder of your project.
ASP.NET C# Code Section
In code behind file (default.aspx.cs), write the code as:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default: System.Web.UI.Page
{
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
int num = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
num = 2;
ViewState["num"]=num;
BindGridView(num);
}
}
private void BindGridView(int numOfRows)
{
DataTable dt = new DataTable();
SqlCommand cmd = null;
SqlDataAdapter adp = null;
try
{
int rCount = rowCount();
if (numOfRows > rCount)
{
btnLoadMore.Visible = false;
}
cmd = new SqlCommand("GetStudentDetails_SP", con);
cmd.Parameters.AddWithValue("@topVal", numOfRows);
cmd.CommandType = CommandType.StoredProcedure;
adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
grdStudentDetails.DataSource = dt;
grdStudentDetails.DataBind();
}
else
{
grdStudentDetails.DataSource = null;
grdStudentDetails.DataBind();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message",
"alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);
}
finally
{
con.Close();
cmd.Dispose();
adp = null;
dt.Clear();
dt.Dispose();
}
}
protected int rowCount()
{
int NoOfRows = 0;
SqlCommand cmd = new SqlCommand("GetStudentDetailsCount_SP", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
NoOfRows = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message",
"alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);
}
finally
{
con.Close();
cmd.Dispose();
}
return NoOfRows;
}
protected void btnLoadMore_Click(object sender, EventArgs e)
{
int numVal = Convert.ToInt32(ViewState["num"])+ 2;
BindGridView(numVal);
ViewState["num"]= numVal;
}
}
ASP.NET VB Section
Design the page (default.aspx) as in above ASP.NET C# section, but replace the lines:
<asp:Button ID="btnLoadMore" runat="server" Text="Load More Data"
onclick="btnLoadMore_Click" />
with the following line:
<asp:Button ID="btnLoadMore"
runat="server" Text="Load More Data" />
In the code behind file (e.g. default.aspx.vb), write the code as:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class _Default
Inherits System.Web.UI.Page
Dim con As NewSqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
Dim num As Integer = 0
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
'Set the num variable equal to the value that you want to load data in gridView
'e.g if initially you want to load 2 rows in GridView then set num=2.
num = 2
'Store this num variable value in ViewState so that we can get this value on Load More Data button click
ViewState("num")=2
BindGridView(num)
End If
End Sub
Private Sub BindGridView(numOfRows As Integer)
Dim dt As New DataTable()
Dim cmd As SqlCommand = Nothing
Dim adp As SqlDataAdapter = Nothing
Try
'get number rows in table by calling the rowCount function i created.
Dim rCount As Integer = rowCount()
'hide the "Load More Data button"
'if the number of requested rows becomes greater than the rows in table
If numOfRows > rCount Then
btnLoadMore.Visible = False
End If
cmd = New SqlCommand("GetStudentDetails_SP", con)
'Passs numOfRows variable value to stored procedure to get desired number of rows
cmd.Parameters.AddWithValue("@topVal", numOfRows)
cmd.CommandType = CommandType.StoredProcedure
adp = New SqlDataAdapter(cmd)
adp.Fill(dt)
If dt.Rows.Count > 0 Then
grdStudentDetails.DataSource = dt
grdStudentDetails.DataBind()
Else
grdStudentDetails.DataSource = Nothing
grdStudentDetails.DataBind()
End If
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", _
"alert('Oops!! Error occured: " + ex.Message.ToString() + "');", True)
Finally
con.Close()
cmd.Dispose()
adp = Nothing
dt.Clear()
dt.Dispose()
End Try
End Sub
Protected Function rowCount() As Integer
Dim NoOfRows As Integer = 0
Dim cmd As New SqlCommand("GetStudentDetailsCount_SP", con)
cmd.CommandType = CommandType.StoredProcedure
Try
con.Open()
NoOfRows = Convert.ToInt32(cmd.ExecuteScalar())
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", _
"alert('Oops!! Error occured: " + ex.Message.ToString() + "');", True)
Finally
con.Close()
cmd.Dispose()
End Try
Return NoOfRows
End Function
Protected Sub btnLoadMore_Click(sender As Object, e As System.EventArgs) HandlesbtnLoadMore.Click
'On every click of this button it will add 2 to the ViewState("num")
'whose value was set to 2 initially on page load. So numval is 4 now.
Dim numVal As Integer = Convert.ToInt32(ViewState("num")) + 2
'Now pass numVal whose value is 4 to the BindGridView function to get 4 rows.
BindGridView(numVal)
'Set ViewState("num") equal to the numVal i.e. 4 so that
'when we again click this button it will be 4 + 2= 6 and so on.
ViewState("num") = numVal
End Sub
End Class
