CODEWITHSUNDEEP
asp.netsql-server-2008
MindFresher
MindFresher

Reputation: 740

Set Limits in query for gridview pagination

I am using C# and SQL Server 2008 ,here i attach my query.i want to set limit for dynamic grid view . I need this query for paging on the dynamic grid view how can i 

SELECT 
    Item.ItemID,Item.TypeID,Item.StatusID,Item.SeverityID,Item.PriorityID,Item.ProjectID,Item.ReleaseID,
    Item.ClientID,Item.Body,Item.CreatedBy,Item.CreatedDate,Item.ModifiedBy,Item.ModifiedDate,Item.IsActive, 
item.[Subject], Type.Name as Type, status.Name as Status, Project.Name as Project,Release.Name as Release,
Priority.Name as Priority,Severity.Name as Severity,Client.Name as Client,
ROW_NUMBER() OVER (ORDER BY ItemID) AS Rowis

FROM Item (NOLOCK)  
LEFT OUTER JOIN  [Type] (NOLOCK)  ON Item.TypeID = [Type].TypeID
LEFT OUTER JOIN  [Status] (NOLOCK) ON Item.StatusID = [Status].StatusID
LEFT OUTER JOIN [Project] (NOLOCK) ON Item.ProjectID=[Project].ProjectID
LEFT OUTER JOIN [Release] (NOLOCK) ON Item.ReleaseID =[Release].ReleaseID 
LEFT OUTER JOIN [Priority] (NOLOCK) ON Item.PriorityID=[Priority].PriorityID 
LEFT OUTER JOIN [Severity] (NOLOCK) ON Item.SeverityID =[Severity].SeverityID
LEFT OUTER JOIN [Client] (NOLOCK) ON Item.ClientID =[Client].ClientID

Upvotes: 0

Views: 456

Answers (1)

Daniel PP Cabral
Daniel PP Cabral

Reputation: 1624

@PageIndex and @PageSize are parameters sent through from the application layer. 

DECLARE @PageIndex INT = 1
DECLARE @PageSize INT = 10

SELECT * FROM 
(
    SELECT 
        Item.ItemID,Item.TypeID,Item.StatusID,Item.SeverityID,Item.PriorityID,Item.ProjectID,Item.ReleaseID,
        Item.ClientID,Item.Body,Item.CreatedBy,Item.CreatedDate,Item.ModifiedBy,Item.ModifiedDate,Item.IsActive, 
    item.[Subject], Type.Name as Type, status.Name as Status, Project.Name as Project,Release.Name as Release,
    Priority.Name as Priority,Severity.Name as Severity,Client.Name as Client,
    ROW_NUMBER() OVER (ORDER BY ItemID) AS Rowis

    FROM Item (NOLOCK)  
    LEFT OUTER JOIN  [Type] (NOLOCK)  ON Item.TypeID = [Type].TypeID
    LEFT OUTER JOIN  [Status] (NOLOCK) ON Item.StatusID = [Status].StatusID
    LEFT OUTER JOIN [Project] (NOLOCK) ON Item.ProjectID=[Project].ProjectID
    LEFT OUTER JOIN [Release] (NOLOCK) ON Item.ReleaseID =[Release].ReleaseID 
    LEFT OUTER JOIN [Priority] (NOLOCK) ON Item.PriorityID=[Priority].PriorityID 
    LEFT OUTER JOIN [Severity] (NOLOCK) ON Item.SeverityID =[Severity].SeverityID
    LEFT OUTER JOIN [Client] (NOLOCK) ON Item.ClientID =[Client].ClientID 
)
AS Results
WHERE Rowis BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize --Paging

Upvotes: 1

Related Questions