[ASP.net WebForm] 製作可重複使用的分頁WebUserControl
※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>選擇頁數 <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