[ASP.net WebForm] 製作可重複使用的分頁WebUserControl

2011-11-28

6904
0
ASP.net WebForm
2012-07-10

※2012.7.10 追記：實務上使用發現，用Session的寫法，若Session過期的話，例外處理很麻煩，所以改成不使用Session的方式

※↓這個有空會改成SQL Server 2012版

分頁的預存程序使用此篇： [MSSQL] 自己寫的SQL分頁預存程序(傳筆數Range版、傳頁數版)

本文須搭配SqlHelper類存取數據：http://www.cnblogs.com/sufei/archive/2010/01/14/1648026.html

ascx檔


    Inherits="Upload_wuc_UploadPhotoPage" %>
<div class="rsmenu">
    合計<asp:Literal ID="li_totalRows" runat="server" />
    <span>｜
        <asp:LinkButton Text="最前頁" ID="lnkFirstPage" runat="server" OnClick="lnkFirstPage_Click" />
        ｜
        <asp:LinkButton Text="上一頁" ID="lnkPrePage" runat="server" OnClick="lnkPrePage_Click" />｜
    </span>選擇頁數&nbsp;&nbsp; <b>第<asp:DropDownList runat="server" ID="dl_currentPage"
        CssClass="fd12">
    </asp:DropDownList>
        頁</b> <span>｜
            <asp:LinkButton Text="下一頁" ID="lnkNextPage" runat="server" OnClick="lnkNextPage_Click" />｜
            <asp:LinkButton Text="最後頁" runat="server" ID="lnkLastPage" OnClick="lnkLastPage_Click" />｜
        </span>每頁
    <asp:DropDownList runat="server" ID="dl_pageSize" CssClass="fd13">
        <asp:ListItem Value="10" Text="10" />
        <asp:ListItem Value="20" Text="20" />
        <asp:ListItem Value="30" Text="30" />
    </asp:DropDownList>
    筆
    <asp:Button ID="btnToPage" runat="server" CssClass="pgbtn" Text="跳頁" OnClick="btnToPage_Click" />
</div>


using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using SystemDAO;

//Create by Shadow at 2012.3.30 (用預存程序分頁)
//http://www.dotblogs.com.tw/shadow/archive/2011/05/22/25784.aspx
public partial class Upload_wuc_UploadPhotoPage : System.Web.UI.UserControl
{
   
    //取得連線字串
    string Conn_E = WebConfigurationManager.AppSettings["連線字串"];

    //資料繫結控制項(GridView、ListView)
    public DataBoundControl ControlList = null;



    
    public string SqlQuery { set; private get; }

    public string SqlOrder { set; private get; }

    //Shadow 說明 at 2011/10/18
    #region 分頁資料，總筆數、總頁數文字顯示處理
    public void ShowData(int currentPage, int pageSize)
    {


        #region 總頁數、總筆數文字顯示處理
        SqlParameter[] param = new SqlParameter[]{
        new SqlParameter(){ ParameterName="@sqlQuery",SqlDbType=SqlDbType.VarChar, Value=this.SqlQuery  },
        new SqlParameter(){ ParameterName="@sqlOrder",SqlDbType=SqlDbType.VarChar, Value=this.SqlOrder  },
        new SqlParameter(){ ParameterName="@currentPage",SqlDbType=SqlDbType.Int, Value=currentPage  },
        new SqlParameter(){ ParameterName="@pageSize",SqlDbType=SqlDbType.Int, Value=pageSize  }

        };

        
        DataSet ds = SqlHelper.ExecuteDataSet(this.Conn_E, CommandType.StoredProcedure, "uSP_pageSQL_passPageNum", param);

        this.ControlList.DataSource = ds.Tables[0];//結果資料集
        this.ControlList.DataBind();


        if (ds.Tables[1] != null)
        {
            int totalRows = Convert.ToInt32(ds.Tables[1].Rows[0]["ToTalRow"]); //總筆數
            li_totalRows.Text = totalRows.ToString();
             

            //總頁數
            int totalPages = Convert.ToInt32(ds.Tables[1].Rows[0]["totalPages"]);



            //防呆寫法
            if (currentPage > totalPages)
                currentPage = totalPages;
            if (currentPage < 1)
                currentPage = 1;



            dl_currentPage.Items.Clear();
            for (int i = 1; i <= totalPages; i++)
            {
                dl_currentPage.Items.Add(i.ToString());
            }
            //目前第幾頁的下拉選單選擇值
            dl_currentPage.SelectedValue = currentPage.ToString();
            //每頁顯示幾筆的下拉選單選擇值
            dl_pageSize.SelectedValue = pageSize.ToString();

            //第一頁和最後一頁時，第一頁、上一頁、下一頁、最末頁按鈕Enabled的處理防呆
            if (currentPage == 1 || totalRows == 0)//目前在第一頁
            {
                lnkFirstPage.Enabled = false;
                lnkPrePage.Enabled = false;
            }
            else
            {
                lnkFirstPage.Enabled = true;
                lnkPrePage.Enabled = true;
            }
            if (currentPage == totalPages || totalRows == 0)//目前在最後一頁
            {
                lnkLastPage.Enabled = false;
                lnkNextPage.Enabled = false;
            }
            else
            {
                lnkLastPage.Enabled = true;
                lnkNextPage.Enabled = true;
            }
        }



        #endregion


    }
    #endregion

