In this article I will describe the method to do this with gridview:
1. Bind data in from database table to
Gridview.
2. Initially we bind few records
in Gridview and on click of "Load More Data button" fetch more data
and bind it to Gridview.
3. Display progress image using
Ajax "UpdateProgress" and "ProgressTemplate" while fetching
more data from Database.
Create a
DataBase “Test” in Sql server, now create a table “Dept_table” with the following Columns and Data type as shown below :
Column
Name
|
Data
Type
|
Dept_Id_Pk
|
Int(Primary
Key. So set is identity=true)
|
Dept_Name
|
varchar(100)
|
Create a stored procedure to get student details to be
filled in Gridview.
CREATE PROCEDURE [dbo].[GetDept_SP]
@topVal INT
AS
BEGIN
--if
@topVal=2 then the below query will become SELECT top (2) * FROM dbo.
Dept_table and get 2 records
SELECT top (@topVal) * FROM dbo.Dept_table
END
Create
another stored procedure to count the number of rows
present in table
CREATE PROCEDURE [dbo].[GetDeptCount_SP]
AS
BEGIN
SELECT COUNT(*) FROM dbo.Dept_table
END
Now let's
connect application with Sql Server database:
<connectionStrings>
<add name="EmpCon" connectionString="Data Source=localhost;Initial
Catalog=test;IntegratedSecurity=True"/>
</connectionStrings>
Asp.Net
C# Section
Design
Part:
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 22px;
}
.style2
{
width: 20px;
}
</style>
</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:200px; text-align:center;">
<legend>Load more data in Gridview</legend>
<table>
<tr><td class="style1"></td>
<td align="center">
<asp:GridView ID="grdDept"
runat="server"
AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:BoundField HeaderText="Department Name" DataField="Dept_Name" />
</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>
<td class="style2"></td>
</tr>
<tr><td class="style1"> </td>
<td align="center">
</td><td class="style2"> </td>
</tr>
<tr>
<td class="style1">
</td>
<td align="center">
<asp:Button ID="btnLoadMore" runat="server"onclick="btnLoadMore_Click"
Text="Load More Data" />
</td>
<td class="style2">
</td>
</tr>
<tr><td class="style1"></td>
<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>
Place “ajax-loader.gif” in your
root folder.
Asp.Net
C# Code Section:
In code
behind, 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 Grid_button : System.Web.UI.Page
{
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);
int num = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
/*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 value in ViewState so that we
can get this value on Load more data button click
ViewState["num"] = num;
BindGrid(num);
}
}
private void BindGrid(int numOfRows)
{
DataTable dt = new DataTable();
SqlCommand cmd = null;
SqlDataAdapter adp = null;
try
{
//get number rows in table by calling the
rowCount function i created.
int rCount = rowCount();
// hide the "Load More Data button"
if the number of request rows becomes greater than the rows in table
if (numOfRows > rCount)
{
btnLoadMore.Visible = false;
}
cmd = new SqlCommand("GetDept_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)
{
grdDept.DataSource = dt;
grdDept.DataBind();
}
else
{
grdDept.DataSource = null;
grdDept.DataBind();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(),"Message", "alert('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("GetDeptCount_SP", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
NoOfRows = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(),"Message", "alert('Error occured: " + ex.Message.ToString() + "');", true);
}
finally
{
con.Close();
cmd.Dispose();
}
return NoOfRows;
}
protected void btnLoadMore_Click(object sender, EventArgs e)
{
//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.
int numVal = Convert.ToInt32(ViewState["num"]) + 2;
//Now pass numVal whose value is 4 to
the BindGrid function to get 4 rows.
BindGrid(numVal);
//Set ViewState["num"] is 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;
}
}
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
following line:
<asp:Button ID="btnLoadMore" runat="server" Text="Load More Data" />
In the
code behind file ,write the code as:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class Grid_button
Inherits System.Web.UI.Page
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Empcon").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 value in
ViewState so that we can get this value on Load more data button click
ViewState("num") = 2
BindGrid(num)
End If
End Sub
Private Sub BindGrid(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 request rows becomes greater than the rows in
table
If numOfRows > rCount Then
btnLoadMore.Visible = False
End If
cmd = New SqlCommand("GetDept_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
grdDept.DataSource
= dt
grdDept.DataBind()
Else
grdDept.DataSource
= Nothing
grdDept.DataBind()
End If
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('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("GetDeptCount _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(' 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) Handles btnLoadMore.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 BindGrid function to get 4 rows.
BindGrid(numVal)
'Set ViewState("num")
is 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 Sub
End Class
0 comments:
Post a Comment