doramail05
asked on
Stored procedure for custom paging in gridview
regarding the stored procedure from
http://www.tipsntracks.com/316/gridview-custom-paging-in-asp-net-3-5-with-sql-server-stored-procedure.html
where it gives the custom paging functionality for ASP.NET gridview,
notice that the SrNo which is the identity,
given that
SELECT sName, sAddress,ClassName FROM @tblStudentDtl WHERE SrNo > @start
and from
SET @start=@intPageSize*(@intC urrentPage - 1)
if we have pagesize = 10 , currentpage = 2,
it will have the equation of @start = 10 * (2 - 1)
which is @start = 10
with that,
SELECT sName, sAddress,ClassName FROM @tblStudentDtl WHERE SrNo >10
we will have been selecting (with pagesize = 10)
11, 12, 13, 14, 15, 16, 17, 18, 19, 20.
if for any reason we deleted records on ids
1,2, ... 20 (from 1 to 20)
and left 1001, 1002, 1003, 1004, 1005, 1006, -- 1020.
then with currentpage = 2
SELECT sName, sAddress,ClassName FROM @tblStudentDtl WHERE SrNo >10
it will select
1001, 1002, --- 1010.
as they are records from 1st page rather than selecting record from 2nd page.
looking for clarification , and if any suggestions are okay.
http://www.tipsntracks.com/316/gridview-custom-paging-in-asp-net-3-5-with-sql-server-stored-procedure.html
where it gives the custom paging functionality for ASP.NET gridview,
notice that the SrNo which is the identity,
given that
SELECT sName, sAddress,ClassName FROM @tblStudentDtl WHERE SrNo > @start
and from
SET @start=@intPageSize*(@intC
if we have pagesize = 10 , currentpage = 2,
it will have the equation of @start = 10 * (2 - 1)
which is @start = 10
with that,
SELECT sName, sAddress,ClassName FROM @tblStudentDtl WHERE SrNo >10
we will have been selecting (with pagesize = 10)
11, 12, 13, 14, 15, 16, 17, 18, 19, 20.
if for any reason we deleted records on ids
1,2, ... 20 (from 1 to 20)
and left 1001, 1002, 1003, 1004, 1005, 1006, -- 1020.
then with currentpage = 2
SELECT sName, sAddress,ClassName FROM @tblStudentDtl WHERE SrNo >10
it will select
1001, 1002, --- 1010.
as they are records from 1st page rather than selecting record from 2nd page.
looking for clarification , and if any suggestions are okay.
CREATE procedure sp_StudentDtl
(
@strSearchText varchar(100),
@intPageSize int,
@intCurrentPage int,
@intTotalRecords int output
)
AS
BEGIN
– Author: <Chetankumar Akarte>
– Description: <Return Records depending on @intPageSize and @intCurrentPage.>
DECLARE @tblStudentDtl TABLE(
SrNo int IDENTITY not null,
sName varchar(50) ,
sAddress varchar(100),
ClassName varchar(100)
)
DECLARE @start int
SET @start=@intPageSize*(@intCurrentPage - 1)
INSERT INTO @tblStudentDtl (sName,sAddress,ClassName)
SELECT sName, sAddress,ClassName FROM tblStudent A JOIN tblClass B ON A.ClassId=B.ClassId WHERE sName like @strSearchText +‘%’
SELECT @intTotalRecords=Count(*) FROM tblStudent A JOIN tblClass B ON A.ClassId=B.ClassId WHERE sName like @strSearchText +‘%’
SET ROWCOUNT @intPageSize
SELECT sName, sAddress,ClassName FROM @tblStudentDtl WHERE SrNo > @start
SET ROWCOUNT 0
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the Row_Number() returns the exact table row disregards of the identity being set rite?
another thing is, the tblClass is just a dummy table where there's a join table case rite?
JOIN tblClass B ON A.ClassId=B.ClassId
another thing is, the tblClass is just a dummy table where there's a join table case rite?
JOIN tblClass B ON A.ClassId=B.ClassId
You haven't really indicated where the fields
sName, sAddress,ClassName
come from. They could either come from A or B.
If A to B has a one-to-many or many-to-many relationship, the result of the join can be more than the number of unique A or B records (cartesian product). Therefore the count(*) and row_number() both need to work on the join between A and B.
sName, sAddress,ClassName
come from. They could either come from A or B.
If A to B has a one-to-many or many-to-many relationship, the result of the join can be more than the number of unique A or B records (cartesian product). Therefore the count(*) and row_number() both need to work on the join between A and B.
ASKER
can you elaborate this part :
SELECT sName, sAddress,ClassName
FROM
(
SELECT TOP (@intCurrentPage*@intPageS ize)
sName, sAddress,ClassName,
RN=ROW_NUMBER() over (order by SrNo)
FROM tblStudent A
JOIN tblClass B ON A.ClassId=B.ClassId
WHERE sName like @strSearchText +'%'
order by SrNo
) X
WHERE RN > (@intCurrentPage-1)*@intPa geSize
lets say current page is 2,
so it should be SELECT TOP (2 * 10) = 20
WHERE RN > (2 - 1) * 10
RN > 10
so that it will be selecting records on 2nd page (RN more than 10) disregards of what the sequential ids is, because we are using RN=ROW_NUMBER() over (order by SrNo) , which is getting the exact table row ?
SELECT sName, sAddress,ClassName
FROM
(
SELECT TOP (@intCurrentPage*@intPageS
sName, sAddress,ClassName,
RN=ROW_NUMBER() over (order by SrNo)
FROM tblStudent A
JOIN tblClass B ON A.ClassId=B.ClassId
WHERE sName like @strSearchText +'%'
order by SrNo
) X
WHERE RN > (@intCurrentPage-1)*@intPa
lets say current page is 2,
so it should be SELECT TOP (2 * 10) = 20
WHERE RN > (2 - 1) * 10
RN > 10
so that it will be selecting records on 2nd page (RN more than 10) disregards of what the sequential ids is, because we are using RN=ROW_NUMBER() over (order by SrNo) , which is getting the exact table row ?
Yes correct. It won't bother to number more than 2 pages worth (20 records), hence the TOP 20.
With those 20, it will only get RN>10. This also works for the last page if TOP 500 returns only 497, so RN > 490 will get only 7 records.
Yes row_number() produces a positional number without gaps, and does not rely on any id.
With those 20, it will only get RN>10. This also works for the last page if TOP 500 returns only 497, so RN > 490 will get only 7 records.
Yes row_number() produces a positional number without gaps, and does not rely on any id.
ASKER
o.o ~~ phew ok.
ASKER
back to that, that the SrNo hasn't been declare, pls advise
The COUNT(*) is interesting, because there is no way to avoid a full scan on the index on sName or (classId, sName) -- you better have one.
For the 2nd part, the TOP only produces ROW_NUMBER up to as far as required and never more, then the outer query returns only the required last page (in the range numbered)