Paging With SQL Server Stored Procedures In ASP.NET
Data paging is very useful when you work with large amount of data. Instead of confusing your user with thousands or maybe even millions of records you
simply show only first page with 10, 20 or 30 records and enable navigation buttons like Next or Previous if user wants to see other pages. Standard ASP.NET server controls, like GridView have built in paging capabilities in two modes: simple or custom.
Simple paging is easy to implement but it always bind complete records set which is not so scalable solution and can take a lot of network traffic and work very slow if you have large table or your web site is on shared hosting or especially if you have a lot of concurrent visitors. More about how to implement simple or custom paging in GridView and ListView you can read in Data Paging in ASP.NET tutorial.
But, which ever server control you use for data presentation, to get efficient and scalable data paging you need to do all your paging logic on SQL Server's side and SQL Server should return only selected page to ASP.NET web application. With logic like this, you get faster solution, need less memory and avoid too much traffic between SQL Server and web server. On SQL Server side, you can do paging on two main ways:
1. By building a dynamic SQL query
2. By using a stored procedure
Both methods are used widely, to find out how to build dynamic SQL queries for paging check SQL Queries For Paging In ASP.NET. In this tutorial we will see some common solution when paging is done with stored procedure.
Paging stored procedure with three nested queries
You can get selected page by using three nested SQL queries. For example to get third page from Customers table where Country column is 'Spain' with 10 records per page and ordered by CompanyName, sql query will be:
SELECT * FROM
(SELECT TOP 10 * FROM
(SELECT TOP 30 * FROM Customers WHERE Country = 'Spain' AS T1
ORDER BY CompanyName ASC)
AS T2 ORDER BY CompanyName DESC)
T3 ORDER BY CompanyName ASC
I made simple stored procedure based on this idea:
CREATE
PROCEDURE getSelectedPage
@TableOrView
nvarchar (50),
@SelectedPage
int,
@PageSize
int,
@Columns
nvarchar(500),
@OrderByColumn
nvarchar(100),
@OrderByDirection
nvarchar(4),
@WhereClause
nvarchar(500)
AS
DECLARE
@ReturnedRecords int,
@SqlQuery nvarchar(1000),
@ConOrderByDirection nvarchar(4)
IF
Upper(@OrderByDirection)
= 'ASC'
BEGIN
SET @ConOrderByDirection
= 'DESC'
END
ELSE
BEGIN
SET @ConOrderByDirection
= 'ASC'
END
IF
@WhereClause <> ''
BEGIN
SET @WhereClause =
' WHERE ' + @WhereClause
END
SET
@ReturnedRecords = (@PageSize
* @SelectedPage)
SET
NOCOUNT ON
SET
@SqlQuery = N'SELECT *
FROM
(SELECT TOP '
+ CAST(@PageSize
as varchar(10))
+ ' * FROM
(SELECT TOP '
+ CAST(@ReturnedRecords
as varchar(10))
+ ' '
+ @Columns +
' FROM ' +
@TableOrView + @WhereClause
+ '
ORDER BY '
+ @OrderByColumn +
' ' +
@OrderByDirection + ')
AS T1
ORDER BY '
+ @OrderByColumn +
' ' +
@ConOrderByDirection +
') AS T2
ORDER
BY '
+ @OrderByColumn +
' ' +
@OrderByDirection
EXEC(@SqlQuery)
SET
NOCOUNT OFF
GO
So, to use it with previous example you need this much simpler line:
EXEC getSelectedPage 'Customers', 3, 10, '*', 'CompanyName', 'ASC', 'Country = ''Spain'' '
Stored procedure for paging by using temporary table
Basically, we create one temporary table with one identity column of type int which will be used as row counter. After that, we extract only wanted rows by filtering rows that belong to selected page.
CREATE
PROCEDURE getPageWithTempTable
@TableOrViewName
varchar(50),
@Columns
varchar(500),
@IdentityColumn
varchar(50),
@SortColumn
varchar(50),
@SortDirection
varchar(4),
@SelectedPage
int,
@PageSize
int,
@WhereClause
varchar(500)
AS
SET
NOCOUNT ON
DECLARE
@SQLQuery varchar(5000),
@StartRecord int,
@EndRecord int
--
Create temporary table
CREATE
TABLE #TempTable (
RowNumber
int IDENTITY
(1, 1),
row_id
int )
-- Find
first record on selected page
SET
@StartRecord = (@SelectedPage
- 1)
* @PageSize + 1
-- Find
last record on selected page
SET
@EndRecord = @SelectedPage
* @PageSize
-- Check
if there is WHERE clause
IF
@WhereClause <> ''
BEGIN
SET @WhereClause =
' WHERE ' +
@WhereClause
END
-- Build
INSERT statement used to populate temporary table
SET
@SQLQuery = 'INSERT
#TempTable (row_id) ' +
' SELECT
TOP '
+ CAST(@EndRecord
AS varchar(20))
+ ' '
+
@IdentityColumn
+ ' FROM '
+ @TableOrViewName +
' ' +
@WhereClause
+ ' ORDER BY '
+ @SortColumn +
' ' +
@SortDirection
--
Execute statement and populate temp table
EXEC
(@SQLQuery)
-- Build
SQL query to return only selected page
SET
@SQLQuery = N'SELECT
RowNumber, ' + @Columns
+
' FROM
#TempTable tmp JOIN '
+ @TableOrViewName +
' ON
row_id = '
+ @TableOrViewName +
'.' +
@IdentityColumn +
' WHERE
RowNumber >= '
+ CAST(@StartRecord
AS varchar(20))
+
' AND
RowNumber <= '
+ CAST(@EndRecord
AS varchar(20))
+
' ORDER BY
RowNumber '
--
Return selected page
EXEC
(@SQLQuery)
--
Delete temporary table
DROP
TABLE #TempTable
SET
NOCOUNT OFF
GO
So, to return data like in previous example we'll use:
EXEC getPageWithTempTable 'Customers', '*', 'CustomerID', 'CompanyName', 'ASC', 2, 10, 'Country = ''Spain'' '
Stored procedure for paging with ROW_NUMBER() function
SQL Server 2005 introduced new ROW_NUMBER() function that makes paging task easier. To achieve paging like in previous examples, get third page from Customers table where Country column is 'Spain' with 10 records per page and ordered by CompanyName stored procedure will look like this:
CREATE
PROCEDURE Paging_Customers
(
@SelectedPage
int,
@PageSize
int
)
AS
BEGIN
WITH CTE_Customers(PageNumber, ContactTitle, ContactName, CompanyName, Phone, Country)
AS
(
SELECT CEILING((ROW_NUMBER()
OVER
(ORDER
BY CompanyName ASC
AS PageNumber,
ContactTitle, ContactName, CompanyName, Phone, Country
FROM Customers
)
SELECT *
FROM
CTE_Customers WHERE PageNumber
= @SelectedPage
END
Then, we call this procedure (for third page and ten rows per page) with this simple line:
EXEC Paging_Customers 3, 10
As you can see, this is more hard coded solution but it is more optimized and faster. The reason why we could not use the same logic in previous two stored procedures is that SQL Server doesn't allow using of variables in TOP clause. You can't write something like SELECT TOP @PageSize * FROM TableName because you'll get syntax error. Instead of that, on SQL Server 2000 you need to build SQL query as a string and then execute it by using EXEC keyword. The first two examples with TOP keyword will work on any version of SQL Server, but last solution with ROW_NUMBER() function is usually better solution if you use SQL Server 2005.
Conclusion
It is very important to pay attention to every input from your users because it could be potentially dangerous. It is recommended to use SQL parameters when calling stored procedure to avoid possible SQL injection attacks. Hardest task is usually building of complex WHERE clauses in case that you need to enable not only paging but also database search to your users. Because of that, we developed Search Control as specialized solution to make this task easy. Search Control creates simple or complex WHERE clauses by only changing control's properties, supports different database SQL syntaxes, take care about security issues and more.
Please let me know if you have some other interesting idea for paging with stored procedure. Happy programming!
|
