Click here to Skip to main content
15,122,559 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I have a gridview in my aspx page and i want to implement custom paging to display record.
how to build query to fatch data from database according to page size and page index.

In my database table studentRecord with id(identity (1,1)), studentName,RollNo,fathername,Class,MobileNo,Address

i want to bind all these info into gridview
Posted
Updated 8-May-13 2:47am
v3
Comments
CHill60 8-May-13 7:42am
   
We don't know your database schema so can't help you to build a query without further information. Try using the Improve question link to post details (code) of what you've tried so far and what problems you are experiencing
Sukanta Dey 8-May-13 8:10am
   
The pagination stored procedure code is given below:

   
Comments
ashok luhach 9-May-13 23:44pm
   
thanks sir for these link
_Amy 9-May-13 23:45pm
   
Welcome. :)
You have to pass pagesize and page number that you want in store procedure

and modify this query according to you

SQL
CREATE PROC dbo.GetData( @PageSize int,@PageNumber int) as
begin
select * From
(
select RANK() over(order by ID) as RO_ID, * From dbo.StudentRecord
)a  where a.RO_ID between @PageSize*(@PageNumber-1) and @PageSize*(@PageNumber-1)+@PageSize
end
   
v2
SQL
CREATE PROCEDURE dbo.usp_sel_AnyList
	@GroupID INT
	, @StartDate DATETIME
	, @EndDate DATETIME
	, @ZoneID INT
	, @ShowArchived BIT = 0
	, @DeliveryStatus INT = 0
	, @SortColumn VARCHAR(25) = ''
	, @SortOrder VARCHAR(4) = 'asc'
	, @StartIndex INT = 0
	, @Count INT
AS

BEGIN

	SET NOCOUNT ON

	DECLARE @EndIndex INT
	SET @EndIndex = @StartIndex + @Count - 1

	--If sort order is neither asc/desc set it to asc
	IF(@SortOrder <> 'desc')
	SET @SortOrder = 'asc'

	DECLARE @SortPattern VARCHAR(30)

	SET @SortOrder = LOWER(@SortOrder)
	SET @SortColumn = LOWER(@SortColumn)

	IF ( @SortColumn = '' )
	SET @SortColumn = 'default'

	SET @SortPattern = @SortColumn + '-' + @SortOrder

	SELECT DISTINCT * FROM (
		SELECT
				TotalRows = COUNT(*) OVER()
				, RowNumber = ROW_NUMBER() OVER
				( ORDER BY SiteId					 )
					, SiteId
					, SiteName
					, AdIDString
					, AdName
					, AdFileName
					, Text1
					, StartDate
					, EndDate
					, Days
					, AccountID
					, StatusID
					, DownloadDate
					, ZoneName
					, LoginID
					, SourceUrl
			FROM (
				SELECT
					o.SiteId
					, o.OrgName as SiteName
					, AdIDString = CAST(a.AdID AS VARCHAR(36))
					, a.Name AS AdName
					, af.FileName AS AdFileName
					, Text1 = ISNULL(CONVERT(VARCHAR(max),a.Text1),'')
					, a.StartDate
					, a.EndDate
					, a.Days
					, AccountID = CAST(a.AccountId AS VARCHAR(36))
					, mf.StatusID
					, DownloadDate = smf.TranDate
					, z.ZoneName
					, act.LoginID
					, mf.SourceUrl
				FROM dbo.Ads a (NOLOCK)
					JOIN (
						SELECT 
							AdID
							, ManifestFileID , FileName
						FROM dbo.AdFiles (NOLOCK)
						GROUP BY 
							AdID
							, ManifestFileID , FileName
						) af
						ON a.AdID = af.AdID
					JOIN Programming.dbo.SiteAds b (NOLOCK) ON a.AdID = b.AdId
					JOIN dbGamePlay..tbOrg o (NOLOCK) ON o.SiteId = b.SiteId 
					JOIN GameManagement..GroupSites gs (NOLOCK) ON gs.SiteID = b.SiteID AND gs.GroupID= @GroupID
					JOIN Accounts act (NOLOCK) ON a.AccountID = act.AccountID
					LEFT JOIN Programming.dbo.AdBackgrounds ab (NOLOCK) ON ab.AdBackgroundID = a.AdBackgroundID
					LEFT JOIN Programming.dbo.ManifestFile mf (NOLOCK) ON mf.ManifestFileID = af.ManifestFileID 
					LEFT JOIN Programming.dbo.SiteManifestFile smf (NOLOCK) ON smf.ManifestFileID = af.ManifestFileID 
						AND smf.SiteID = b.SiteID
					LEFT JOIN Programming.dbo.Zone z (NOLOCK)ON a.ZoneID = z.ZoneID
				WHERE (@StartDate <= a.EndDate OR EndDate IS NULL)
					AND (@EndDate >= StartDate OR StartDate IS NULL)
					AND (@ZoneID = 0 OR a.ZoneID = @ZoneID)
					AND ((@ShowArchived = 0 AND (a.EndDate >= GETDATE() OR a.EndDate IS NULL)) OR (@ShowArchived = 1))
					AND ( @DeliveryStatus = 0 ) 
						OR 
						( @DeliveryStatus = 1 AND smf.TranDate IS NOT NULL )
						 OR 
						( @DeliveryStatus = 2 AND smf.TranDate IS NULL )
				) adFiles
		) t
	WHERE @startIndex = -1
	OR RowNumber BETWEEN @startIndex AND @EndIndex
	ORDER BY RowNumber
END
   
v2
Comments
ashok luhach 10-May-13 2:19am
   
Sorry sir because of procedure length i can't understand it. can you tell me in some few row

i have three table from which i need to fetch data and bind in gridview with custom pagging my table schema like tblStudent (id identity(1,1) pk,name nvachar(100) mobileNo char(10)) second is tblMessage (id identity(1,1) pk, message nvarchar(1000)), third tblMsgHistory (id identity(1,1) pk, stuId int references tblStudent(id),msgId int references tblMessage (id), msgdate smalldatetime, deleverysatatus bool)

i want to fetch data these three table using join with my @pageindex and @pagesize condition

please tell me how i can implement my procedure

i am using sqlserver 2008 and asp.net 4.0 with c#

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900