    //第一頁Click
    protected void lnkFirstPage_Click(object sender, EventArgs e)
    {

        dl_currentPage.SelectedValue = "1";//到第一頁
        this.btnToPage_Click(null, null);
    }

    //上一頁Click
    protected void lnkPrePage_Click(object sender, EventArgs e)
    {

        int prePage = Convert.ToInt32(dl_currentPage.SelectedValue) - 1;//上一頁數
        if (prePage <= 0)
        {
            prePage = 1;
        }

        dl_currentPage.SelectedValue = prePage.ToString();
        this.btnToPage_Click(null, null);
    }
    //下一頁Click
    protected void lnkNextPage_Click(object sender, EventArgs e)
    {

        int nextPage = Convert.ToInt32(dl_currentPage.SelectedValue) + 1;//下一頁數
        if (nextPage > dl_currentPage.Items.Count)//下一頁數大於總頁數
        {
            nextPage = dl_currentPage.Items.Count;//下一頁數為總頁數 
        }
        dl_currentPage.SelectedValue = nextPage.ToString();

        this.btnToPage_Click(null, null);
    }

    //最後一頁
    protected void lnkLastPage_Click(object sender, EventArgs e)
    {

        dl_currentPage.SelectedValue = dl_currentPage.Items[dl_currentPage.Items.Count - 1].Value;
        this.btnToPage_Click(null, null);
    }

    #region 跳頁事件postback的處理
    public void btnToPage_Click(object sender, EventArgs e)
    {
        #region 防呆：當查詢無資料時，dl_currentPage.Items.Count == 0，為了避免再按一次搜尋或再次postback時，程式掛掉，加入以下判斷
        //當查無資料時，要防呆
        int currentPage = 1;
        if (dl_currentPage.Items.Count == 0)
        {
            currentPage = 1;//因為會發生查無資料(dl_currentPage.Items.Count == 0)時，使用者再按一次查詢，如果這邊return的話，使用者永遠無法再度查詢
        }
        else
        {
            currentPage = Convert.ToInt32(dl_currentPage.SelectedValue);
        }

        this.ShowData(currentPage, Convert.ToInt32(dl_pageSize.SelectedValue));
        #endregion
    }


    #endregion


}

使用方法：

把WebUserControl放到畫面上


    Inherits="Demo" %>
<%@ Register TagPrefix="include" TagName="pager" Src="wuc_UploadPhotoPage.ascx" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
     
     <asp:TextBox id="TextBox1" runat="server" /><asp:Button id="Button1" runat="server" Text="查詢" onclick="Button1_Click" />

    <asp:GridView id="GridView1" runat="server" />
         


     <br />
    <include:pager ID="pager" runat="server" />
     
    </form>
</body>
</html>


using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using Utility;
using System.Web.Configuration;
using System.Data;
using SystemDAO;
using System.IO;
using NLog;

public partial class Demo : System.Web.UI.Page
{
   
    protected void Page_Load(object sender, EventArgs e)
    {
        //指定要分頁的對象(GridView控制項在每次Get Method或PostBack後會重新new一份，參考會改變，所以每次都要指定分頁的對象)
        pager.ControlList = GridView1;

        //SQL語句
        string sql = @"Select *
                               from yourTable 
                               Where 1=1";//預設撈全部數據
        if (TextBox1.Text!="")
        {//每次postback時，要檢查是否拼接條件
            sql += " And 1=0 ";
        }
        pager.SqlQuery = sql;
        pager.SqlOrder = " Order by col1 DESC";
        
        if (!IsPostBack)//第一次Get Method進來時
        {
            pager.btnToPage_Click(null, null); //跳頁
        }
    }
      
    //查詢Click事件
    protected void Button1_Click(object sender, EventArgs e)
    {
        //SQL語句已在Page_Load事件組好
        //重新Bind GridView
        pager.btnToPage_Click(null, null); 
    }
}

衍伸閱讀其他部落客文章：

GridView 分頁元件 by 阿奴

[習題]上集 Ch 14-4 撰寫ADO.NET DataReader的分頁程式#1（搭配SQL指令 ROW_NUMBER）by mis2000Lab

回首頁