功能通过测试一个简单的小例子。方便以后自己拷着用;没有实现海量数据分页。
1.首先是界面没啥
<body>
<input type="button" value="上一页" disabled="disabled"/><input type="button" value="下一页"/>
<table>
<tr><th>书名</th><th>ISBN</th><th>价格</th><th>编号</th></tr>
</table>
</body>
</html>
2.前台命令页面的录入ShowBook.htm 拷入jquery包jquery-1.6.2.min.js我用的时这个版本;编写窗体加载和上一页,下一页的jquery代码
<title></title>
<script src="js/jquery-1.6.2.min.js" type="text/javascript"></script>
<script type="text/javascript" language="javascript">
//定义起始页和总页数
var pageIndex = 1;
var TotalPage;
//窗体加载事件调用jquery里德ajax发送请求页面。
$(function () {
$.ajax({
url: "AjaxFunction.aspx",
dataType: "json",
data: { pagesize: 3, pageindex: pageIndex },
success: function (data, text) {
for (var i = 0; i < data.records.length; i++) {
$("<tr><td>" + data.records[i].title + "</td><td>" + data.records[i].isbn + "</td><td>" + data.records[i].unitprice + "</td><td>" + data.records[i].id + "</td></tr>").appendTo($("table"));
}
TotalPage = window.parseInt(data.totalPage);
},
error: function (e) {
alert("error:" + e);
}
});
//下一页按钮点击
$("[value=下一页]").click(function () {
pageIndex++;
if (pageIndex >= TotalPage) {
pageIndex = TotalPage;
$(this).attr("disabled", true)
} else {
$(this).attr("disabled", false);
}
if (pageIndex <= 1) {
pageIndex = 1;
$("[value=上一页]").attr("disabled", true);
} else {
$("[value=上一页]").attr("disabled", false);
}
$.ajax({
url: "AjaxFunction.aspx",
dataType: "json",
data: { pagesize: 3, pageindex: pageIndex },
success: function (data, text) {
$("table tr:gt(0)").remove();
for (var i = 0; i < data.records.length; i++) {
$("<tr><td>" + data.records[i].title + "</td><td>" + data.records[i].isbn + "</td><td>" + data.records[i].unitprice + "</td><td>" + data.records[i].id + "</td></tr>").appendTo($("table"));
}
TotalPage = window.parseInt(data.totalPage);
},
error: function (e) {
alert("error:" + e);
}
});
});
//上一页按钮点击
$("[value=上一页]").click(function () {
pageIndex--;
if (pageIndex >= TotalPage) {
pageIndex = TotalPage;
$(this).attr("disabled", true)
} else {
$(this).attr("disabled", false);
}
if (pageIndex <= 1) {
pageIndex = 1;
$("[value=上一页]").attr("disable", true);
} else {
$("[value=上一页]").attr("disable", false);
}
$.ajax({
url: "AjaxFunction.aspx",
dataType: "json",
data: { pagesize: 3, pageindex: pageIndex },
success: function (data, text) {
$("table tr:gt(0)").remove();
for (var i = 0; i < data.records.length; i++) {
$("<tr><td>" + data.records[i].title + "</td><td>" + data.records[i].isbn + "</td><td>" + data.records[i].unitprice + "</td><td>" + data.records[i].id + "</td></tr>").appendTo($("table"));
}
TotalPage = window.parseInt(data.totalPage);
},
error: function (e) {
alert("error:" + e);
}
});
});
});
3.后台访问数据库页面 AjaxFunction.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
//获得每页显示多少行
string pagesize = Request.QueryString["pagesize"];
int PageSize;
if (pagesize == null)
{
PageSize = 3;
}
else {
PageSize = Convert.ToInt32(pagesize);
}
//获得当前是第几页
string pageindex = Request.QueryString["pageindex"];
int PageIndex;
if (pageindex == null)
{
PageIndex = 1;
}
else
{
PageIndex = Convert.ToInt32(pageindex);
}
SqlConnection connect = new SqlConnection("Data Source=.;Initial Catalog=MyBookShop;Integrated Security=True");
SqlCommand command = new SqlCommand("books_pager", connect);
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter pPageSize = new SqlParameter("@PageSize", PageSize);
SqlParameter pPageIndex = new SqlParameter("@PageIndex", PageIndex);
SqlParameter pTotalPages = new SqlParameter("@totalPages", System.Data.SqlDbType.Int);
pTotalPages.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(pPageSize);
command.Parameters.Add(pPageIndex);
command.Parameters.Add(pTotalPages);
connect.Open();
SqlDataReader reader = command.ExecuteReader();
string content = "";
while (reader.Read()) {
int bookId = (int)reader["bookId"];
string title = reader["Title"].ToString();
string price = reader["UnitPrice"].ToString();
string ISBN = reader["ISBN"].ToString();
content = content + "{\"id\":\"" + bookId + "\",\"title\":\"" + title + "\",\"isbn\":\"" + ISBN + "\",\"unitprice\":\"" + price + "\"},";
}
string subContent = content.Substring(0, content.Length - 1);
connect.Close();
int totalPage = (int)pTotalPages.Value;
Response.Write("{");
Response.Write("\"name\":\"Books\",");
Response.Write("\"pTotalPages\":\""+totalPage+"\",");
Response.Write("\"records\":");
Response.Write("[");
Response.Write(subContent);
Response.Write("]");
Response.Write("}");
Response.End();
}
好了功能实现,啊哈哈哈哈哈哈。对了还有存储过程没弄。贴上
create proc books_pager
@PageSize int,
@PageIndex int ,
@totalPages int output
as
declare @startIndex int
declare @endIndex int
declare @totalRows int
select @totalRows=COUNT(*) from Books
set @totalPages=@totalRows/@PageSize
if(@totalRows%@PageSize !=0)
begin
set @totalPages = @totalPages+1
end
set @startIndex=(@PageIndex-1)*@PageSize+1
set @endIndex = @startIndex + @PageSize-1
declare @booktemp table([id] int identity(1,1) not null,bookId int)
insert @booktemp
select Id from Books
select * from @booktemp as t,Books as b
where t.bookId=b.Id and t.id>=@startIndex and t.id<=@endIndex
go
declare @P int
exec books_pager 7,5,@P output
print @p
再笑意下。成功了。啊啊哈哈哈哈哈哈哈哈哈