首页 > 代码库 > jQuery+存储过程实现无刷新分页（九）
jQuery+存储过程实现无刷新分页（九）
涉及到得知识点：
1.分页存储过程
2.分页原理
3.jQuery DOM操作
4.jQuery Ajax存取数据
效果图：
思路：数据库---DAL----BLL----UI
具体代码：
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoCREATE PROCEDURE [dbo].[GetDataByPager]( @startIndex INT,--代表起始条数(从0开始) @tableName VARCHAR(50),--代表分页的表名称 @pageSize INT=5,--代表每页的条数 @condition VARCHAR(1000)=‘1=1‘,--代表条件 @key VARCHAR(20)=‘id‘, --代表主键 @orderType VARCHAR(20)=‘desc‘ --代表排序方式)ASBEGIN DECLARE @SQL VARCHAR(1000) SET @SQL=‘select TOP ‘ +CONVERT(VARCHAR(20),@pagesize)+‘ * FROM ‘+@tableName +‘ WHERE ‘+ @condition+‘ and ‘+@key+‘ NOT IN(SELECT TOP ‘ +CONVERT(VARCHAR(20),@startIndex)+‘ ‘+@key+‘ FROM ‘+@tableName+‘ where ‘+@condition+‘ order by ‘+@key+‘ ‘+@orderType+‘) order by ‘+@key+‘ ‘+@orderType EXEC(@SQL)END
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo--得到要分页的表的总记录数CREATE PROCEDURE [dbo].[GetDataByPager_Count]( @tableName VARCHAR(200), @condition VARCHAR(1000)=‘1=1‘)ASBEGIN DECLARE @strsql VARCHAR(2000) SET @strsql = ‘select count(*) from ‘+@tableName+‘ where ‘+@condition EXEC(@strsql)END
DAL层：
public static partial class AuthorService { /// <summary> /// 分页取数据 /// </summary> /// <param name="start">起始记录数（从1开始)</param> /// <param name="limit">每页的条数</param> /// <param name="condition">查询条件</param> /// <returns></returns> public static IList<Author> GetAuthorByPager(int start, int limit, string condition) { //string strsql = string.Format("Exec GetDataByPager2 {0},‘{1}‘,{2},‘{3}‘", start, limit, condition); string strsql = string.Format("Exec GetDataByPager {0},‘{1}‘,{2},‘{3}‘", start - 1, "Author", limit, condition); return GetAuthorsBySql(strsql); } /// <summary> /// 条件查询结果集数目 /// </summary> /// <param name="condition">条件</param> /// <returns></returns> public static int GetAuthorByPager_Count(string condition) { //string strsql = string.Format("GetDataByPager_Count ‘{0}‘", condition); string strsql = string.Format("GetDataByPager_Count ‘{0}‘,‘{1}‘ ", "Author", condition); return DBHelper.GetScalar(strsql); } }BLL层代码：
public static partial class AuthorManager { /// <summary> /// 分页取数据 /// </summary> /// <param name="start">起始记录数（从1开始)</param> /// <param name="limit">每页的条数</param> /// <param name="condition">查询条件</param> /// <returns></returns> public static IList<Author> GetAuthorByPager(int start, int limit, string condition) { return DAL.AuthorService.GetAuthorByPager(start, limit, condition); } /// <summary> /// 条件查询结果集数目 /// </summary> /// <param name="condition">条件</param> /// <returns></returns> public static int GetAuthorByPager_Count(string condition) { return DAL.AuthorService.GetAuthorByPager_Count(condition); } }引用了两个常用的js 代码：
String.format = function() { if (arguments.length == 0) return null; var str = arguments[0]; for (var i = 1; i < arguments.length; i++) { var re = new RegExp(‘\\{‘ + (i - 1) + ‘\\}‘, ‘gm‘); str = str.replace(re, arguments[i]); } return str;} function StringBuffer(){ this._strings_=new Array();}StringBuffer.prototype.append = function(str){ this._strings_.push(str);}StringBuffer.prototype.toString=function(){ return this._strings_.join("");}UI层得到数据：
public partial class HandleAuthorList : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (Request["startIndex"] != null && Request["limit"] != null) { int startIndex = int.Parse(Request["startIndex"].ToString()); int limit = int.Parse(Request["limit"].ToString()); IList<Models.Author> authors = BLL.AuthorManager.GetAuthorByPager(startIndex, limit, "1=1"); string json = JsonHelper.Serialize(authors); Response.Write(json); } else if (Request["type"] != null) { int rowCount= BLL.AuthorManager.GetAuthorByPager_Count("1=1"); Response.Write(rowCount); } }}Ajax异步刷新：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AuthorMgr.aspx.cs" Inherits="AuthorMgr" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head id="Head1" runat="server"> <title></title> <script src="http://www.mamicode.com/scripts/jquery-1.2.6.js" type="text/javascript"></script> <script src="http://www.mamicode.com/scripts/jquery-1.2.6-vsdoc-cn.js" type="text/javascript"></script> <script src="http://www.mamicode.com/scripts/string.format.js" type="text/javascript"></script> <script src="http://www.mamicode.com/scripts/StringBuffer.js" type="text/javascript"></script> <script type="text/javascript"> <script type="text/javascript"> var pageIndex = 1; //用于保存或设置当前页数 var startIndex = 1; //用于保存或设置记录条数 var limit = 3; //用于设置分页的条数 var rowcount = 0; //表示总记录数 var pageCount = 0; //代表总页数 var oFooter = "<tr align=‘left‘ style=‘color:#003399;background-color:#99CCCC;‘>"+ "<td colspan=‘4‘><table border=‘0‘>"+ "<tr><td><a>首页</a></td><td><a>下一页</a></td><td><a>上一页</a></td><td><a>最后一页</a></td></tr>" + "</table></td></tr>" function AjaxPager() { var $table = $("#GridView1"); $("a", $table).attr("href", "javascript:void(0)") .click(function() { $("tr>td", $table).parent().remove(); var oA = $(this).text(); if (oA == "首页") { pageIndex = 1; } else if (oA == "上一页") { pageIndex = pageIndex == 1 ? 1 : pageIndex - 1; } else if (oA == "下一页") { pageIndex = pageIndex >= pageCount ? pageIndex : pageIndex + 1; } else { pageIndex = pageCount; } startIndex = (pageIndex - 1) * limit + 1; $.getJSON("HandleAuthorList.aspx", { startIndex: startIndex, limit: limit }, function(josnArray) { var sb = new StringBuffer(); $.each(josnArray, function(i, item) { var trTemplate = "<tr style=‘color:#003399;background-color:White;‘>" + "<td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td>" + "</tr>"; var oTr = String.format(trTemplate, item.id, item.au_Id, item.au_Lname, item.phone); sb.append(oTr); }) $table.append(sb.toString()); if ($("td>a", $table).length == 0) $table.append(oFooter); var $tableFooter = $("td>table", $table); if (pageIndex == 1) $("tr>td:gt(1)", $tableFooter).hide(); else if (pageIndex == pageCount) $("tr>td:lt(2)", $tableFooter).hide(); AjaxPager(); }) }); } $(document).ready(function() { $.get("HandleAuthorList.aspx", { type: "getcount" }, function(data) { rowcount = parseInt(data); if (rowcount % limit == 0) pageCount = rowcount / limit; else pageCount = parseInt(rowcount / limit) + 1; AjaxPager(); }); }); </script></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="Id" DataSourceID="SqlDataSource1" EmptyDataText="没有可显示的数据记录。" PageSize="3" Width="494px" EnableViewState="False"> <PagerSettings FirstPageText="首页" LastPageText="最后一页" Mode="NextPreviousFirstLast" NextPageText="下一页" PreviousPageText="上一页" /> <RowStyle BackColor="White" ForeColor="#003399" /> <Columns> <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" /> <asp:BoundField DataField="Au_Id" HeaderText="Au_Id" SortExpression="Au_Id" /> <asp:BoundField DataField="Au_Lname" HeaderText="Au_Lname" SortExpression="Au_Lname" /> <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /> </Columns> <FooterStyle BackColor="#99CCCC" ForeColor="#003399" /> <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" /> <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" /> <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:JQuerySimpleDBConnectionString1 %>" DeleteCommand="DELETE FROM [Author] WHERE [Id] = @Id" InsertCommand="INSERT INTO [Author] ([Au_Id], [Au_Lname], [Au_Fname], [Phone], [Addr], [City], [State], [Zip]) VALUES (@Au_Id, @Au_Lname, @Au_Fname, @Phone, @Addr, @City, @State, @Zip)" ProviderName="<%$ ConnectionStrings:JQuerySimpleDBConnectionString1.ProviderName %>" SelectCommand="SELECT [Id], [Au_Id], [Au_Lname], [Au_Fname], [Phone], [Addr], [City], [State], [Zip] FROM [Author] order by Id desc" UpdateCommand="UPDATE [Author] SET [Au_Id] = @Au_Id, [Au_Lname] = @Au_Lname, [Au_Fname] = @Au_Fname, [Phone] = @Phone, [Addr] = @Addr, [City] = @City, [State] = @State, [Zip] = @Zip WHERE [Id] = @Id"> <DeleteParameters> <asp:Parameter Name="Id" Type="Int32" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="Au_Id" Type="String" /> <asp:Parameter Name="Au_Lname" Type="String" /> <asp:Parameter Name="Au_Fname" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> <asp:Parameter Name="Addr" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="State" Type="String" /> <asp:Parameter Name="Zip" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="Au_Id" Type="String" /> <asp:Parameter Name="Au_Lname" Type="String" /> <asp:Parameter Name="Au_Fname" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> <asp:Parameter Name="Addr" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="State" Type="String" /> <asp:Parameter Name="Zip" Type="String" /> <asp:Parameter Name="Id" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> </div> </form></body></html>
jQuery+存储过程实现无刷新分页（九）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。