GridView查詢在資料來源不會自動做分頁,因此需要靠分頁語法或分頁SP來幫忙做分頁查詢,加上GridView的底層修改,可以每次只查到真正需要的資料,請參考下面分頁查詢SP範例:
GridView查詢在資料來源不會自動做分頁,因此需要靠分頁語法或分頁SP來幫忙做分頁查詢,加上GridView的底層修改,可以每次只查到真正需要的資料,請參考下面分頁查詢SP範例:
CREATE PROCEDURE [dbo].[usp_QueryByPager]
@PageIndex INT = 1,
@PageSize INT = 10,
@QuerySQL NVARCHAR(MAX) = NULL,
@OrderBy NVARCHAR(1000) = NULL,
@Paging CHAR(1) = 'Y',
@ReturnCount INT = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ExecSQL NVARCHAR(MAX),@startRowIndex INT, @maximumRows INT, @FromIndex INT;
SET @startRowIndex = @PageIndex * @PageSize;
SET @maximumRows = @PageSize;
IF @Paging='Y'
BEGIN
SET @ExecSQL = 'SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RankNumber
FROM (' + @QuerySQL + '
) as Main
) AS Result
WHERE RankNumber > ' + CONVERT(VARCHAR(10), @startRowIndex) + '
AND RankNumber <= ' + CONVERT(VARCHAR(10), @startRowIndex+@maximumRows)
+ ' ORDER BY RankNumber'
END
ELSE
BEGIN
SET @ExecSQL = @QuerySQL + 'ORDER BY ' + @OrderBy
END
exec(@ExecSQL)
--print @ExecSQL
--回傳資料總筆數
SET @ExecSQL = 'SELECT @ReturnCount=COUNT(1) FROM (' + @QuerySQL + ') A'
EXEC sp_executesql @ExecSQL,N'@ReturnCount int OUTPUT',@ReturnCount OUTPUT
END
ASP.NET執行SP範例:
DataTable dtData = new DataTable();
SqlParameter ReturnCount = new SqlParameter("@ReturnCount", SqlDbType.Int);
using (SqlConnection conn = new SqlConnection(Common.getDBConnectionString((uint)type)))
{
SqlCommand cmd = new SqlCommand("usp_QueryByPager", conn);
try
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@PageIndex", MasterGridView.PagerIndex));
cmd.Parameters.Add(new SqlParameter("@PageSize", MasterGridView.PageSize));
cmd.Parameters.Add(new SqlParameter("@QuerySQL", QuerySQL));
cmd.Parameters.Add(new SqlParameter("@OrderBy", OrderField));
cmd.Parameters.Add(new SqlParameter("@Paging", MasterGridView.AllowPaging == true ? "Y" : "N"));
ReturnCount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(ReturnCount);
dtData.Load(cmd.ExecuteReader());
}
catch (Exception ex)
{
throw;
}
}
//更新資料總筆數
MasterGridView.PagerDataCount = Convert.ToInt32(ReturnCount.Value);
//ReBind Grid
MasterGridView.DataSource = dtData;
MasterGridView.DataBind();