星期五, 11月 21, 2008

用ObjectDataSource來處理分頁效率

今天試寫了一下用objectdatasource+girdview去把分頁這功能做出來..留下紀錄..
給之後要寫分頁的朋友參考..PigPigPigPigPigPig..

---store procedure---

CREATE PROCEDURE dbo._Paging

@PageIndex INT,
@PageSize INT,
@PageNumberCount INT OUTPUT

AS
SET NOCOUNT ON

begin
SELECT @PageNumberCount = COUNT(ProductID) FROM Products
end

begin
WITH tempTable AS (
SELECT ROW_NUMBER() OVER (ORDER BY ProductID)
AS Row, ProductID, ProductName, UnitPrice, UnitsInStock
FROM Products)

SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM tempTable
WHERE Row between
(@PageIndex - 1) * @PageSize + 1 and @PageIndex * @PageSize
end

RETURN

------.cs--------

public class test1
{
int _PageNumberCount;
public DataTable getDataTable(int startRowIndex, int maximumRows)
{
int pageIndex = (startRowIndex / maximumRows) + 1;

using (SqlConnection sqlConn =
new SqlConnection(ConfigurationManager
.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString))
{
SqlCommand sqlComm = sqlConn.CreateCommand();
sqlComm.CommandText = "_Paging";
sqlComm.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable();

sqlComm.Parameters.AddWithValue("@PageIndex", pageIndex);
sqlComm.Parameters.AddWithValue("@PageSize", maximumRows);
SqlParameter sp = new SqlParameter("@PageNumberCount", 0);
sp.Direction = ParameterDirection.Output;
sqlComm.Parameters.Add(sp);
sqlConn.Open();
dt.Load(sqlComm.ExecuteReader());
_PageNumberCount = Convert.ToInt32(sp.Value);
return dt;
}
}
public int GetTotleRowsCount()
{
return _PageNumberCount;
}
}

------.aspx------

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>

<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True"
SelectMethod="getDataTable" TypeName="test1"
SelectCountMethod="GetTotleRowsCount">
</asp:ObjectDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
DataSourceID="ObjectDataSource1" PageSize="5">
</asp:GridView>
</div>
</form>
</body>
</html>

這樣就可以做到很有效率的分頁功能了...Money EyesMoney EyesMoney EyesMoney EyesMoney EyesMoney Eyes..

參考資料:
http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
http://aspalliance.com/910_Efficient_Data_Paging_with_the_ASPNET_20_DataList_Control_and_ObjectDataSource.3

沒有留言: