基本思路:在页面上放个gridview数据控件,然后在页面中放几个LinkButton控件用来显示首页、上一页、下一页、末页四个按钮,就是当点击任意一个按钮的时候调用一次存储过程(其实看到代码你就会明白,就是通过浏览器传入一个分页值,然后通过分页值限制存储过程读出的数据。),从数据库中读出有限的记录数,这样就是我想看多少条就读出多少条。呵呵,给新手们提供一个大数据量分页的思路。
下面看代码:
cs代码:
string pagesize = "1";
string condition = " where id > 0 ";
string TheTbale = " Tables";
string SelectField = " * ";
string orderby = " order by id asc ";
string TableID = " id ";
string sql = "select count(1) from Tables";//读总记录数
DataTable dt = zh.DataReturn(sql).Tables[0];//一个有返回值执行sql语句的方法。
int total = int.Parse(dt.Rows[0][0].ToString());
string lastpage = string.Empty;
if (total % int.Parse(pagesize) == 0)//计算最后一页为第几页
{
lastpage = Convert.ToString(total / int.Parse(pagesize));
}
else
{
lastpage = Convert.ToString((int)(total / int.Parse(pagesize)) + 1);
}
string page = Request.QueryString["page"];
int ipage;//如果传入页面为0则置为1
int.TryParse(page, out ipage);
if (ipage == 0) { ipage = 1; page = "1"; }
else { }
string nextpage = Convert.ToString(ipage + 1);//计算下一页
if (int.Parse(nextpage) > int.Parse(lastpage))//如果末页大于总页数则把末页重置为总页数
{
nextpage = lastpage;
}
string purpage = Convert.ToString(ipage - 1);//如果上一页为0则重置上一页为1
if (purpage == "0") purpage = "1";
string url = Request.Url.ToString();//获取url
string html = string.Empty;
if (url.Contains("?page="))//截取多余字符
{
string[] arr = url.Split('?');
url = arr[0];
url = url.TrimEnd('?');
}
//定位按钮url
hlpage.NavigateUrl = url + "?page=1";
hlpur.NavigateUrl = url + "?page=" + purpage;
hlnext.NavigateUrl = url + "?page=" + nextpage;
hllast.NavigateUrl = url + "?page=" + lastpage;
lbmessage.Text = "总记录:" + total + "条 当前第" + page + "/" + lastpage + "页";
hfpage.Value = lastpage;
if (lastpage == "1")//如果就一页则禁用翻页按钮
{
hllast.Enabled = hlnext.Enabled = hlpage.Enabled = hlpur.Enabled = false;
}
if (page == lastpage)//如果当前页为末页则禁用下一页和末页按钮
{
hlnext.Enabled = hllast.Enabled = false;
}
if (page == "1")//如果当前页为首页则禁用首页和上一页按钮
{
hlpage.Enabled = hlpur.Enabled = false;
}
zh.RetrunDataSet(pagesize, page, condition, TheTbale, SelectField, orderby, TableID, gv);
//以下为存储过程调用代码
SqlConnection Cnd;
Cnd = new SqlConnection("server=localhost;database=Tabless;uid=sa;pwd=Tabless");
SqlCommand Cmd = new SqlCommand("zh_Page", Cnd); //调用的存储过程名称
Cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parm = Cmd.Parameters.Add("@PageSize", SqlDbType.Int, 4);
parm.Value = "10";
parm = Cmd.Parameters.Add("@PageIndex", SqlDbType.Int, 4);
parm.Value = page;
parm = Cmd.Parameters.Add("@Condition", SqlDbType.NVarChar, 8000);
parm.Value = " where customerID>0 ";
parm = Cmd.Parameters.Add("@TheTable", SqlDbType.NVarChar, 8000);
parm.Value = " LogList ";
parm = Cmd.Parameters.Add("@SelectField", SqlDbType.NVarChar, 8000);
parm.Value = " * ";
parm = Cmd.Parameters.Add("@OrderBy", SqlDbType.NVarChar, 8000);
parm.Value = " order by _Date asc ";
parm = Cmd.Parameters.Add("@TableID", SqlDbType.NVarChar, 8000);
parm.Value = " _Date ";
Cnd.Open();
SqlDataReader dr1 = Cmd.ExecuteReader();
gv.DataSource = dr1;
gv.DataBind();
Cnd.Close();
html代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<html>
<head>
<link href="sty.css" rel="stylesheet" />
<style type="text/css">
.high { font-family: "宋体"; font-size: 9pt; line-height: 140%}
.mid { font-size: 9pt; line-height: 12pt}
.small { font-size: 9pt; line-height: normal}
.TP10_5 {
font-size: 14px;
line-height: 140%;
}
</style>
<style type="text/css">A:link {
COLOR: #cc6666
}
</style>
</head>
<body bottommargin="0" leftmargin="0" rightmargin="0" topmargin="0" style="text-align: center">
<form id="Form1" runat="server">
<br />
<br />
<asp:GridView ID="gv" runat="server">
</asp:GridView>
<asp:HyperLink ID="hlpage" runat="server" Font-Underline="False">首页</asp:HyperLink>
<asp:HyperLink ID="hlpur" runat="server" Font-Underline="False">上一页</asp:HyperLink>
<asp:HyperLink ID="hlnext" runat="server" Font-Underline="False">下一页</asp:HyperLink>
<asp:HyperLink ID="hllast" runat="server" Font-Underline="False">末页</asp:HyperLink>
跳至第<input id="tbpage" style="width: 50px; height: 21px" type="text" />页<input id="btJump" style="width: 21px; height: 21px" type="button" value="跳" οnclick="isdigit(document.getElementById('tbpage').value);" />
<asp:Label ID="lbmessage" runat="server"></asp:Label>
<asp:HiddenField ID="hfpage" runat="server" />
</form>
</body>
</html>
<script>
function isdigit(s)
{
var r,re;
re = /\d*/i; //\d表示数字,*表示匹配多个数字
r = s.match(re);
if(s==0)
{
alert("页码只能为数字!");
}
else
{
if(r==s)
{
if(parseInt(document.getElementById("tbpage").value)>parseInt(document.getElementById("hfpage").value))
{
location.href='default2.aspx?page='+document.getElementById("hfpage").value;
}
else
{
location.href='default2.aspx?page='+document.getElementById("tbpage").value;
}
}
else
{
alert("页码只能为数字!");
}}
}
</script>
存储过程代码:
CREATE PROCEDURE [dbo].[zh_Page]
(
@PageSize int, --每页记录
@PageIndex int, --当前页数,1开始
@Condition varchar(500), --查询条件,包括and,where,必须有一个条件如where 2>1
@TheTable varchar(100), --表名
@SelectField varchar(500), --要选择的字段
@OrderBy varchar (200), --OrderBy字句,包括order
@TableID varchar (50) --table主键
)
AS
begin
declare @Sql varchar(8000)
--返回记录
set @Sql='select top ' + cast(@PageSize as varchar(10)) + ' ' + @SelectField + ' from ' + @TheTable + ' ' + @Condition + ' and '
+ @TableID +' not in (select top ' + cast((@PageSize*(@PageIndex-1)) as varchar(10))+' ' +@TableID+' from ' + @TheTable + ' ' + @Condition
+ ' ' + @OrderBy +') ' + @OrderBy
exec(@sql)
--返回总数
--set @Sql='select count(' + @TableID + ') from ' + @TheTable +' ' + @Condition
--exec(@sql)
end
GO