Implementing paging correctly is critically important. If you’re shop still uses, or insists on stored procedures, that’s fine. It’s just as easy to write a stored procedure for paging in SQL.
This example was developed on Microsoft SQL server 2016, although I believe it will work fine back to SQL 2012, not sure about SQL 2008.
As with any solution, the idea is to keep the data at rest and only pull out what you need to return. This example uses the AdventureWorks database.
This example has all of the typical parameters that I use on many paging implementations. You can trim out the ones not needed for your implementation.
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Created by Jack Yasgar, www.jackyasgar.net
CREATE PROCEDURE [dbo].[uspGetOrderDetailsYTGI]
@PageNumber INT,
@PageSize INT,
@SearchTerm VARCHAR(100) = NULL,
@SortDirection VARCHAR(4) = 'ASC',
@SortColumn VARCHAR(100) = 'lastname',
@Filter VARCHAR(50) = '',
@ActiveOnly BIT
AS
BEGIN
SET NOCOUNT ON;
IF ((@PageNumber = 0) OR (@PageNumber IS NULL))
BEGIN
SET @PageNumber = 1;
END
IF ((@PageSize = 0) OR (@PageSize IS NULL))
BEGIN
SET @PageSize = 25;
END
DECLARE @FirstRec INT, @LastRec INT;
DECLARE @OnlineOrder BIT;
SET @OnlineOrder = (SELECT CONVERT(BIT, @Filter));
SELECT @FirstRec = (@PageNumber - 1) * @PageSize
SELECT @LastRec = (@PageNumber * @PageSize + 1);
SELECT
sod.OrderQty,
sod.ProductID,
p.[Name] AS ProductName,
p.ProductNumber,
soh.ShipDate,
sod.UnitPrice,
soh.OnlineOrderFlag
FROM [Sales].[SalesOrderHeader] soh WITH (NOLOCK)
LEFT JOIN [Sales].[SalesOrderDetail] sod WITH (NOLOCK) ON soh.SalesOrderID = sod.SalesOrderID
LEFT JOIN [Production].[Product] p WITH (NOLOCK) ON p.ProductID = sod.ProductID
WHERE
(ISNULL(@SearchTerm, '') = '' OR @SearchTerm = '' OR p.[Name] LIKE '%' + @SearchTerm + '%')
AND
(ISNULL(@Filter, 0) = 0 OR @Filter = 0 OR soh.OnlineOrderFlag = @OnlineOrder)
AND
(ISNULL(@ActiveOnly, 0) = 0 OR @ActiveOnly = 0 OR soh.ShipDate > '2013-01-01')
ORDER BY
CASE WHEN @SortDirection = 'ASC' THEN
CASE @SortColumn
WHEN 'ProductName' THEN p.[Name]
WHEN 'ProductNumber' THEN p.ProductNumber
END
END,
CASE WHEN @SortDirection = 'DESC' THEN
CASE @SortColumn
WHEN 'ProductName' THEN p.[Name]
WHEN 'ProductNumber' THEN p.ProductNumber
END
END DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;
-- Return the full count for pager
SELECT COUNT(*) AS TotalCount FROM (
SELECT DISTINCT sod.SalesOrderDetailID
FROM [Sales].[SalesOrderHeader] soh WITH (NOLOCK)
LEFT JOIN [Sales].[SalesOrderDetail] sod WITH (NOLOCK) ON soh.SalesOrderID = sod.SalesOrderID
LEFT JOIN [Production].[Product] p WITH (NOLOCK) ON p.ProductID = sod.ProductID
WHERE
(ISNULL(@SearchTerm, '') = '' OR @SearchTerm = '' OR p.[Name] LIKE '%' + @SearchTerm + '%')
AND
(ISNULL(@Filter, 0) = 0 OR @Filter = 0 OR soh.OnlineOrderFlag = @OnlineOrder)
AND
(ISNULL(@ActiveOnly, 0) = 0 OR @ActiveOnly = 0 OR soh.ShipDate > '2013-01-01')
) AS Counting
END
This query uses the SQL OFFSET command to start where we need to based upon the PageNumber and PageSize values passed in as arguments. It then uses the FETCH command to retrieve a specific row. You can take a look at the documentation on FETCH here:
Once you receive back the DataSet in your Repository, you’ll need to stuff the data into a collection of DTO’s in your ServiceLayer for transfer through your web service: Stuffing DataSets into your objects.
