Using Paging for huge amount of data (Need SQL and Function)

Question

Hi.......

I am using 3 tier architecture in ASP.NET. I have so many tables in my database and some table has 1000's of data. In earlier forums , i saw answers that use
paging for gridview instead of filling the full dataset. Can any one give me an idea on using this paging and can any one give me what would be the sql statement
on using paging.

Please give me detail information as am new to these architectures and methods.

Thanks and Regards
Sankar

Pradip Pandey · Answer

Hi Sankar, CREATE PROCEDURE [dbo] . [DisplayDataPaging] ( @PageNumber INT , @PageSize INT = '' ) AS SET NOCOUNT ON ; CREATE TABLE #Employee ( EmployeeID int , FullName Varchar ( 500 ) , Address1 Varchar ( max ) , City Varchar ( 50 ) , Country Varchar ( 100 ) , Age int , DOB DateTime , RowNumber Int IDENTITY ( 1 , 1 ) ) DECLARE @intTotal INT DECLARE @SQL varchar ( MAX ) SET @intTotal = @PageSize * @PageNumber Select COUNT ( EmployeeID ) As TotalRecord FROM Employee INSERT INTO #Employee SELECT EmployeeID ,( FirstName + ' ' + LastName ) As FullName , Address1 , City , Country , Age , DOB FROM Employee Select EmployeeID , FullName , Address1 , City , Country , Age , DOB , RowNumber From #Employee WHERE RowNumber between (( @PageNumber * @PageSize )-( @PageSize - 1 )) AND ( @PageNumber * @PageSize ) Declare @TotalRecords Integer Declare @TotalPage Integer SELECT @TotalRecords = MAX ( RowNumber ) from #Employee if ( @TotalRecords is not NULL) begin if ( @TotalRecords % @PageSize = 0 ) begin SET @TotalPage = @TotalRecords / @PageSize end else begin SET @TotalPage = @TotalRecords / @PageSize + 1 end end else begin set @TotalPage = 1 end Select @TotalPage [TotalPages] , @TotalRecords [TotalRecords] DROP Table #Employee Hope it will help you.

Pradip Pandey · Answer

Hi Sankar, Previously I gave idea only about the stored procedure, now you can refer this link for complete example http://www.c-sharpcorner.com/UploadFile/pandeypradip/sql-paging-in-Asp-Net-using-sql-server-Asp-Net-and-C-Sharp/ and hope it will help you.