写出代码之前,先说明一下原理,比较简单。有一张表(test)如下
结构是:id(自动编号) txt 假设40条记录)
现在要每页显示10条记录,则每页要显示的数据应该是:
第一页:1----10
第二页:11----20
第三页:21----30
第四页:31----40
如要显示第一页,最简单的方法就是 select top 10 * from test 就OK了。
第二页开始呢?怎么做呢?请接着看:
比如我要显示第三页:也就是21----30
原理:找出不要的数据也就是1----20,最大的id,这是里20
再找出大于这个id(20) 前10条记录就OK了。
原理知道后写代码就简单了,前台界面比较简单,不多说,代码如下
<
asp:LinkButton
id
="lbtnFirst"
Runat
="server"
>
首頁
</
asp:LinkButton
>
<
asp:LinkButton
id
="lbtnBack"
Runat
="server"
>
上頁
</
asp:LinkButton
>
<
asp:LinkButton
id
="lbtnNext"
Runat
="server"
>
下頁
</
asp:LinkButton
>
<
asp:LinkButton
id
="lbtnLast"
Runat
="server"
>
尾頁
</
asp:LinkButton
>
<
asp:Label
id
="Label1"
runat
="server"
>
当前页:
</
asp:Label
>
<
asp:Label
id
="lblCurrentPage"
runat
="server"
>
1
</
asp:Label
>
<
asp:Label
id
="Label2"
runat
="server"
>
总页:
</
asp:Label
>
<
asp:Label
id
="lblPageCount"
runat
="server"
>
200
</
asp:Label
>
<
asp:Label
id
="Label3"
runat
="server"
>
跳转:
</
asp:Label
>
<
asp:TextBox
id
="txtToPage"
runat
="server"
Width
="88px"
></
asp:TextBox
>
<
asp:Button
id
="btnToPage"
runat
="server"
Text
="go"
></
asp:Button
>
<
asp:DataGrid
id
="DataGrid1"
runat
="server"
AllowPaging
="True"
AllowCustomPaging
="True"
></
asp:DataGrid
>
后台代码:
private
void
Page_Load(
object
sender, System.EventArgs e)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
if (!Page.IsPostBack)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//开始显示第一页
ShowDate(Convert.ToInt64(lblCurrentPage.Text));
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/// <summary>
/// 显示数据
/// </summary>
/// <param name="page"></param>
private
void
ShowDate(
long
page)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//*-------------------设置参数------------------*/
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
string tblName = "test";
string fldName = "id";
string orderStr = "asc";
int PageSize = 10;
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//*-------------------设置结束------------------*/
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
byte[] t =Convert.FromBase64String(System.Configuration.ConfigurationSettings.AppSettings["conStr"]);
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
string conStr = System.Text.ASCIIEncoding.ASCII.GetString(t);
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
SqlConnection conn = new SqlConnection(conStr);
conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from " + tblName,conn);
lblPageCount.Text = Convert.ToString(((int)cmd.ExecuteScalar() / PageSize + 1));
DataGrid1.VirtualItemCount = Convert.ToInt32(lblPageCount.Text);//datadrid每次就显示一页,所有要手动加上总页
string sql = string.Empty;
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
sql = "select top " + Convert.ToString((page - 1) * PageSize) + " " + fldName + " from " + tblName + " order by " + fldName + " " + orderStr; //排除的记录部分
sql = "select max (" + fldName + ") from ( " + sql + " ) as t"; //得到排除记录里的最大ID号
sql = "select top " + PageSize.ToString() + " * from " + tblName + " where " + fldName + ">(" + sql + ") order by " + fldName + " " + orderStr;
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (page == 1)
{sql = "select top " + PageSize + " * from " + tblName;lblCurrentPage.Text = "1";}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlDataAdapter da = new SqlDataAdapter(sql,conn);
System.Data.DataSet ds = new DataSet();
da.Fill(ds);
DataGrid1.DataSource = ds.Tables[0].DefaultView;
DataGrid1.DataBind();
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
conn.Close();
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
}
catch ( Exception ex)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Response.Write(ex.Message.ToString());
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/// 首页
private
void
lbtnFirst_Click(
object
sender, System.EventArgs e)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
lblCurrentPage.Text = "1";
ShowDate(1);
}
![None.gif](/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/// 上页
private
void
lbtnBack_Click(
object
sender, System.EventArgs e)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
if (lblCurrentPage.Text != "1")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
lblCurrentPage.Text = Convert.ToString(Convert.ToInt64(lblCurrentPage.Text) - 1);
ShowDate(Convert.ToInt64(lblCurrentPage.Text));
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/// 下页
private
void
lbtnNext_Click(
object
sender, System.EventArgs e)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
if (lblCurrentPage.Text != lblPageCount.Text)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
lblCurrentPage.Text = Convert.ToString(Convert.ToInt64(lblCurrentPage.Text) + 1);
ShowDate(Convert.ToInt64(lblCurrentPage.Text));
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/// 尾页
private
void
lbtnLast_Click(
object
sender, System.EventArgs e)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
lblCurrentPage.Text = lblPageCount.Text;
ShowDate(Convert.ToInt64(lblPageCount.Text));
}
![None.gif](/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/// 跳转
private
void
btnToPage_Click(
object
sender, System.EventArgs e)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
if ( Convert.ToInt64(txtToPage.Text.Trim()) > 0 && Convert.ToInt64(txtToPage.Text.Trim()) < Convert.ToInt64(lblPageCount.Text))
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
lblCurrentPage.Text = txtToPage.Text;
ShowDate(Convert.ToInt64(txtToPage.Text));
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/// dataGrid单击的页数
private
void
DataGrid1_PageIndexChanged(
object
source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
DataGrid1.CurrentPageIndex = e.NewPageIndex;
lblCurrentPage.Text = Convert.ToString(e.NewPageIndex + 1);
ShowDate(e.NewPageIndex + 1);
}
亲自测试sql2000 460万条记录,显示速度很快