[MSSQL] 分頁查詢SP

2011-04-15

3111
0
MSSQL
2011-05-11

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();

回首頁