用Gridview和ObjectDataSource轻松实现自定义分页
?一．什么是自定義分頁
??????? 自定義分頁是與默認分頁相對應的。默認分頁指一次檢索出所有數據并將其綁定到數據綁定控件中，雖然該控件只能一頁一頁顯示這些數據，但是所有數據其實都已經被綁定到控件上了。自定義分頁的含義是顯示到哪一頁就檢索并綁定哪一頁的數據。顯然在大數據量的情況下，自定義分頁的效率會高很多。??
??????? 在Asp.net 1.x中自定義分頁又稱數據庫分頁，DataGrid中的AllowCustomerPaging屬性和VirtualItemCount屬性就是專門為自定義分頁準備的。在Asp.net 2.0中因為引入了數據源的概念，因此自定義分頁也可以叫做數據源分頁。
二．為什么使用ObjectDataSource
??????? ASP.NET2.0提供了SqlDataSource數據源控件，提供了ConnectionString、SelectCommand、SelectCommandType、SelectParameters等屬性，分別用于指定連接字符串、SQL查詢語句、SQL查詢語句的類型和查詢所使用的參數。SqlDataSource數據源控件根據這些屬性的設定從關系數據庫中獲取數據。但是，SqlDataSource 控件存在一個問題：該控件的缺點在于它迫使您將用戶界面層與數據訪問層混合在一起，忽略了業務邏輯層。然而隨著應用程序規模的擴大，具有清晰的用戶界面層、業務邏輯層、數據訪問層以及數據實體層是極為必要的。僅僅通過 SqlDataSource 控件的屬性，在用戶界面層引用 SQL 語句或存儲過程是不可取的，或說是缺乏架構意識的，不利于代碼的重用和維護。另外，SqlDataSource也不支持數據源分頁，也就不能實現自定義分頁。
??????? ObjectDataSource 控件對象模型類似于 SqlDataSource 控件。但ObjectDataSource 提供一個 TypeName 屬性（而不是 ConnectionString屬性），該屬性指定用于執行數據操作的業務邏輯類的類名，ObjectDataSource可以通過TypeName 屬性直接調用業務層的類。類似于 SqlDataSource 的命令屬性SelectCommand、InsertCommand、UpdateCommand、DeleteCommand，ObjectDataSource 控件支持諸如 SelectMethod、UpdateMethod、InsertMethod 和 DeleteMethod屬性，用于指定執行這些操作的方法名。顯然ObjectDataSource是依托于一個業務邏輯類的，這樣我們就可以擁有完善的架構，業務邏輯類可以為復雜的業務邏輯提供好的支持，也有利于代碼的重用和維護。特別是ObjectDataSource 控件提供了EnablePaging屬性、SelectCountMethod屬性、StartRowIndexParameterName屬性和MaximumRowsParameterName屬性專門支持數據源分頁。?SelectCountMethod屬性指定的是獲取數據項總數的方法。StartRowIndexParameterName屬性用于指定一個參數的名稱，如程序中不特別設定，其默認參數名為startRowIndex，該參數代表該頁數據項的開始行索引；MaximumRowsParameterName屬性也用于指定一個參數名稱，其默認參數名為maximumRows，該參數代表一頁中容納的數據項總數。
三．示例
??????? 本例是以SQL Server自帶的Northwind數據庫的Orders表為主，Employees表和Customers表為輔，顯示OrderDate在1997年之前的Order列表。
(1). 實體層
??????? 在實體層中創建Order、Employee、Customer三個類，其中Order引用了Employee類和Customer類。
(2).??數據訪問層
????????數據訪問層使用了微軟提供的SqlHelper類。
????{
????????private?string??ConnectionString?=?Convert.ToString(ConfigurationManager.ConnectionStrings["NorthWindConnectionString"]);
????????public?OrderDataAccess()
????????{
????????????//
????????????//?TODO:?Add?constructor?logic?here
????????????//
????????}
????????public?int?CountTotalNumber()
????????{
????????????return?Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString,?CommandType.StoredProcedure,?"Order_Select_TotalNumber"));?
????????}
????????public?IEnumerable?FindOrders(int?startRowIndex,?int?maximumRows,bool?isDataSet)
????????{
????????????SqlParameter[]?parms?=?{
????????????????new?SqlParameter("@StartRowIndex",SqlDbType.Int,4),
????????????????new?SqlParameter("@MaximumRows",SqlDbType.Int,4)
????????????};
????????????parms[0].Value?=?startRowIndex;
????????????parms[1].Value?=?maximumRows;
????????????if(!isDataSet)
????????????{
????????????????ArrayList?OrderList?=?new?ArrayList();
????????????????using?(SqlDataReader?reader?=?SqlHelper.ExecuteReader(ConnectionString,?CommandType.StoredProcedure,?"Order_Select_Pagination",?parms))
????????????????{
????????????????????while?(reader.Read())
????????????????????{
????????????????????????OrderList.Add(LoadOrder(reader));
????????????????????}
????????????????}
????????????????return?OrderList;
????????????}
????????????else
????????????{
????????????????DataSet?ds?=?SqlHelper.ExecuteDataset(ConnectionString,?CommandType.StoredProcedure,?"Order_Select_Pagination",?parms);
????????????????return?ds.Tables[0].DefaultView;
????????????}
????????}
????????public?int?DeleteOrder(int?orderId)
????????{
????????????SqlParameter?parm?=?new?SqlParameter("@OrderID",SqlDbType.Int,4);
????????????parm.Value?=?orderId;
????????????return?SqlHelper.ExecuteNonQuery(ConnectionString,CommandType.StoredProcedure,"Order_Delete",parm);
????????}
????????private?Order?LoadOrder(SqlDataReader?reader)
????????{
????????????Order?order?=?new?Order();
????????????order.OrderId?=?Convert.ToInt32(reader["OrderID"]);
????????????order.Customer?=?new?Customer(Convert.ToString(reader["CustomerID"]),?Convert.ToString(reader["CompanyName"]));
????????????order.Employee?=?new?Employee(Convert.ToInt32(reader["EmployeeID"]),?Convert.ToString(reader["LastName"]),?Convert.ToString(reader["FirstName"]));
????????????order.OrderDate?=?Convert.ToDateTime(reader["OrderDate"]);
????????????order.RequiredDate?=?Convert.ToDateTime(reader["RequiredDate"]);
????????????order.ShippedDate?=?Convert.ToDateTime(reader["ShippedDate"]);
????????????order.ShipVia?=?Convert.ToInt32(reader["ShipVia"]);
????????????order.Freight?=?Convert.ToDecimal(reader["Freight"]);
????????????order.ShipName?=?Convert.ToString(reader["ShipName"]);
????????????order.ShipAddress?=?Convert.ToString(reader["ShipAddress"]);
????????????order.ShipCity?=?Convert.ToString(reader["ShipCity"]);
????????????order.ShipRegion?=?Convert.ToString(reader["ShipRegion"]);
????????????order.ShipPostalCode?=?Convert.ToString(reader["ShipPostalCode"]);
????????????order.ShipCountry?=?Convert.ToString(reader["ShipCountry"]);
????????????return?order;
????????}
????}
??????? 注意FindOrders方法，它帶有三個參數，startRowIndex代表起始行的索引，maxmiumRows代表本次查詢所要獲得的數據項總數，isDataSet是為標示是使用DataSet還是SqlDataReader，如果表示層的GridView設置了屬性AllowSorting為true，也就是要求具有排序功能，那么數據訪問層就必須使用DataSet來容納數據，否則使用SqlDataReader就可以了。
(3). 存儲過程
獲取符合要求的總訂單數存儲過程：
ALTER?PROCEDURE?Order_Select_TotalNumber?AS
SET?NOCOUNT?ON
Select?Count(OrderID)
From?Orders?
Where?OrderDate?<?'1997'
RETURN?
分頁獲取數據的存儲過程：
ALTER?PROCEDURE?Order_Select_Pagination?(
????@StartRowIndex?int?=?null,
????@MaximumRows?int?=?null
)
AS
SET?NOCOUNT?ON
DECLARE?@PageLowerBound?int
DECLARE?@PageUpperBound?int
--?Set?the?page?bounds
SET?@PageLowerBound?=?@StartRowIndex
SET?@PageUpperBound?=?@PageLowerBound?+?@MaximumRows?+?1
--?Create?a?temp?table?to?store?the?select?results
CREATE?TABLE?#tmp
(
?????RecNo?int?IDENTITY?(1,?1)?NOT?NULL,
?????OrderID?int
)
INSERT?INTO?#tmp
????????SELECT?[OrderID]
????????FROM?[Orders]
????????Where?OrderDate?<?'1997'
????????ORDER?BY?OrderID?ASC
SELECT?o.*,e.LastName,e.FirstName,c.CompanyName
FROM?Orders?o?inner?join?Employees?e?on?o.EmployeeID?=?e.EmployeeID?inner?join?Customers?c?on?o.CustomerID?=?c.CustomerID,?#tmp?t
WHERE?o.OrderID?=?t.OrderID?AND
?????t.RecNo?>?@PageLowerBound?AND
?????t.RecNo?<?@PageUpperBound
ORDER?BY?t.RecNo
????RETURN
(4). 業務邏輯層
??????? 本例的業務邏輯很簡單，只是作為表示層和數據訪問層之間的橋梁，并沒有摻雜其它的運算邏輯。
業務邏輯類中的方法可以設置給ObjectDataSource控件的SelectCountMethod屬性和SelectMethod屬性，這樣ObjectDataSource就可以自動通過業務邏輯類獲得數據了。
????{
????????private?static?OrderDataAccess?orderAccess?=?new?OrderDataAccess();
????????
????????public?OrderBusinessLogic()
????????{
????????????//
????????????//?TODO:?Add?constructor?logic?here
????????????//
????????}
????????public?static?int?GetRowsTotalNumber()
????????{
????????????return?orderAccess.CountTotalNumber();
????????}
????????public?static?IEnumerable?GetOrdersForPagingAndSorting(int?startRowIndex,?int?maximumRows)
????????{
???????????return?orderAccess.FindOrders(startRowIndex,?maximumRows,true);??????????
????????}
????????public?static?IEnumerable?GetOrdersForPaging(int?startRowIndex,?int?maximumRows)
????????{
????????????return?orderAccess.FindOrders(startRowIndex,?maximumRows,?false);
????????}
????????public?static?void?DeleteOrder(int?orderId)
????????{
????????????orderAccess.DeleteOrder(orderId);
????????}???????
????}
???????值得注意的兩點是：第一，這里的方法都是用了靜態方法，其實也可以不使用靜態方法。不使用靜態方法時Asp.net會先實例化ObjectDataSource的TypeName中設定的類，然后調用它的方法。第二，GetOrdersForPagingAndSorting和GetOrdersForPaging兩個方法，前者是為了應對GridView的排序要求，因為為了能夠實現排序，必須使用DataView、DataTable或DataSet；而后者則不用于排序，只需返回一個SqlDataReader。
(5).頁面程序
??????? 如果想只使用下圖所示GridView的默認分頁樣式，則按照下面的頁面代碼，不必再寫任何后臺代碼就可實現。
???
????????????AllowPaging="true"?runat="server"?AllowSorting="true"?Width="720px"?PageSize="20">
????????????<PagerStyle?ForeColor="Blue"?BackColor="LightBlue"?/>????????????
????????????<Columns>
????????????????<asp:BoundField?HeaderText="Order?Id"?DataField="OrderId"??/>
????????????????<asp:TemplateField?HeaderText="Customer">
????????????????????<ItemTemplate>
????????????????????????<%#?Eval("Customer.CompanyName")%>
????????????????????</ItemTemplate>
????????????????</asp:TemplateField>
????????????????<asp:TemplateField?HeaderText="Employee">
????????????????????<ItemTemplate>
????????????????????????<%#?Eval("Employee.EmployeeName")%>
????????????????????</ItemTemplate>
????????????????</asp:TemplateField>
????????????????<asp:BoundField?HeaderText="Order?date"?DataField="OrderDate"?DataFormatString="{0:g}"?/>
????????????????<asp:BoundField?HeaderText="Required?date"?DataField="RequiredDate"?DataFormatString="{0:d}"?/>
????????????????<asp:BoundField?HeaderText="Shipped?date"?DataField="ShippedDate"?DataFormatString="{0:d}"?/>
????????????????<asp:BoundField?HeaderText="Ship?address"?DataField="ShipAddress"?/>
????????????????<asp:BoundField?HeaderText="Ship?country"?DataField="ShipCountry"?/>????????????????
????????????</Columns>
????????</asp:GridView>
????????<asp:ObjectDataSource?ID="OrdersObjectDataSource"?runat="server"?SelectCountMethod="GetRowsTotalNumber"
??????????SelectMethod="GetOrdersForPaging"?TypeName="MyTest.BusinessLogic.OrderBusinessLogic"?OldValuesParameterFormatString="Original_{0}"?EnablePaging="true">??????????
????????</asp:ObjectDataSource>
??????? 如果想實現如下圖所示的自定義的分頁樣式，則參考下列代碼：
????????????AllowPaging="true"?AllowSorting="true"?OnDataBound="OrdersGridView_DataBound"?runat="server"?
????????????Width="720px"?PageSize="25"?OnRowDeleted="OrdersGridView_RowDeleted"?DataKeyNames="OrderID">
????????????<Columns>
????????????????<asp:BoundField?HeaderText="Order?Id"?DataField="OrderID"?SortExpression="OrderID"/>
????????????????<asp:BoundField?HeaderText="Customer?company"?DataField="CompanyName"?SortExpression="CompanyName"/>
????????????????<asp:TemplateField?HeaderText="Employee"?SortExpression="EmployeeName">
??????????????????<ItemTemplate>
??????????????????????<%#?Eval("LastName")+"?"+Eval("FirstName")?%>??
??????????????????</ItemTemplate>
????????????????</asp:TemplateField>????????????????????????????????????????????????
????????????????<asp:BoundField?HeaderText="Order?date"?DataField="OrderDate"?DataFormatString="{0:g}"?SortExpression="OrderDate"/>
????????????????<asp:BoundField?HeaderText="Required?date"?DataField="RequiredDate"?DataFormatString="{0:d}"?SortExpression="RequiredDate"/>
????????????????<asp:BoundField?HeaderText="Shipped?date"?DataField="ShippedDate"?DataFormatString="{0:d}"?SortExpression="ShippedDate"/>
????????????????<asp:BoundField?HeaderText="Ship?address"?DataField="ShipAddress"?/>
????????????????<asp:BoundField?HeaderText="Ship?country"?DataField="ShipCountry"?/>????????????????
????????????????<asp:CommandField?ButtonType="Button"?DeleteText="刪除"?ShowDeleteButton="true"?HeaderText="Operation"??/>
????????????</Columns>
????????????<PagerStyle?ForeColor="Blue"?BackColor="LightBlue"?/>????????????
????????????<PagerTemplate>
????????????????<table?width="100%">
????????????????????<tr>
????????????????????????<td?width="70%">
????????????????????????????<asp:Label?ID="MessageLabel"?ForeColor="Blue"?Text="頁碼:"?runat="server"?/>
????????????????????????????<asp:DropDownList?ID="PageDropDownList"?AutoPostBack="true"?OnSelectedIndexChanged="PageDropDownList_SelectedIndexChanged"
????????????????????????????????runat="server"?/>
????????????????????????????<asp:LinkButton?CommandName="Page"?CommandArgument="First"?ID="linkBtnFirst"?runat="server">首頁</asp:LinkButton>
????????????????????????????<asp:LinkButton?CommandName="Page"?CommandArgument="Prev"?ID="linkBtnPrev"?runat="server">上一頁</asp:LinkButton>
????????????????????????????<asp:LinkButton?CommandName="Page"?CommandArgument="Next"?ID="linkBtnNext"?runat="server">下一頁</asp:LinkButton>
????????????????????????????<asp:LinkButton?CommandName="Page"?CommandArgument="Last"?ID="linkBtnLast"?runat="server">末頁</asp:LinkButton>
????????????????????????</td>
????????????????????????<td?align="right">
????????????????????????????<asp:Label?ID="CurrentPageLabel"?ForeColor="Blue"?runat="server"?/>
????????????????????????</td>
????????????????????</tr>
????????????????</table>
????????????</PagerTemplate>
????????</asp:GridView>
????????<asp:ObjectDataSource?ID="OrdersObjectDataSource"?runat="server"?SelectCountMethod="GetRowsTotalNumber"
??????????SelectMethod="GetOrdersForPagingAndSorting"?DeleteMethod="DeleteOrder"
??????????TypeName="MyTest.BusinessLogic.OrderBusinessLogic"?EnablePaging="true"?EnableViewState="true">??????????
????????<DeleteParameters>
????????????<asp:Parameter?Name="OrderId"?Type="Int32"?Direction="Input"?/>
????????</DeleteParameters>
????????</asp:ObjectDataSource>
??????? 注意頁導航模板PagerTemplate屬性的使用。通常將按鈕控件(如上面代碼中的LinkButton)添加到頁導航模板以執行分頁操作。單擊 CommandName 屬性設置為“Page”的按鈕控件時，GridView 控件會執行分頁操作。按鈕的 CommandArgument 屬性確定要執行的分頁操作的類型。下表列出了 GridView 控件支持的命令參數值。?
| “Next” | 導航至下一頁。 |
| “Prev” | 導航至上一頁。 |
| “First” | 導航至第一頁。 |
| “Last” | 導航至最后一頁。 |
| 整數值 | 導航至指定頁碼。 |
??????? 頁面程序的后臺代碼：
public?partial?class?TestGridview2?:?System.Web.UI.Page{
????protected?void?Page_Load(object?sender,?EventArgs?e)
????{
????}
????protected?void?PageDropDownList_SelectedIndexChanged(Object?sender,?EventArgs?e)
????{
????????GridViewRow?pagerRow?=?OrdersGridView.BottomPagerRow;
????????DropDownList?pageList?=?(DropDownList)pagerRow.Cells[0].FindControl("PageDropDownList");
????????OrdersGridView.PageIndex?=?pageList.SelectedIndex;
????}
????protected?void?OrdersGridView_DataBound(Object?sender,?EventArgs?e)
????{
????????GridViewRow?pagerRow?=?OrdersGridView.BottomPagerRow;
????????LinkButton?linkBtnFirst?=?(LinkButton)pagerRow.Cells[0].FindControl("linkBtnFirst");
????????LinkButton?linkBtnPrev?=?(LinkButton)pagerRow.Cells[0].FindControl("linkBtnPrev");
????????LinkButton?linkBtnNext?=?(LinkButton)pagerRow.Cells[0].FindControl("linkBtnNext");
????????LinkButton?linkBtnLast?=?(LinkButton)pagerRow.Cells[0].FindControl("linkBtnLast");
????????if?(OrdersGridView.PageIndex?==?0)
????????{
????????????linkBtnFirst.Enabled?=?false;
????????????linkBtnPrev.Enabled?=?false;
????????}
????????else?if?(OrdersGridView.PageIndex?==?OrdersGridView.PageCount?-?1)
????????{
????????????linkBtnLast.Enabled?=?false;
????????????linkBtnNext.Enabled?=?false;
????????}
????????else?if?(OrdersGridView.PageCount?<=?0)
????????{
????????????linkBtnFirst.Enabled?=?false;
????????????linkBtnPrev.Enabled?=?false;
????????????linkBtnNext.Enabled?=?false;
????????????linkBtnLast.Enabled?=?false;
????????}
????????DropDownList?pageList?=?(DropDownList)pagerRow.Cells[0].FindControl("PageDropDownList");
????????Label?pageLabel?=?(Label)pagerRow.Cells[0].FindControl("CurrentPageLabel");
????????if?(pageList?!=?null)
????????{
????????????for?(int?i?=?0;?i?<?OrdersGridView.PageCount;?i++)
????????????{
????????????????int?pageNumber?=?i?+?1;
????????????????ListItem?item?=?new?ListItem(pageNumber.ToString()?+?"/"?+?OrdersGridView.PageCount.ToString(),?pageNumber.ToString());
????????????????if?(i?==?OrdersGridView.PageIndex)
????????????????{
????????????????????item.Selected?=?true;
????????????????}
????????????????pageList.Items.Add(item);
????????????}
????????}
????????if?(pageLabel?!=?null)
????????{
????????????int?currentPage?=?OrdersGridView.PageIndex?+?1;
????????????pageLabel.Text?=?"當前頁：?"?+?currentPage.ToString()?+
??????????????"?/?"?+?OrdersGridView.PageCount.ToString();
????????}
????}
????protected?void?OrdersGridView_RowDeleted(object?sender,GridViewDeletedEventArgs?e)
????{
????????if?(e.Exception?==?null?&&?OrdersGridView.Rows.Count?==?1)
????????{
????????????//?we?just?deleted?the?last?row
????????????OrdersGridView.PageIndex?=?Math.Max(0,?OrdersGridView.PageIndex?-?1);
?????????}
????}
}
????
??? 由以上示例可以看出GridView關聯ObjectDataSource時，省去了DataBind方法的使用。也就是說只要給GridView關聯上數據源控件，那么綁定的事程序員就不用操心了。當GridView發生翻頁事件時整個的運行過程是這樣的，GridView的PageIndex變成新值，ObjectDataSource根據GridView的PageIndex屬性換算出startRowIndex和MaxmiumRows的值，然后傳遞這兩個參數給SelectMethod指定的方法從而獲得數據，然后再調用SelectCountMethod指定的方法獲得總數據項數，以計算出總頁數，然后執行OrderGridView_DataBound事件處理方法最終完成綁定工作。
??? 注意OrdersGridView_RowDeleted事件處理方法的寫法，它是為了應對將最后一頁的最后一條數據刪除之后，GridView將能夠識別出最后一頁已經被刪空了，因此原來的倒數第二頁就變成了現在的末頁了，并讓GridView的當前頁指向末頁。
????下載完整源程序/Files/taewind/TestDataBindControlls.rar
總結
以上是生活随笔為你收集整理的用Gridview和ObjectDataSource轻松实现自定义分页的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： moment.js插件的简单上手使用
- 下一篇： Learning OSG program