I need to do pagination

Question

0.00/5 (No votes)

See more:

I tried but i need to view it in gridview with 100 records and next 100 records like this kindly help me

What I have tried:

MY code below shows

Declare @start int,@limit int = 100, @mod int, @pagecount int,@pagesub int

    set @start =(select count(sno) from tbl_Purchase_Stock )
	
	set @endproductcount=@start
	
    set @mod = @start % @limit

    if(@mod = 0)
    Begin
        set @pagesub = @start/@limit
        set @pagecount = @pagesub
    end
    else
    Begin
        set @pagesub = @start/@limit
        set @pagecount = @pagesub+1
    end

	select * from (SELECT DISTINCT P.sno AS sno,ROW_NUMBER() Over (ORDER BY P.sno asc) as rownum,P.code AS code, P.name AS name, P.descrip AS descrip, P.color AS color, P.size AS size, P.type AS type, P.qty AS qty, P.unitrt AS unitrt, (P.qty*P.unitrt) AS totalur, P.wholert AS wholert,P.qty*P.wholert AS Totalwholert,P.name+' -'+P.code AS C_name,L.Low_stock_Shop1  AS LOW FROM tbl_Purchase_Stock AS P,tbl_Low_stk_Entry_details AS L WHERE P.code=L.code 
)as tbl where rownum between @endproductcount-(@limit-1) AND @endproductcount

Posted 8-Jan-19 18:12pm

Member 14071109

Updated 8-Jan-19 21:03pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Answer 1 · 2019-01-08T19:19:00

Solution 1

SQL

DECLARE @PageIndex int=1; --firstPage
DECLARE @PageSize int=100; --100 records/page
DECLARE @cnt int;
--Create Declarative table(if huge records #temp is best) same as selected columns
DECLARE @Temp
   TABLE(sno int,RowNumber int,wholert int,Totalwholert float,C_name Varchar(50),LOW float);
--INSERT all records into @Temp
INSERT INTO @Temp 
SELECT DISTINCT P.sno AS sno
          ,ROW_NUMBER() Over (ORDER BY P.sno asc) as RowNumber
          ,P.wholert AS wholert 
          ,P.qty*P.wholert AS Totalwholert
		  ,P.name+' -'+P.code AS C_name
		  ,L.Low_stock_Shop1  AS LOW 
   FROM tbl_Purchase_Stock AS P INNER JOIN tbl_Low_stk_Entry_details AS L 
 ON P.code=L.code 


 select @cnt=COUNT(1) FROM @Temp;

 SELECT * FROM @Temp
    WHERE  
	RowNumber BETWEEN (@PageIndex -1) * @PageSize + 1 AND 
	                 (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1;

select @cnt AS TotalRecords;

Posted 8-Jan-19 19:19pm

Santosh kumar Pithani

Comments

CHill60 9-Jan-19 4:21am

Wouldn't using a CTE be more efficient than using a temporary table? e.g.

;with cte as
(
	SELECT DISTINCT P.sno AS sno
		,ROW_NUMBER() Over (ORDER BY P.sno asc) as RowNumber
		,P.wholert AS wholert ,P.qty*P.wholert AS Totalwholert
		,P.name+' -'+P.code AS C_name,L.Low_stock_Shop1  AS LOW 
	FROM tbl_Purchase_Stock AS P 
	INNER JOIN tbl_Low_stk_Entry_details AS L ON P.code=L.code 
)
SELECT * FROM cte
WHERE RowNumber BETWEEN (@PageIndex -1) * @PageSize + 1 AND 
	                 (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1;

Santosh kumar Pithani 9-Jan-19 4:59am

I have create table for showing total records as well as count of records in table so
temporary table is used instead of CTE.We know that CTE supports only single scope execution.

CHill60 9-Jan-19 7:57am

Fair enough - except you aren't using @cnt</pre> for anything and as you have no <code>where clause you could have calculated @cnt from the original table. There is no need for a temporary table (or table variable)

Santosh kumar Pithani 9-Jan-19 8:26am

I accepts your suggestion but many cases joins(multiple tables) and group by commands(complicated queries) used for paging and @cnt its a performance issue compared to Temporary tables.

RickZeeland · Answer 2 · 2019-01-08T21:03:00

Solution 2

You might also be interested in Virtual Mode, see: c# - implementing virtual mode for a datagridview that is databound - Stack Overflow[^]

Here's another approach with a UserControl: DataGridView With Paging (UserControl)[^]
Note: for large databases the stored procedure method is the preferred way.

Posted 8-Jan-19 21:03pm

RickZeeland

Updated 8-Jan-19 21:10pm

v2