ASP.NET中的分页剖析(三)

这里再给大家分析下如何实现基于SQL语句的分页功能。我们要手动实现自己的分页效果。如下图：

同时分页时能进行排序，如下图：

要实现这些功能需要如下编码实现，具体代码如下：

.aspx页面排序时的代码：

.aspx页面分页时的代码：

.aspx.cs对应的业务逻辑代码如下：

public partial class BookList_ManualPager : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //首次加载，赋初值
            try
            {
                ViewState["typeid"] = Convert.ToInt32(Request.QueryString["typeid"]);
            }
            catch
            {
                ViewState["typeid"] = -1;
            }

            RecordCount = BookManager.GetBooksCountByCategoryId(Convert.ToInt32(ViewState["typeid"]));
            btnChangeOrder.Enabled = false;
            Pager = 1;
            PageSize = 10;
            SetCurrentStatus();

rvTxtPageSize.MaximumValue = RecordCount.ToString();
rvTxtCPI.MaximumValue = PageCount.ToString();

            BindData();
        }
        else
        {
            RecordCount = BookManager.GetBooksCountByCategoryId(Convert.ToInt32(ViewState["typeid"]));
            rvTxtPageSize.MaximumValue = RecordCount.ToString();
            rvTxtCPI.MaximumValue = PageCount.ToString();
        }
    }

    /// <summary>
    /// 当前页索引号
    /// </summary>
    private int Pager
    {
        get { return (int)ViewState["Page"]; }
        set { ViewState["Page"] = value; }
    }

    /// <summary>
    /// 分页大小
    /// </summary>
    private int PageSize
    {
        get { return (int)ViewState["PageSize"]; }
        set { ViewState["PageSize"] = value; }
    }

    /// <summary>
    /// 记录数量
    /// </summary>
    private int RecordCount
    {
        get
        {
            return (int)ViewState["RecordCount"];
        }
        set
        {
            ViewState["RecordCount"] = value;
        }
    }

    /// <summary>
    /// 总页数
    /// </summary>
    private int PageCount
    {
        get
        {
            return (int)Math.Ceiling((double)RecordCount/PageSize);
        }
    }

    /// <summary>
    /// 获得封面的url
    /// </summary>
    /// <param name="isbn"></param>
    /// <returns></returns>
    public string GetUrl(string isbn)
    {
        return StringHandler.CoverUrl(isbn);
    }
    /// <summary>
    /// 截断图书显示内容
    /// </summary>
    /// <param name="content"></param>
    /// <param name="num"></param>
    /// <returns></returns>
    public string GetCut(string content, int num)
    {
        return StringHandler.CutString(content, num);
    }

    #region 排序
    protected void btnDate_Click(object sender, EventArgs e)
    {
        if (btnPrice.Enabled)
        {
            btnPrice.Enabled = false;
            btnPrice.BackColor = Color.Gray;

            if (!btnChangeOrder.Enabled)
            {
                btnChangeOrder.Enabled = true;
            }
        }

        string publishDateOrder = (string)ViewState["Order"];
        if (publishDateOrder == null)
        {
            ViewState["Order"] = "PublishDate";
            btnDate.Text = "出版日期↑";
        }
        else
        {
            if (publishDateOrder.Equals("PublishDate"))
            {
                ViewState["Order"] = "PublishDate Desc";
                btnDate.Text = "出版日期↓";
            }
            else
            {
                ViewState["Order"] = "PublishDate";
                btnDate.Text = "出版日期↑";
            }
        }

        Pager = 1;
        SetCurrentStatus();
        BindData();
    }

    protected void btnPrice_Click(object sender, EventArgs e)
    {
        if (btnDate.Enabled)
        {
            btnDate.Enabled = false;
            btnDate.BackColor = Color.Gray;

            if (!btnChangeOrder.Enabled)
            {
                btnChangeOrder.Enabled = true;
            }
        }
        string unitPriceOrder = (string)ViewState["Order"];
        if (unitPriceOrder == null)
        {
            ViewState["Order"] = "UnitPrice";
            btnPrice.Text = "价格↑";
        }
        else
        {
            if (unitPriceOrder.Equals("UnitPrice"))
            {
                ViewState["Order"] = "UnitPrice Desc";
                btnPrice.Text = "价格↓";
            }
            else
            {
                ViewState["Order"] = "UnitPrice";
                btnPrice.Text = "价格↑";
            }
        }

        Pager = 1;
        SetCurrentStatus();
        BindData();
    }
    #endregion

    private void BindData()
    {
        IList<Book> books;
        if (Pager == PageCount)
        {
            books = BookManager.GetCurrentPageBooksByCategoryId((Pager - 1) * PageSize + 1, RecordCount, Convert.ToInt32(ViewState["typeid"]), (string)ViewState["Order"]);
        }
        else
        {
            books = BookManager.GetCurrentPageBooksByCategoryId((Pager - 1) * PageSize + 1, Pager * PageSize, Convert.ToInt32(ViewState["typeid"]), (string)ViewState["Order"]);
        }

        dlBooks.DataSource = books;
        dlBooks.DataBind();
    }
    protected void btnChangeOrder_Click(object sender, EventArgs e)
    {
        if (btnDate.Enabled)
        {
            btnDate.Enabled = false;
            btnDate.BackColor = Color.Gray;
        }
        else
        {
            btnDate.Enabled = true;
            btnDate.BackColor = Color.FromArgb(192, 255, 192);
            if (btnDate.Text.Trim().Equals("出版日期↑"))
            {
                ViewState["Order"] = "PublishDate";
            }
            else
            {
                ViewState["Order"] = "PublishDate Desc";
            }

            Pager = 1;
            SetCurrentStatus();
            BindData();
        }
        if (btnPrice.Enabled)
        {
            btnPrice.Enabled = false;
            btnPrice.BackColor = Color.Gray;
        }
        else
        {
            btnPrice.Enabled = true;
            btnPrice.BackColor = Color.FromArgb(192, 255, 192);
            if (btnPrice.Text.Trim().Equals("价格↑"))
            {
                ViewState["Order"] = "UnitPrice";
            }
            else
            {
                ViewState["Order"] = "UnitPrice Desc";
            }

            Pager = 1;
            SetCurrentStatus();
            BindData();
        }
    }
    protected void btnFirstPage_Click(object sender, EventArgs e)
    {
        PageSize = Convert.ToInt32(txtPageSize.Text.Trim());
        Pager = 1;
        SetCurrentStatus();
        BindData();
    }
    protected void btnPreviousPage_Click(object sender, EventArgs e)
    {
        PageSize = Convert.ToInt32(txtPageSize.Text.Trim());
        Pager--;
        SetCurrentStatus();
        BindData();
    }
    protected void btnNextPage_Click(object sender, EventArgs e)
    {
        PageSize = Convert.ToInt32(txtPageSize.Text.Trim());
        Pager++;
        SetCurrentStatus();
        BindData();
    }
    protected void btnLastPage_Click(object sender, EventArgs e)
    {
        PageSize = Convert.ToInt32(txtPageSize.Text.Trim());
        Pager = PageCount;
        SetCurrentStatus();
        BindData();
    }
    protected void btnTurnPage_Click(object sender, EventArgs e)
    {
        Pager = Convert.ToInt32(txtCurrentPageIndex.Text.Trim());
        PageSize = Convert.ToInt32(txtPageSize.Text.Trim());
        SetCurrentStatus();

BindData();
}

    private void SetCurrentStatus()
    {
        if (Pager == 1 && Pager == PageCount)
        {
            btnFirstPage.Enabled = false;
            btnPreviousPage.Enabled = false;
            btnNextPage.Enabled = false;
            btnLastPage.Enabled = false;
        }
        else
        {
            if (Pager == 1)
            {
                btnFirstPage.Enabled = false;
                btnPreviousPage.Enabled = false;
                btnNextPage.Enabled = true;
                btnLastPage.Enabled = true;
            }
            else if (Pager == PageCount)
            {
                btnFirstPage.Enabled = true;
                btnPreviousPage.Enabled = true;
                btnNextPage.Enabled = false;
                btnLastPage.Enabled = false;
            }
            else
            {
                btnFirstPage.Enabled = true;
                btnPreviousPage.Enabled = true;
                btnNextPage.Enabled = true;
                btnLastPage.Enabled = true;
            }
        }

        txtPageSize.Text = PageSize.ToString();
        txtCurrentPageIndex.Text = Pager.ToString();
        lblMessage.Text = "当前页：" + Pager + "共" + PageCount + "页";
    }
}

