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
Loading
Pradip PandeyPosted Aug 3, 2012, 9:04 AM
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 IntegerDeclare @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 PandeyPosted Aug 7, 2012, 12:21 AM
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.