以下資料來自於


用ObjectDataSource來處理分頁效率

---store procedure---


USE [資料庫名稱]
GO

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>

全站熱搜

kingjoy1235 發表在 痞客邦 留言(0) 人氣()