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 ‘[email protected]           +‘ WHERE ‘+ @condition+‘ and ‘[email protected]+‘ NOT IN(SELECT TOP  ‘ +CONVERT(VARCHAR(20),@startIndex)+‘ ‘[email protected]+‘ FROM ‘[email protected]+‘ where ‘[email protected]+‘ order by ‘[email protected]+‘ ‘[email protected]+‘) order by ‘[email protected]+‘ ‘[email protected]  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 ‘[email protected]+‘ where ‘[email protected]  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="scripts/jquery-1.2.6.js" type="text/javascript"></script>    <script src="scripts/jquery-1.2.6-vsdoc-cn.js" type="text/javascript"></script>    <script src="scripts/string.format.js" type="text/javascript"></script>    <script src="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+預存程序實現無重新整理分頁（九）