在 ASP.NET GridView 中，通过按钮点击从 SQL Server 表中加载更多记录
4.62/5 (15投票s)
在本文中，我将演示如何按需获取更多数据，即每次单击按钮时从 SQL Server 数据库表中获取更多记录，并使用等待/进度/加载图像在 ASP.NET 中加载到 GridView 中，如图所示，使用 C# 和 VB 语言。
引言
通过本文，您将学习以下内容：
- 如何从 SQL Server
数据库表初始绑定指定数量的记录到GridView。 - 如何将一些数据绑定到
GridView中，并在每次单击“加载更多数据”按钮时获取更多数据并加载到GridView中。 - 如何使用 Ajax 的“
UpdateProgress”和“ProgressTemplate”显示等待/进度加载图像，同时从 SQL Server 表中获取更多数据并绑定到gridview。
Using the Code
实现：让我们创建一个演示网站来演示这个概念。
首先，在 SQL Server 中创建一个数据库，并命名它，例如“DB_Student”，并在该数据库中创建一个具有以下列和 数据类型的表，如下所示，并将此表命名为“Tbl_Student”。
| 列名 | 数据类型 |
StudentId |
Int（主键。因此将 is identity 设置为 true） |
StudentName |
varchar(100) |
类 |
varchar(50) |
年龄 |
Int |
性别 |
varchar(50) |
地址 |
varchar(500) |
创建一个存储过程，以获取要填充到 GridView 数据控件中的学生详细信息。
CREATE PROCEDURE [dbo].[GetStudentDetails_SP]
@topVal INT
AS
BEGIN
--if @topVal=2 then the below query will become SELECT top (2) * FROM dbo.Tbl_Student and get 2 records
SELECT top (@topVal) * FROM dbo.Tbl_Student
END
创建另一个存储过程，以计算表中存在的行数
CREATE PROCEDURE [dbo].[GetStudentDetailsCount_SP]
AS
BEGIN
SELECT COUNT(*) FROM dbo.Tbl_Student
END
现在让我们将 ASP.NET 应用程序连接到 SQL Server 数据库。
因此，在 web.config 文件的 <configuration> 标签中，创建连接字符串，如下所示：
<connectionStrings>
<add name="conStr" connectionString="Data Source=LALIT;_
Initial Catalog=DB_Student;Integrated Security=True"/>
</connectionStrings>
注意： 请根据您的数据库设置替换数据源和初始目录。
ASP.NET C# 部分
以下是 Default.aspx 页面的 HTML 源代码。
<%@ 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>
注意：您需要在互联网上搜索并下载您选择的任何等待/进度 GIF 图像，并将其命名为“ajax-loader.gif”，然后将其粘贴到项目的根文件夹中。
ASP.NET C# 代码部分
在代码隐藏文件 (default.aspx.cs) 中，编写如下代码：
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)
{
//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 button click
ViewState["num"]=num;
BindGridView(num);
}
}
private void BindGridView(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 requested rows becomes greater than the rows in table
if (numOfRows > rCount)
{
btnLoadMore.Visible = false;
}
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)
{
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)
{
//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 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;
}
}
ASP.NET VB 部分
设计页面 (default.aspx) 如上述 ASP.NET C# 部分所示，但替换以下行：
<asp:Button ID="btnLoadMore" runat="server" Text="Load More Data"
onclick="btnLoadMore_Click" />
为以下行：
<asp:Button ID="btnLoadMore"
runat="server" Text="Load More Data" />
在代码隐藏文件 (例如 default.aspx.vb) 中，编写如下代码：
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