前言：
公司項(xiàng)目開(kāi)發(fā)，上周的任務(wù)是做基礎(chǔ)數(shù)據(jù)的管理。在Sharepoint2010里邊內(nèi)嵌asp.net的aspx頁(yè)，遇到了各種各樣奇葩的問(wèn)題，因?yàn)橹皩?duì)sharepoint只是有一些了解，但是沒(méi)有設(shè)計(jì)到具體的編程工作，這一次算是初次接觸吧。其中有一部分基礎(chǔ)數(shù)據(jù)數(shù)據(jù)量很大，大致有十多萬(wàn)，因?yàn)槭菍?duì)基礎(chǔ)數(shù)據(jù)的維護(hù)，所以還需要對(duì)數(shù)據(jù)進(jìn)行列表展示，增刪改查什么的，大家都知道Asp.net里邊的GridView有自帶的分頁(yè)，但是，那個(gè)分頁(yè)對(duì)于少量的數(shù)據(jù)還好，對(duì)于這種數(shù)十萬(wàn)的數(shù)據(jù)量而言，這種分頁(yè)方式簡(jiǎn)直就是災(zāi)難。網(wǎng)上關(guān)于GridView高效分頁(yè)的東西有很多，找了一個(gè)自己改了改。用著效果還不錯(cuò)，和大家分享一下。
這是分頁(yè)的效果圖
下邊就講一下具體的實(shí)現(xiàn)，首先聲明，這個(gè)東西是不是我原創(chuàng)的，只是在此基礎(chǔ)上進(jìn)行了修飾和完善。希望能給各位有所啟發(fā)。
一、前臺(tái)布局
復(fù)制代碼 代碼如下:
div>
div id="main">
div id="search">
table>
tr>
td>
asp:Label ID="lb" runat="server" Text="姓名">/asp:Label>/td>
td>
asp:TextBox ID="SearchName" runat="server">/asp:TextBox>
/td>
td>
asp:Button ID="btnSearch" runat="server" Text="查詢" onclick="PagerBtnCommand_OnClick" CommandName="search" />
/td>
td>
asp:Button ID="btnReset" runat="server" Text="重置" onclick="btnReset_Click" />
/td>
/tr>
/table>
/div>
div id="gridView">
asp:GridView ID="UserGridView" runat="server" AutoGenerateColumns="false">
Columns>
asp:TemplateField HeaderText="用戶名">
ItemTemplate>
asp:Label ID="UserName" runat="server" Text='%#Eval("username") %>'>/asp:Label>
/ItemTemplate>
/asp:TemplateField>
asp:TemplateField HeaderText="單位名稱">
ItemTemplate>
asp:Label ID="DisplayName" runat="server" Text='%#Eval("displayname") %>'>/asp:Label>
/ItemTemplate>
/asp:TemplateField>
asp:TemplateField HeaderText="組織編碼">
ItemTemplate>
asp:Label ID="OrgCode" runat="server" Text='%#Eval("orgcode") %>'>/asp:Label>
/ItemTemplate>
/asp:TemplateField>
asp:TemplateField HeaderText="組織名稱">
ItemTemplate>
asp:Label ID="OrgName" runat="server" Text='%#Eval("orgname") %>'>/asp:Label>
/ItemTemplate>
/asp:TemplateField>
/Columns>
/asp:GridView>
/div>
div id="page">
table>
tr>
td>
asp:Label ID="lbcurrentpage1" runat="server" Text="當(dāng)前頁(yè):">/asp:Label>
asp:Label ID="lbCurrentPage" runat="server" Text="">/asp:Label>
asp:Label ID="lbFenGe" runat="server" Text="/">/asp:Label>
asp:Label ID="lbPageCount" runat="server" Text="">/asp:Label>
/td>
td>
asp:Label ID="recordscount" runat="server" Text="總條數(shù):">/asp:Label>
asp:Label ID="lbRecordCount" runat="server" Text="">/asp:Label>
/td>
td>
asp:Button ID="Fistpage" runat="server" CommandName="" Text="首頁(yè)" OnClick="PagerBtnCommand_OnClick" />
asp:Button ID="Prevpage" runat="server" CommandName="prev" Text="上一頁(yè)"
OnClick="PagerBtnCommand_OnClick" />
asp:Button ID="Nextpage" runat="server" CommandName="next" Text="下一頁(yè)" OnClick="PagerBtnCommand_OnClick" />
asp:Button ID="Lastpage" runat="server" CommandName="last" Text="尾頁(yè)"
key="last" OnClick="PagerBtnCommand_OnClick" />
/td>
td>
asp:Label ID="lbjumppage" runat="server" Text="跳轉(zhuǎn)到第">/asp:Label>
asp:TextBox ID="GotoPage" runat="server" Width="25px">/asp:TextBox>
asp:Label ID="lbye" runat="server" Text="頁(yè)">/asp:Label>
asp:Button ID="Jump" runat="server" Text="跳轉(zhuǎn)" CommandName="jump" OnClick="PagerBtnCommand_OnClick" />
/td>
/tr>
/table>
/div>
/div>
/div>
布局的效果如下：
二、后臺(tái)的代碼實(shí)現(xiàn)
我會(huì)一點(diǎn)一點(diǎn)向大家講解清楚，這個(gè)分頁(yè)的原理
Members：這里主要定義幾個(gè)全局的變量，主要用來(lái)記錄信息的數(shù)量、頁(yè)的數(shù)量和當(dāng)前頁(yè)
復(fù)制代碼 代碼如下:
#region Members
const int PAGESIZE = 10;//每頁(yè)顯示信息數(shù)量
int PagesCount, RecordsCount;//記錄總頁(yè)數(shù)和信息總條數(shù)
int CurrentPage, Pages, JumpPage;//當(dāng)前頁(yè)，信息總頁(yè)數(shù)(用來(lái)控制按鈕失效)，跳轉(zhuǎn)頁(yè)碼
const string COUNT_SQL = "select count(*) from p_user";
#endregion
Methods：
1、GetRecordsCount：該方法主要用來(lái)獲取當(dāng)前信息的總數(shù)，有一個(gè)sqlSearch參數(shù)，默認(rèn)的為default,即初始化頁(yè)面時(shí)，查詢所有信息的總條數(shù)，當(dāng)用戶輸入要搜索的用戶名進(jìn)行檢索時(shí)，獲取符合用戶檢索條件的信息的總條數(shù)
復(fù)制代碼 代碼如下:
/// summary>
/// 獲取信息總數(shù)
/// /summary>
/// param name="sqlSearch">/param>
/// returns>/returns>
public static int GetRecordsCount(string sqlRecordsCount)
{
string sqlQuery;
if (sqlRecordsCount == "default")
{
sqlQuery = COUNT_SQL;
}
else
{
sqlQuery = sqlRecordsCount;
}
int RecordCount = 0;
SqlCommand cmd = new SqlCommand(sqlQuery, Conn());
RecordCount = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Connection.Close();
return RecordCount;
}
2、OverPage:該方法主要用來(lái)計(jì)算剩余頁(yè)，當(dāng)前設(shè)置的為每頁(yè)顯示10條數(shù)據(jù)，如何符合條件的數(shù)據(jù)有11條，則要顯示2頁(yè)
復(fù)制代碼 代碼如下:
/// summary>
/// 計(jì)算余頁(yè)
/// /summary>
/// returns>/returns>
public int OverPage()
{
int pages = 0;
if (RecordsCount % PAGESIZE != 0)
pages = 1;
else
pages = 0;
return pages;
}
3、ModPage：該方法也是用計(jì)算余頁(yè)，主要用來(lái)防止SQL執(zhí)行溢出
復(fù)制代碼 代碼如下:
/// summary>
/// 計(jì)算余頁(yè)，防止SQL語(yǔ)句執(zhí)行時(shí)溢出查詢范圍
/// /summary>
/// returns>/returns>
public int ModPage()
{
int pages = 0;
if (RecordsCount % PAGESIZE == 0 RecordsCount != 0)
pages = 1;
else
pages = 0;
return pages;
}
4、Conn：該方法用來(lái)創(chuàng)建數(shù)據(jù)連接對(duì)象，在使用的時(shí)候只需改成自己的數(shù)據(jù)庫(kù)名即可
復(fù)制代碼 代碼如下:
/// summary>
/// 數(shù)據(jù)連接對(duì)象
/// /summary>
/// returns>/returns>
public static SqlConnection Conn()
{
SqlConnection conn = new SqlConnection("data source=.;initial catalog=DB_GSL_ZCW;Integrated Security=true");
conn.Open();
return conn;
}
5、GridViewDataBind：該方法主要用來(lái)數(shù)據(jù)綁定，如果傳入的參數(shù)為default則，默認(rèn)的綁定所有的數(shù)據(jù)，否則，則綁定過(guò)濾過(guò)的數(shù)據(jù)
復(fù)制代碼 代碼如下:
/// summary>
/// GridView數(shù)據(jù)綁定，根據(jù)傳入?yún)?shù)的不同，進(jìn)行不同方式的查詢，
/// /summary>
/// param name="sqlSearch">/param>
private void GridViewDataBind(string sqlSearch)
{
CurrentPage = (int)ViewState["PageIndex"];
//從ViewState中讀取頁(yè)碼值保存到CurrentPage變量中進(jìn)行按鈕失效運(yùn)算
Pages = (int)ViewState["PagesCount"];
//從ViewState中讀取總頁(yè)參數(shù)進(jìn)行按鈕失效運(yùn)算
//判斷四個(gè)按鈕（首頁(yè)、上一頁(yè)、下一頁(yè)、尾頁(yè)）狀態(tài)
if (CurrentPage + 1 > 1)//當(dāng)前頁(yè)是否為首頁(yè)
{
Fistpage.Enabled = true;
Prevpage.Enabled = true;
}
else
{
Fistpage.Enabled = false;
Prevpage.Enabled = false;
}
if (CurrentPage == Pages)//當(dāng)前頁(yè)是否為尾頁(yè)
{
Nextpage.Enabled = false;
Lastpage.Enabled = false;
}
else
{
Nextpage.Enabled = true;
Lastpage.Enabled = true;
}
DataSet ds = new DataSet();
string sqlResult;
//根據(jù)傳入?yún)?shù)sqlSearch進(jìn)行判斷，如果為default則為默認(rèn)的分頁(yè)查詢，否則為添加了過(guò)濾條件的分頁(yè)查詢
if (sqlSearch == "default")
{
sqlResult = "Select Top " + PAGESIZE + "user_serialid,username,displayname,orgcode,orgname from p_user where user_serialid not in(select top " + PAGESIZE * CurrentPage + " user_serialid from p_user order by user_serialid asc) order by user_serialid asc";
}
else
{
sqlResult = sqlSearch;
}
SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlResult, Conn());
sqlAdapter.Fill(ds, "Result");
UserGridView.DataSource = ds.Tables["Result"].DefaultView;
UserGridView.DataBind();
//顯示Label控件lbCurrentPaget和文本框控件GotoPage狀態(tài)
lbCurrentPage.Text = (CurrentPage + 1).ToString();
GotoPage.Text = (CurrentPage + 1).ToString();
sqlAdapter.Dispose();
}
6、Page_Load：頁(yè)面加載函數(shù)，主要是在首次進(jìn)入頁(yè)面時(shí)，進(jìn)行初始化，默認(rèn)的獲取所有的信息
復(fù)制代碼 代碼如下:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)//首次進(jìn)行該頁(yè)時(shí)，頁(yè)面初始化
{
RecordsCount = GetRecordsCount("default");//默認(rèn)信息總數(shù)
PagesCount = RecordsCount / PAGESIZE + OverPage();//默認(rèn)的頁(yè)總數(shù)
ViewState["PagesCount"] = RecordsCount / PAGESIZE - ModPage();//保存末頁(yè)索引，比頁(yè)總數(shù)小1
ViewState["PageIndex"] = 0;//保存頁(yè)面初始索引從0開(kāi)始
ViewState["JumpPages"] = PagesCount;
//保存頁(yè)總數(shù)，跳頁(yè)時(shí)判斷用戶輸入數(shù)是否超出頁(yè)碼范圍
//顯示lbPageCount、lbRecordCount的狀態(tài)
lbPageCount.Text = PagesCount.ToString();
lbRecordCount.Text = RecordsCount.ToString();
//判斷跳頁(yè)文本框失效
if (RecordsCount = 10)
{
GotoPage.Enabled = false;
}
GridViewDataBind("default");//調(diào)用數(shù)據(jù)綁定函數(shù)TDataBind()進(jìn)行數(shù)據(jù)綁定運(yùn)算
}
}
7、PagerBtnCommand_OnClick：該方法主要用來(lái)處理設(shè)計(jì)視圖頁(yè)的“首頁(yè)”、“下一頁(yè)”，“上一頁(yè)”，“尾頁(yè)”，“查詢”按鈕的Click事件，主要通過(guò)不同按鈕的CommandName屬性來(lái)分別處理，需要在前臺(tái)為每一個(gè)按鈕相應(yīng)的CommandName屬性賦值，如果用戶點(diǎn)擊的是“查詢”按鈕，這個(gè)時(shí)候需要對(duì)查詢的Sql語(yǔ)句進(jìn)行重寫(xiě)，加入過(guò)濾條件，即用戶輸入的查詢的條件
復(fù)制代碼 代碼如下:
/// summary>
/// 頁(yè)面按鈕Click處理
/// /summary>
/// param name="sender">/param>
/// param name="e">/param>
protected void PagerBtnCommand_OnClick(object sender, EventArgs e)
{
CurrentPage = (int)ViewState["PageIndex"];
//從ViewState中讀取頁(yè)碼值保存到CurrentPage變量中進(jìn)行參數(shù)運(yùn)算
Pages = (int)ViewState["PagesCount"];//從ViewState中讀取總頁(yè)參數(shù)運(yùn)算
Button btn = sender as Button;
string sqlResult="default";
if (btn != null)
{
string cmd = btn.CommandName;
switch (cmd)//根據(jù)不同的CommandName做出不同的處理
{
case "next":
CurrentPage++;
break;
case "prev":
CurrentPage--;
break;
case "last":
CurrentPage = Pages;
break;
case "search":
if (!string.IsNullOrEmpty(SearchName.Text))
{
RecordsCount = GetRecordsCount("select count(*) from p_user where username like '" + SearchName.Text + "%'");//獲取過(guò)濾后的總記錄數(shù)
PagesCount = RecordsCount / PAGESIZE + OverPage();//該變量為頁(yè)總數(shù)
ViewState["PagesCount"] = RecordsCount / PAGESIZE - ModPage();//該變量為末頁(yè)索引，比頁(yè)總數(shù)小1
ViewState["PageIndex"] = 0;//保存一個(gè)為0的頁(yè)面索引值到ViewState，頁(yè)面索引從0開(kāi)始
ViewState["JumpPages"] = PagesCount;
//保存PageCount到ViewState，跳頁(yè)時(shí)判斷用戶輸入數(shù)是否超出頁(yè)碼范圍
//顯示lbPageCount、lbRecordCount的狀態(tài)
lbPageCount.Text = PagesCount.ToString();
lbRecordCount.Text = RecordsCount.ToString();
//判斷跳頁(yè)文本框失效
if (RecordsCount = 10)
GotoPage.Enabled = false;
sqlResult = "Select Top " + PAGESIZE + "user_serialid,username,displayname,orgcode,orgname from p_user where user_serialid not in(select top " + PAGESIZE * CurrentPage + " user_serialid from p_user order by user_serialid asc) and username like '" + SearchName.Text + "%' order by user_serialid asc";
}
else
{
Response.Write("請(qǐng)輸入您所要查找的用戶姓名！");
}
break;
case "jump":
JumpPage = (int)ViewState["JumpPages"];
//從ViewState中讀取可用頁(yè)數(shù)值保存到JumpPage變量中
//判斷用戶輸入值是否超過(guò)可用頁(yè)數(shù)范圍值
if(Int32.Parse(GotoPage.Text) > JumpPage || Int32.Parse(GotoPage.Text) = 0)
Response.Write("script>alert('頁(yè)碼范圍越界！')/script>");
else
{
int InputPage = Int32.Parse(GotoPage.Text.ToString()) - 1;
//轉(zhuǎn)換用戶輸入值保存在int型InputPage變量中
ViewState["PageIndex"] = InputPage;
CurrentPage = InputPage;
//寫(xiě)入InputPage值到ViewState["PageIndex"]中
sqlResult = "Select Top " + PAGESIZE + "user_serialid,username,displayname,orgcode,orgname from p_user where user_serialid not in(select top " + PAGESIZE * CurrentPage + " user_serialid from p_user order by user_serialid asc) and username like '" + SearchName.Text + "%' order by user_serialid asc";
}
break;
default:
CurrentPage = 0;
break;
}
ViewState["PageIndex"] = CurrentPage;
//將運(yùn)算后的CurrentPage變量再次保存至ViewState
GridViewDataBind(sqlResult);//調(diào)用數(shù)據(jù)綁定函數(shù)TDataBind()
}
}
8、btn_Reset_Click:該方法主要用來(lái)進(jìn)行重置，用戶完成一次查詢之后，需要重置，才能進(jìn)行下一次的查詢操作
復(fù)制代碼 代碼如下:
protected void btnReset_Click(object sender, EventArgs e)
(
RecordsCount = GetRecordsCount("default");//默認(rèn)信息總數(shù)
PagesCount = RecordsCount / PAGESIZE + OverPage();//默認(rèn)的頁(yè)總數(shù)
ViewState["PagesCount"] = RecordsCount / PAGESIZE - ModPage();//保存末頁(yè)索引，比頁(yè)總數(shù)小1
ViewState["PageIndex"] = 0;//保存頁(yè)面初始索引從0開(kāi)始
ViewState["JumpPages"] = PagesCount;
//保存頁(yè)總數(shù)，跳頁(yè)時(shí)判斷用戶輸入數(shù)是否超出頁(yè)碼范圍
//顯示lbPageCount、lbRecordCount的狀態(tài)
lbPageCount.Text = PagesCount.ToString();
lbRecordCount.Text = RecordsCount.ToString();
//判斷跳頁(yè)文本框失效
if (RecordsCount = 10)
{
GotoPage.Enabled = false;
}
GridViewDataBind("default");//調(diào)用數(shù)據(jù)綁定函數(shù)TDataBind()進(jìn)行數(shù)據(jù)綁定運(yùn)算
}
這里的高效分頁(yè)方法主要用的是select top 10 Id ，Name from tb where Id not in (select top 10*N from tb order by Id asc) order by Id asc
示例中的N代表的是頁(yè)數(shù)，之前原子里也有很多關(guān)于高效分頁(yè)的討論，這里就不再多說(shuō)了，我個(gè)人覺(jué)得這個(gè)分頁(yè)語(yǔ)句效果還不錯(cuò)，當(dāng)然除此之外還有row_number（）函數(shù)分頁(yè)、select Max() 分頁(yè)等等方法，之前也有總結(jié)過(guò)，有興趣的朋友可以看一下我之前寫(xiě)的ListView和Repeater高效分頁(yè)這篇文章，里邊講述的也很詳細(xì)，只要你一步一步的照著去操練應(yīng)該問(wèn)題不大的。
這里需要解釋一下的是為什么沒(méi)有有數(shù)據(jù)綁定控件直接進(jìn)行綁定，因?yàn)樵趕harepoint2010項(xiàng)目里邊它支持的數(shù)據(jù)源控件只有XMLDataSource，所以就只有自己動(dòng)手實(shí)現(xiàn)了。
好了今天就到這里了，希望能給大家?guī)?lái)些幫助吧！還請(qǐng)多多指教！
您可能感興趣的文章:- asp.net gridview分頁(yè)：第一頁(yè) 下一頁(yè) 1 2 3 4 上一頁(yè) 最末頁(yè)
- asp.net中的GridView分頁(yè)問(wèn)題
- Android入門之ActivityGroup+GridView實(shí)現(xiàn)Tab分頁(yè)標(biāo)簽的方法
- GridView分頁(yè)的實(shí)現(xiàn)以及自定義分頁(yè)樣式功能實(shí)例
- Android中實(shí)現(xiàn)多行、水平滾動(dòng)的分頁(yè)的Gridview實(shí)例源碼
- Asp.net GridView使用大全(分頁(yè)實(shí)現(xiàn))
- GridView分頁(yè)的實(shí)現(xiàn)(通用分頁(yè)模板)
- AspNetPager+GridView實(shí)現(xiàn)分頁(yè)的實(shí)例代碼
- asp.net中g(shù)ridview的查詢、分頁(yè)、編輯更新、刪除的實(shí)例代碼
- 解析GridView自帶分頁(yè)及與DropDownList結(jié)合使用
