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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s