sql 分页

--sql分页

StringBuilder sql = new StringBuilder();
          sql.AppendFormat("select top {0} * from books",pageSize);
          sql.AppendFormat(" where Id not in");
          sql.AppendFormat(" (select top {0} Id from books",pageSize*( currentPage-1));
          sql.AppendFormat(" order by Id asc)");

protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            BindDataList();
            ViewState["CurrentPage"] = 1;


            PagedDataSourePager();


        }
        if (Convert.ToInt32(ViewState["CurrentPage"]) == 1)
        {
            BtnUpPage.Enabled = false;
        }
   
        GetSumPage();
    }


    private void GetSumPage()
    {
        int page = BookShopBLL.BookManager.GetAllBooks().Count / pageSize;
        if (BookShopBLL.BookManager.GetAllBooks().Count % pageSize != 0)
        {
            page = page + 1;
        }


        lblSum.Text = page.ToString();
        lblNow.Text = ViewState["CurrentPage"].ToString();
    }


    private void BindDataList()
    {
        this.DataList1.DataSource = BookShopBLL.BookManager.GetAllBooks();
        this.DataBind();
    }


    public  string GetImageUrl(string isbn)
    {
        return "Images/BookCovers/"+isbn+".jpg";
    }


    protected void btnBuy_Click(object sender, ImageClickEventArgs e)
    {
      
    }
    protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
    {
        string bookid = DataList1.DataKeys[e.Item.ItemIndex].ToString();
        if (e.CommandName == "btnBuy")
        {
            ShoppingManger manager = new ShoppingManger(Session["ShoppingCart"]);
            manager.AddItem(Convert.ToInt32(bookid));
            Session["ShoppingCart"] = manager.ShoppingItems;


          
        }
       
    }
    protected void btnCar_Click(object sender, ImageClickEventArgs e)
    {


    }
    protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
    {


    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string sortName = DropDownList1.SelectedValue.ToString();


        this.DataList1.DataSource= BookShopBLL.BookManager.GetBookBySortName(sortName);
        this.DataList1.DataBind();


    }
    protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)
    {


    }
    int pageSize = 5;


    protected void BtnNextPage_Click(object sender, EventArgs e)
    {
        ViewState["CurrentPage"] =Convert.ToInt32(ViewState["CurrentPage"]) +1;
        BtnUpPage.Enabled = true;


        lblNow.Text = ViewState["CurrentPage"].ToString();
        //this.DataList1.DataSource = BookShopBLL.BookManager.GetBookPager(pageSize, Convert.ToInt32(ViewState["CurrentPage"]));
        //this.DataList1.DataBind();
        PagedDataSourePager();
        lblNow.Text = ViewState["CurrentPage"].ToString();
      


    }
    protected void BtnUpPage_Click(object sender, EventArgs e)
    {
        if (Convert.ToInt32(ViewState["CurrentPage"]) == 1)
        {
            BtnUpPage.Enabled = false;
        }
        else
        { 
            ViewState["CurrentPage"] = Convert.ToInt32(ViewState["CurrentPage"]) - 1;
            lblNow.Text = ViewState["CurrentPage"].ToString();


            PagedDataSourePager();
            //this.DataList1.DataSource = BookShopBLL.BookManager.GetBookPager(pageSize, Convert.ToInt32(ViewState["CurrentPage"]));
            //this.DataList1.DataBind();
        }


    }


    private void PagedDataSourePager()
    {
        PagedDataSource pdPage = new PagedDataSource();


        pdPage.CurrentPageIndex =Convert.ToInt32( ViewState["CurrentPage"]);
        pdPage.AllowPaging = true;
        pdPage.PageSize = 5;
        pdPage.DataSource = BookShopBLL.BookManager.GetAllBooks();




        this.DataList1.DataSource = pdPage;
        this.DataBind();


    }