使用控件自带的分页功能时，会先将所查询的数据全部加载出来，若数据量较大，则造成浏览器端等待时间过长。
然而在庞大的数据量，用户所需要的不过是那么几条，甚至只要其中的一条数据，所以，为了减少开销，每次只从数据库中读取10条数据。
以下是我做过的一个例子，该数据库表中有101万行数据，只要将数据库连接修改一下，即可复制，粘贴使用。
以下是aspx中的代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="test.aspx.cs" Inherits="test" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
<link rel="stylesheet" type="text/css" href="../Styles/bootstrap.min.css" />
<link rel="stylesheet" type="text/css" href="../Styles/admin-all.css" />
<link rel="stylesheet" type="text/css" href="../Styles/ui-lightness/jquery-ui-1.8.22.custom.css" />
<link rel="stylesheet" type="text/css" href="../Styles/web.css" />
<script type="text/javascript" src="../Scripts/jquery-1.7.2.js"></script>
<script type="text/javascript" src="../Scripts/jquery-ui-1.8.22.custom.min.js"></script>
<script type="text/javascript" src="../Scripts/My97DatePicker/WdatePicker.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table id="Search" class="table table-striped table-bordered table-condensed">
<thead>
<tr>
<td colspan="6">
患者管理查询
</td>
</tr>
</thead>
<tr>
<td rowspan="2">
精确查找
</td>
<td>
编号：
</td>
<td>
<asp:TextBox ID="TextBoxIDNumber" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
姓名：
</td>
<td>
<asp:TextBox ID="TextBoxName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td rowspan="2">
模糊查询：
</td>
<td>
编号：
</td>
<td>
<asp:TextBox ID="TextBoxNumberx" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
姓名：
</td>
<td>
<asp:TextBox ID="TextBoxNamex" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td>
排列方式
</td>
<td>
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem Value="1">按ID正序</asp:ListItem>
<asp:ListItem Value="2">按ID倒序</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="3">
<asp:Button ID="ButtonSearch" runat="server" Text="查询" OnClick="ButtonSearch_Click" />
<asp:Button ID="ButtonExcel" runat="server" Text="导出" OnClick="ButtonExcel_Click" />
</td>
</tr> </table>
<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate>
<table>
<tr>
<td style="width:50px;">
ID
</td>
<td style="width:150px;">
编号
</td>
<td style="width:100px;">
姓名
</td>
</tr> </HeaderTemplate>
<ItemTemplate>
<tr>
<td style=" color:Green;">
<%#Eval("ID")%>
</td>
<td style="color:Blue;">
<%#Eval("Number")%>
</td>
<td style="color:Red;">
<%#Eval("Name")%>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate> </asp:Repeater>
<table>
<tr >
<td>
<asp:LinkButton ID="LinkButtonFirst" runat="server" onclick="LinkButtonFirst_Click">首页</asp:LinkButton></td>
<td>
<asp:TextBox ID="TextBoxGo" runat="server" Width="29px"></asp:TextBox></td> <td>
<asp:LinkButton ID="LinkButtonGo" runat="server" onclick="LinkButtonGo_Click">前往</asp:LinkButton></td>
<td>
<asp:Label ID="Label1" runat="server" Text="第"></asp:Label></td>
<td>
<asp:Label ID="index" runat="server" Text="1"></asp:Label></td>
<td>
<asp:Label ID="Label3" runat="server" Text="页"></asp:Label></td>
<td>
<asp:Label ID="Label2" runat="server" Text="共"></asp:Label></td>
<td>
<asp:Label ID="labcount" runat="server" Text=""></asp:Label></td>
<td>
<asp:Label ID="Label5" runat="server" Text="页"></asp:Label></td>
<td>
<asp:LinkButton ID="LinkButtonBefore" runat="server" onclick="LinkButtonBefore_Click">上一页</asp:LinkButton></td>
<td>
<asp:LinkButton ID="LinkButtonAfter" runat="server" onclick="LinkButtonAfter_Click">下一页</asp:LinkButton></td>
<td>
<asp:LinkButton ID="LinkButtonLast" runat="server" onclick="LinkButtonLast_Click">尾页</asp:LinkButton></td>
</tr>
</table>
</div>
</form>
</body>
</html>
下面是后台cs代码：
public partial class test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.shuju();
} } /// <summary>
/// 获取数据，并设置页数
/// </summary>
/// <param name="pagenumber"></param> public void GetData(int pagenumber)
{
string str_sql1 = "select id,number,name from dbo.test ";
string str_sql2 = "select count(*) from dbo.test "; //获得数据总行数
if (ViewState["where"] != null)
{
str_sql2 += ViewState["where"].ToString();
str_sql1 += ViewState["where"].ToString();
}
if (ViewState["order"] != null)
{
str_sql1 += ViewState["order"].ToString();
}
else
{
str_sql1 += " order by id ";
}
str_sql1 += " offset " + pagenumber + " rows fetch next 10 rows only ";
string rowsNumber = MySql.GetSingleStr(str_sql2); //获得数据总行数
DataTable dt = MySql.GetDataTable(str_sql1);
if (Convert.ToInt32(rowsNumber) % != )
{
labcount.Text = (Convert.ToInt32(rowsNumber) / + ).ToString(); //返回总页数，不能被10整除则加一页
}
else
{
labcount.Text = (Convert.ToInt32(rowsNumber) / ).ToString(); //返回总页数
}
this.Repeater1.DataSource = dt;
this.Repeater1.DataBind();
} /// <summary>
/// 初始化数据
/// </summary>
public void shuju()
{
GetData();
index.Text = ""; //当前页为1 //把首页和上一页设置为不可操作
if (index.Text == "")
{
LinkButtonFirst.Enabled = false;
LinkButtonBefore.Enabled = false;
}
if (index.Text == labcount.Text)
{
LinkButtonAfter.Enabled = false;
LinkButtonLast.Enabled = false;
}
else
{
LinkButtonAfter.Enabled = true;
LinkButtonLast.Enabled = true;
}
} /// <summary>
/// 首页按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkButtonFirst_Click(object sender, EventArgs e)
{ GetData();
index.Text = ""; //当前页为1 //把首页和上一页设置为不可操作
if (index.Text == "")
{
LinkButtonFirst.Enabled = false;
LinkButtonBefore.Enabled = false;
}
if (index.Text == labcount.Text)
{
LinkButtonAfter.Enabled = false;
LinkButtonLast.Enabled = false;
}
else
{
LinkButtonAfter.Enabled = true;
LinkButtonLast.Enabled = true;
}
} /// <summary>
/// Go按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkButtonGo_Click(object sender, EventArgs e)
{
if (TextBoxGo.Text != "")
{
string pageindex = TextBoxGo.Text; //获取要前往的页数
int intpageindex = Convert.ToInt32(pageindex); //转为整型 int pagecount = Convert.ToInt32(labcount.Text);
if (intpageindex > pagecount&&intpageindex<=) //判断，如果输入的数字大于总页数就返回
{
return;
}
else
{
int pageNumber = Convert.ToInt32(TextBoxGo.Text)*-;
GetData(pageNumber);
if (TextBoxGo.Text == "") //判断如果输入1，首页和上一页不可操作，但是尾页和下一页可以操作
{
LinkButtonFirst.Enabled = false;
LinkButtonBefore.Enabled = false; LinkButtonAfter.Enabled = true;
LinkButtonLast.Enabled = true;
}
else if (TextBoxGo.Text == labcount.Text) //判断如果输入的数等于总页数，首页和第一页可以操作，但是尾页和下一页不可操作
{
LinkButtonAfter.Enabled = false;
LinkButtonLast.Enabled = false; LinkButtonFirst.Enabled = true;
LinkButtonBefore.Enabled = true;
}
else //如果是其他情况，首页和下一页，尾页和下一页都可操作
{
LinkButtonAfter.Enabled = true;
LinkButtonLast.Enabled = true; LinkButtonFirst.Enabled = true;
LinkButtonBefore.Enabled = true;
}
} index.Text = TextBoxGo.Text; //设置当前页数等于输入的页数
}
else
{
return;
}
} /// <summary>
/// 上一页按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkButtonBefore_Click(object sender, EventArgs e)
{ string pageindex = index.Text; //获取当前页数
int pageNumber = Convert.ToInt32(pageindex) * - ; //获取上一页之前的总行数
GetData(pageNumber); int intindex = Convert.ToInt32(index.Text);
index.Text = (intindex - ).ToString(); //当前页-1 LinkButtonAfter.Enabled = true;
LinkButtonLast.Enabled = true; if (index.Text == "")
{
LinkButtonFirst.Enabled = false;
LinkButtonBefore.Enabled = false;
} } /// <summary>
/// 下一页按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkButtonAfter_Click(object sender, EventArgs e)
{
string pageindex = index.Text; //获取当前页数
int pageNumber = Convert.ToInt32(pageindex)*; //获取下一页之前的总行数
GetData(pageNumber); int intindex = Convert.ToInt32(index.Text);
index.Text = (intindex + ).ToString(); //当前页+1 LinkButtonFirst.Enabled = true;
LinkButtonBefore.Enabled = true; if (index.Text == labcount.Text)
{
LinkButtonAfter.Enabled = false;
LinkButtonLast.Enabled = false;
}
} /// <summary>
/// 尾页按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkButtonLast_Click(object sender, EventArgs e)
{ int pageNumber = (Convert.ToInt32(labcount.Text) - ) * ; //获取最后一页之前的总行数
GetData(pageNumber); index.Text = labcount.Text; LinkButtonFirst.Enabled = true;
LinkButtonBefore.Enabled = true; LinkButtonAfter.Enabled = false;
LinkButtonLast.Enabled = false;
}
protected void ButtonSearch_Click(object sender, EventArgs e)
{
string strWhere = " where ";
string strOrder = " order by ID ";
if (TextBoxIDNumber.Text != "") //填写编号
{
strWhere += "Number='" + TextBoxIDNumber.Text + "' and ";
}
if (TextBoxName.Text != "") //填写姓名
{
strWhere += "Name='" + TextBoxName.Text + "' and ";
}
if (TextBoxNumberx.Text != "") //填写模糊编号
{
strWhere += "Number like '" + TextBoxNumberx.Text + "%' and ";
}
if (TextBoxNamex.Text != "") //编写模糊姓名
{
strWhere += "Name like '" + TextBoxNamex.Text + "%' and ";
}
strWhere += " 1=1 ";
if (DropDownList1.SelectedValue == "") //选择倒序
{
strOrder += " desc";
}
ViewState["where"] = strWhere;
ViewState["order"] = strOrder;
shuju(); } protected void ButtonExcel_Click(object sender, EventArgs e)
{
string sql = "select id,number,name from dbo.test ";
if(ViewState["where"]!=null)
{
sql += ViewState["where"].ToString();
}
if (ViewState["order"] != null)
{
sql += ViewState["order"].ToString();
}
DataTable dtexcel = MySql.GetDataTable(sql);
Random ran = new Random();
string temp = ran.Next(, ).ToString();
string excel = "Excel-" + DateTime.Now.ToString("yyyyMMdd") + temp;
CreateExcel(dtexcel, "application/ms-excel", excel);
} /// <summary>
/// 导出excel
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="FileType">文件类型</param>
/// <param name="FileName">文件名</param>
public void CreateExcel(DataTable dt, string FileType, string FileName)
{
Response.Clear();
Response.Charset = "UTF-8";
Response.Buffer = true;
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
Response.ContentType = FileType;
string colHeaders = string.Empty;
string ls_item = string.Empty;
ls_item += "ID\t编号\t姓名\n";
DataRow[] myRow = dt.Select();
int i = ;
int cl = dt.Columns.Count;
foreach (DataRow row in myRow)
{
for (i = ; i < cl; i++)
{
if (i == (cl - ))
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
Response.Output.Write(ls_item);
ls_item = string.Empty;
}
Response.Output.Flush();
Response.End();
}
}
附带excel导出功能。