ASP.NET数据分页技术(4)
这一种分页技术是企业用的最多的，也是最经典的，效率最高的。首先要建一个存储过程来返回客户获取信息的总记录数和
请求的当前页数，然后返回数据。
create PROCEDURE GetStudentInfo_ListByPage
(
@nCurrentPage int,
@nPageSize int,
)
AS
CREATE TABLE #studentTempTable
(
rowid int IDENTITY PRIMARY KEY,
stuXH char(10) NOT NULL,
stuXM varchar(10) NOT NULL,
stuXB char(2) not null,
stuBirthday DateTime not null,
stuAge int not null
)
-- copy the search results into the temporary table
INSERT INTO #ProductCategoryTempTable (stuXH,stuXM,stuXB,stuBirthday,stuAge)
SELECT stuXH,stuXM,stuXB,stuBirthday,stuAge
FROM Student
ORDER BY stuXH
-- calculate the current page
DECLARE @nFirstPageRecord int
DECLARE @nLastPageRecord int
SELECT @nFirstPageRecord = (@nCurrentPage - 1) * @nPageSize
SELECT @nLastPageRecord = ((@nCurrentPage * @nPageSize) + 1)
-- select the correct page of data with the given page size
SELECT * FROM #ProductCategoryTempTable
WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)
)
HTML页面如下：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PageSort1.aspx.cs" Inherits="PageSort1" %>
<!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">
<asp:DataGrid ID="DataGrid1" runat="server"
BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px"
CellPadding="4"
Width="531px" onpageindexchanged="DataGrid1_PageIndexChanged">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<SelectedItemStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left"
Mode="NumericPages" />
<ItemStyle BackColor="White" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:DataGrid>
<div>
<asp:Button ID="top" runat="server" onclick="top_Click" Text="Top" />
<asp:Button ID="next" runat="server" onclick="next_Click" Text="Next" />
<asp:Button ID="pre" runat="server" onclick="pre_Click" Text="Pre" />
<asp:Button ID="bottom" runat="server" onclick="bottom_Click" Text="Bottom" />
</div>
</form>
</body>
</html>
后台代码如下：
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;
public partial class PageSort1 : System.Web.UI.Page
{
private const int PageCount = 3;
private static int TotalPageCount = 0;
private int RecordCount = 0;
private int CurrentPageIndex=0;
protected void Page_Load(object sender, EventArgs e)
{
if (ViewState["CurrentPageIndex"] != null)
{
this.CurrentPageIndex = Int32.Parse(ViewState["CurrentPageIndex"].ToString());
}
if (!this.IsPostBack)
{
this.GetTotalPageCount();
TotalPageCount = this.RecordCount / PageCount;
if (TotalPageCount * PageCount < this.RecordCount)
{
TotalPageCount++;
}
this.BindGrid();
}
}
private void GetTotalPageCount()
{
string sql = "select * from xs order by XH ASC";
string sqlcon = WebConfigurationManager.ConnectionStrings["xscjConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(sqlcon);
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
this.RecordCount++;
}
dr.Close();
}
protected void DataGrid1_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
this.DataGrid1.CurrentPageIndex = e.NewPageIndex;
this.BindGrid();
}
private void BindGrid()
{
DataSet ds = GetDataSet(this.CurrentPageIndex, PageCount);
this.DataGrid1.DataSource = ds;
this.DataGrid1.DataKeyField = "XH";
this.DataGrid1.DataBind();
this.FirstLastPage();
}
private DataSet GetDataSet(int currentPageIndex, int PageSize)
{
string ConnString = WebConfigurationManager.ConnectionStrings["xscjConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(ConnString);
SqlDataAdapter da = new SqlDataAdapter("GetStudentInfo_ListByPage",con);
da.SelectCommand.Parameters.Add("@nCurrentPage",SqlDbType.Int,4).Value=currentPageIndex;
da.SelectCommand.Parameters["@nCurrentPage"].Direction = ParameterDirection.Input;
da.SelectCommand.Parameters.Add("@nPageSize",SqlDbType.Int,4).Value=PageSize;
da.SelectCommand.Parameters["@nPageSize"].Direction = ParameterDirection.Input;
con.Open();
DataSet DS = new DataSet();
da.Fill(DS,"xs");
conection.Close();
return DS;
}
private void FirstLastPage()
{
if (TotalPageCount > 0)
{
if (this.CurrentPageIndex == 0)
{
this.top.Enabled = false;
this.pre.Enabled = false;
this.next.Enabled = true;
this.bottom.Enabled = true;
}
if (this.CurrentPageIndex == TotalPageCount - 1)
{
this.top.Enabled = true;
this.pre.Enabled = true;
this.next.Enabled = false;
this.bottom.Enabled = false;
}
if (this.CurrentPageIndex != 0 && this.CurrentPageIndex != TotalPageCount - 1)
{
this.top.Enabled = true;
this.pre.Enabled = true;
this.next.Enabled = true;
this.bottom.Enabled = true;
}
}
}
protected void top_Click(object sender, EventArgs e)
{
this.CurrentPageIndex = 0;
ViewState["CurrentPageIndex"] = this.CurrentPageIndex.ToString();
Response.Write(CurrentPageIndex.ToString());
this.BindGrid();
}
protected void next_Click(object sender, EventArgs e)
{
this.CurrentPageIndex = (int)Math.Min(TotalPageCount - 1, this.CurrentPageIndex + 1);
ViewState["CurrentPageIndex"] = this.CurrentPageIndex.ToString();
Response.Write(CurrentPageIndex.ToString());
this.BindGrid();
}
protected void pre_Click(object sender, EventArgs e)
{
this.CurrentPageIndex = (int)Math.Max(0, this.CurrentPageIndex - 1);
ViewState["CurrentPageIndex"] = this.CurrentPageIndex.ToString();
Response.Write(CurrentPageIndex.ToString());
this.BindGrid();
}
protected void bottom_Click(object sender, EventArgs e)
{
this.CurrentPageIndex = TotalPageCount - 1;
ViewState["CurrentPageIndex"] = this.CurrentPageIndex.ToString();
Response.Write(CurrentPageIndex.ToString());
this.BindGrid();
}
}
