Paging through strored procedure in sql server

This is very important for the most web developers. This stored procedure accept parameter of page index and page size. By using this paging, you don’t need to store everything in your viewstate which in the end will cause performance suffer. This will load up the necessary result based on the page index and page size and it uses row number to put the number in each row.

Datagrid needs to use custom paging = true as well as virtualitem count to produce the number for paging.

Datagrid in ASPX Page

Code Behind

//function to do the paging itself
  private void LoadContent(int pageIndex)
    {
        _presenter.GetContentSubmissionSelect(pendingStatusID, pageIndex, PAGE_SIZE);

        if (GetContentSubmissionSelect.Rows.Count > 0)
        {
            lblNoContent.Visible = false;

            dgTaskList.Visible = true;
            dgTaskList.AllowCustomPaging = true;
            dgTaskList.AllowPaging = true;
            dgTaskList.PageSize = PAGE_SIZE;
            _presenter.GetContentSubmissionCount(pendingStatusID);

            dgTaskList.VirtualItemCount = Convert.ToInt32(GetContentSubmissionCount.Rows[0][0]);
            dgTaskList.DataSource = GetContentSubmissionSelect;
            dgTaskList.CurrentPageIndex = pageIndex;
            dgTaskList.DataBind();
        }
        else
        {
            dgTaskList.Visible = false;
            lblNoContent.Visible = true;
        }
    }

//handle the page index changed
    protected void dgTaskList_PageIndexChanged(object sender, DataGridPageChangedEventArgs e)
    {
        LoadContent(e.NewPageIndex);
    }

SQL Stored Procedure
We need to have two query where one to get the number of records and the other one is to get the actual result it self
To get the query/actual result

CREATE PROCEDURE uspContentSubmissionSelect
(
	@statusID int = NULL,
	@PageIndex int,
	@PageSize int
)
AS
	SET NOCOUNT ON;
SELECT
	o.Id, o.ContentSubmission_id, o.AppointmentId,
	o.Title, o.StatusName
FROM
	(
		SELECT
			r.ID, r.contentSubmission_id, r.AppointmentId,
			r.Title, s.StatusName,
			ROW_NUMBER() OVER (ORDER BY r.ID DESC) as RowNumber
		FROM
			MyInternet.ContentSubmissionRef r
		INNER JOIN
			MyInternet.Status s
		ON
			s.Id = r.StatusID
		AND
			((@StatusID IS NULL) OR (s.ID = @statusID))
	) o
WHERE
	o.RowNumber BETWEEN (@PageIndex * @PageSize + 1) AND ((@PageIndex * @PageSize) + @PageSize)
ORDER BY RowNumber
GO

To get the record count

CREATE PROCEDURE [dbo].[uspContentSubmissionCount]
(
	@statusID int = NULL
)
AS
	SET NOCOUNT ON;

		SELECT
			COUNT(*) AS TotalRow
		FROM
			MyInternet.ContentSubmissionRef r
		INNER JOIN
			MyInternet.Status s
		ON
			s.ID = r.StatusID
		AND
			((@StatusID IS NULL) OR (s.ID = @statusID))