DAL获取数据的SQL语句如下：

        public static IList<Book> GetCurrentPageBooksByCategoryId(int startRecordIndex, int endRecordIndex, int categoryId, string order)
        {
            IList<Book> Books = new List<Book>();
            string sql = string.Empty;
            bool bCategorySelect = false;
            bool bOrderSelect = false;

            if (categoryId > 0)
            {
                bCategorySelect = true;
            }

            if (order != null)
            {
                if (order.Trim().Length > 0)
                {
                    bOrderSelect = true;
                }
            }

            if (bCategorySelect && bOrderSelect)
            {
                sql = string.Format("select top {0} Id,ISBN, Title, Author, PublisherId, PublishDate, UnitPrice,SubString(ContentDescription,0,200) as ShortContent from Books where Id not in (select top {1} Id from Books where CategoryId = {2} order by {3}) and CategoryId = {4} order by {5}", endRecordIndex - startRecordIndex + 1, startRecordIndex - 1, categoryId, order, categoryId, order);
            }
            else if (bCategorySelect && !bOrderSelect)
            {
                sql = string.Format("select top {0} Id,ISBN, Title, Author, PublisherId, PublishDate, UnitPrice,SubString(ContentDescription,0,200) as ShortContent from Books where Id not in (select top {1} Id from Books where CategoryId = {2} order by Id) and CategoryId = {3} order by Id", endRecordIndex - startRecordIndex + 1, startRecordIndex - 1, categoryId, categoryId);
            }
            else if (!bCategorySelect && bOrderSelect)
            {
                sql = string.Format("select top {0} Id,ISBN, Title, Author, PublisherId, PublishDate, UnitPrice,SubString(ContentDescription,0,200) as ShortContent from Books where Id not in (select top {1} Id from Books order by {2}) order by {3}", endRecordIndex - startRecordIndex + 1, startRecordIndex - 1, order, order);
            }
            else
            {
                sql = string.Format("select top {0} Id,ISBN, Title, Author, PublisherId, PublishDate, UnitPrice,SubString(ContentDescription,0,200) as ShortContent from Books where Id not in (select top {1} Id from Books order by Id) order by Id", endRecordIndex - startRecordIndex + 1, startRecordIndex - 1);
            }

            DataTable table = DBHelper.GetDataSet(sql);
            foreach (DataRow row in table.Rows)
            {
                Book book = new Book();
                book.Id = (int)row["Id"];
                book.Title = (string)row["Title"];
                book.Author = (string)row["Author"];

                if (table.Columns.Contains("UnitPrice"))
                    book.UnitPrice = (decimal)row["UnitPrice"];
                if (table.Columns.Contains("ShortContent"))
                    book.ContentDescription = (string)row["ShortContent"];
                if (table.Columns.Contains("ISBN"))
                    book.ISBN = (string)row["ISBN"];
                if (table.Columns.Contains("Clicks"))
                    book.Clicks = (int)row["Clicks"];
                if (table.Columns.Contains("PublishDate"))
                    book.PublishDate = (DateTime)row["PublishDate"];
                if (table.Columns.Contains("CategoryId"))
                    book.Category = CategoryService.GetCategoryById((int)row["CategoryId"]);
                book.Publisher = PublisherService.GetPublisherById((int)row["PublisherId"]); //FK
                Books.Add(book);
            }
            return Books;
        }

这一次的内容就到这里吧！下一次---分页剖析(四)准备把页面部分的代码使用用户控件的方式进行改造，让其更具有通用性，来实现代码的复用效果。【待续...】