<!–引入jquery-1.6.2–><!–ShowPageData1.aspx页面–><script src="../Jquery/jquery-1.6.2.min.js" type="text/javascript"></script><!–引入jquery-1.6.2–><script type="text/javascript" language="javascript">var pageIndex=1; //当前页码var totalPage; //总页数$(function () {$.ajax({url: "GetPageDataBySql.aspx", //请求页面dataType: "json",//返回的数据类型为jsondata: { pagesize: 3, pageindex: pageIndex }, //向url那个页面传两个参数success: function (getdata, stext) {for (var i = 0; i < getdata.records.length; i++) {$("<tr><td>" + getdata.records[i].isbn + "</td><td>" + getdata.records[i].id + "</td><td>" + getdata.records[i].unitprice + "</td><td>" + getdata.records[i].title + "</td></tr>").appendTo($("table"));totalPage = window.parseInt(getdata.totalpage);}},error: function (xmlhttprequest, st, e) {alert("Error:" + e); //打印错误消息}});//下一页$("[value=下一页]").click(function () {pageIndex++;if (pageIndex >= totalPage) {pageIndex = totalPage; //如果当前页码大于等于总页数，当前页码就等于总页数$(this).attr("disabled", true); //下一页不可点击(this代表当前点击的按钮)} else {$(this).attr("disabled", false); //否则下一页可点击(this代表当前点击的按钮)}if (pageIndex <= 1) {pageIndex = 1; //如果当前页码小于等于1，，则当前页码为第一页$("[value=上一页]").attr("disabled", true); //上一页不可点击} else {$("[value=上一页]").attr("disabled", false); //上一页可点击}//重新绑定数据$.ajax({url: "GetPageDataBySql.aspx", //请求页面dataType: "json",//返回的数据类型为jsondata: { pagesize: 3, pageindex: pageIndex }, //向url那个页面传两个参数success: function (getdata, stext) {//每次点击下一页的时候都要清空当前页的数据$("table tr:gt(0)").remove();for (var i = 0; i < getdata.records.length; i++) {$("<tr><td>" + getdata.records[i].isbn + "</td><td>" + getdata.records[i].id + "</td><td>" + getdata.records[i].unitprice + "</td><td>" + getdata.records[i].title + "</td></tr>").appendTo($("table"));totalPage = window.parseInt(getdata.totalpage);}},error: function (xmlhttprequest, st, e) {alert("Error:" + e); //打印错误消息}});});//上一页$("[value=上一页]").click(function () {pageIndex–;if (pageIndex >= totalPage) {pageIndex = totalPage; //如果当前页码大于等于总页数，当前页码就等于总页数$(this).attr("disabled", true); //下一页不可点击(this代表当前点击的按钮)} else {$(this).attr("disabled", false); //否则下一页可点击(this代表当前点击的按钮)}if (pageIndex <= 1) {pageIndex = 1; //如果当前页码小于等于1，则当前页码为第一页$("[value=上一页]").attr("disabled", true); //上一页不可点击} else {$("[value=上一页]").attr("disabled", false); //上一页可点击}//重新绑定数据$.ajax({url: "GetPageDataBySql.aspx", //请求页面dataType: "json", //返回的数据类型为jsondata: { pagesize: 3, pageindex: pageIndex }, //向url那个页面传两个参数success: function (getdata, stext) {//每次点击上一页的时候都要清空当前页的数据$("table tr:gt(0)").remove();for (var i = 0; i < getdata.records.length; i++) {$("<tr><td>" + getdata.records[i].isbn + "</td><td>" + getdata.records[i].id + "</td><td>" + getdata.records[i].unitprice + "</td><td>" + getdata.records[i].title + "</td></tr>").appendTo($("table"));totalPage = window.parseInt(getdata.totalpage);}},error: function (xmlhttprequest, st, e) {alert("Error:" + e); //打印错误消息}});});});</script><body><form id="form1" runat="server"><div><table><tr><th>封面</th><th>编号</th><th>价格</th><th>标题</th></tr></table><p><input type="button" value="上一页" disabled="disabled" /><input type="button" value="下一页" /></p></div></form></body>//GetPageDataBySql.aspx.csusing System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data.SqlClient;public partial class UseJqueryPage1_GetPageDataBySql : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e){string strPageSize=Request.QueryString["pagesize"]; //每页显示的行数int pageSize;if (strPageSize == null){pageSize = 3;//如果客户端传过来的那个pagesize为空的话，就默认每页显示3行}else {pageSize = Convert.ToInt32(strPageSize); //如果不为空的话就直接拿到就ok}string strPageIndex = Request.QueryString["pageindex"];//当前页码int pageIndex;if (strPageIndex == null){pageIndex = 1; //如果客户端传过来的那个pageindex为空的话，就默认当前是第一页}else {pageIndex = Convert.ToInt32(strPageIndex); //如果不为空的话就直接拿到pageindex就ok}//调用数据库中的存储过程进行分页SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=MyBookShop;Integrated Security=True"); //这里是连接数据库的连接串SqlCommand command = new SqlCommand("books_pager", conn); //执行存储过程 books_pager表示数据库存储过程的名字command.CommandType = System.Data.CommandType.StoredProcedure;//调用存储过程必须设置commandType为执行存储//存储过程需要3个参数，所以必须实例化3个参数对象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(pPageIndex);command.Parameters.Add(pPageSize);command.Parameters.Add(pTotalPages);conn.Open(); //打开数据库链接SqlDataReader reader = command.ExecuteReader();//执行查询string content = "";while (reader.Read()) {int bookId = (int)reader["bookId"];string title = reader["Title"].ToString();string isbn = reader["ISBN"].ToString();string price = reader["UnitPrice"].ToString();content += "{\&;id\&;:\&;"+bookId+"\&;,\&;isbn\&;:\&;"+isbn+"\&;,\&;title\&;:\&;"+title+"\&;,\&;unitprice\&;:\&;"+price+"\&;},";}string subContent = content.Substring(0,content.Length-1); //截取json最后面的那个逗号，因为content正常输出的时候最后面多了一个逗号conn.Close(); //关闭数据库连接int totalPage = (int)pTotalPages.Value; //必须要数据库关闭之后才可以获取输出的参数（即是总页数）Response.Write("{");Response.Write("\&;name\&;:\&;Books\&;,");Response.Write("\&;totalPage\&;:\&;"+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–总页数asdeclare @startIndex intdeclare @endIndex intdeclare @totalRows intselect @totalRows=COUNT(*) from Booksset @totalPages = @totalRows/@pageSizeif(@totalRows%@pageSize!=0)beginset @totalPages=@totalPages+1endset @startIndex=(@pageIndex-1)*@pageSize+1set @endIndex = @startIndex + @pageSize-1declare @booktemp table([id] int identity(1,1) not null,bookId int)insert @booktempselect Id from Booksselect * from @booktemp as t,Books as bwhere t.bookId=b.Id and t.id>=@startIndex and t.id<=@endIndexgo
要纠正别人之前，先反省自己有没有犯错
