Basically, we do not need to change the
GridView
/HTML table/etc. where we display the data to create an effective paging.
What we need is only to change the SQL script to get a certain record and add some data paging controls on the UI.
For example, a paging has 10 rows in a page and we want to show the second page (record number 11-20). The SQL script will be:
DECLARE @fromDate DATETIME
DECLARE @toDate DATETIME
DECLARE @currentPage INT
DECLARE @pageSize INT
DECLARE @startRowNumber INT
DECLARE @endRowNumber INT
SET @fromDate = '2011-01-01'
SET @toDate = '2011-12-31'
SET @currentPage = 2
SET @pageSize = 10
SET @startRowNumber = (@currentPage - 1) * @pageSize + 1
SET @endRowNumber = @currentPage * @pageSize
SELECT tmp.TotalRecords, tmp.TransDate, tmp.TransDesc, tmp.Amount
FROM
(
SELECT
COUNT(1) OVER() AS TotalRecords,
ROW_NUMBER() OVER(ORDER BY TransDate DESC) AS RowNumber,
TransDate, TransDesc, Amount
FROM MainTrans WITH(NOLOCK)
WHERE TransDate BETWEEN @fromDate AND @toDate
) tmp
WHERE tmp.RowNumber BETWEEN @startRowNumber AND @endRowNumber
The example above will return 10 records, which are records number 11-20 based on
TransDate
descending order (assume table has 20 or more records).
Column
TotalRecords
returns total number of records available between
@fromDate
and
@toDate
, which can be used to calculate the total pages (
TotalPages = TotalRecords / PageSize
).
ROW_NUMBER() OVER(ORDER BY <Column Name> DESC)
The above syntax will return row number for each row with ordering according to
ORDER BY
syntax.
COUNT(1) OVER()
The above syntax will return total records selected, which
TransDate
between
@fromDate
and
@toDate
.
Robby Tendean had his Master Degree in Computer Science from National Taiwan University of Science and Technology. His master degree thesis, Energy-Efficient Routing Protocol for Wireless Sensor Networks with Static Clustering and Dynamic Structure, has been published in Springerlink International Journal.
Currently he is working as Software Engineer based in Singapore with several years experience in HTML, Javascript, JQuery, C#.NET, VB.NET, Microsoft SQL Server for web development.