Comment faire de la pagination dans SQL Server 2008

Comment faites-vous de la pagination dans SQL Server 2008?

Vous pouvez essayer quelque chose comme

DECLARE @Table TABLE( Val VARCHAR(50) ) DECLARE @PageSize INT, @Page INT SELECT @PageSize = 10, @Page = 2 ;WITH PageNumbers AS( SELECT Val, ROW_NUMBER() OVER(ORDER BY Val) ID FROM @Table ) SELECT * FROM PageNumbers WHERE ID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) 

Vous pouvez utiliser ROW_NUMBER () :

Renvoie le numéro séquentiel d’une ligne dans une partition d’un jeu de résultats, en commençant à 1 pour la première ligne de chaque partition.

Exemple:

 WITH CTEResults AS ( SELECT IDColumn, SomeField, DateField, ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum FROM MyTable ) SELECT * FROM CTEResults WHERE RowNum BETWEEN 10 AND 20; 

SQL Server 2012 fournit des fonctionnalités de pagination (voir http://www.codeproject.com/Articles/442503/New-features-for-database-developers-in-SQL-Server )

En SQL2008, vous pouvez le faire de cette façon:

 declare @rowsPerPage as bigint; declare @pageNum as bigint; set @rowsPerPage=25; set @pageNum=10; With SQLPaging As ( Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc) as resultNum, * FROM Employee ) select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage) 

Prooven! Cela fonctionne et évolue régulièrement.

1) CRÉER DES DONNÉES DOUBLES

 CREATE TABLE #employee (EMPID INT IDENTITY, NAME VARCHAR(20)) DECLARE @id INT = 1 WHILE @id < 200 BEGIN INSERT INTO #employee ( NAME ) VALUES ('employee_' + CAST(@id AS VARCHAR) ) SET @id = @id + 1 END 

2) APPLIQUEZ MAINTENANT LA SOLUTION.

Ce cas suppose que EMPID est une colonne unique et sortingée.

Hors-cours, vous allez l'appliquer une colonne différente ...

 DECLARE @pageSize INT = 20 SELECT * FROM ( SELECT *, PageNumber = CEILING(CAST(EMPID AS FLOAT)/@pageSize) FROM #employee ) MyQuery WHERE MyQuery.PageNumber = 1 

Ce sont mes solutions pour rechercher le résultat de la requête côté serveur SQL. J’ai ajouté le concept de filtrage et de classement avec une colonne. Il est très efficace lorsque vous paginez, filtrez et commandez dans votre Gridview.

Avant de tester, vous devez créer un exemple de table et insérer une ligne dans cette table: (Dans le monde réel, vous devez changer la clause Where en considérant votre champ de table et peut-être que vous avez une jointure et une sous-requête dans la partie principale de select)

 Create Table VLT ( ID int IDentity(1,1), Name nvarchar(50), Tel Varchar(20) ) GO Insert INTO VLT VALUES ('NAME' + Convert(varchar(10),@@identity),'FAMIL' + Convert(varchar(10),@@identity)) GO 500000 

Dans SQL Server 2008, vous pouvez utiliser le concept CTE. Pour cette raison, j’ai écrit deux types de requêtes pour SQL Server 2008+

– SQL Server 2008+

 DECLARE @PageNumber Int = 1200 DECLARE @PageSize INT = 200 DECLARE @SortByField int = 1 --The field used for sort by DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/) DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information. SELECT Data.ID, Data.Name, Data.Tel FROM ( SELECT ROW_NUMBER() OVER( ORDER BY CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC' THEN VLT.ID END ASC, CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC' THEN VLT.ID END DESC, CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC' THEN VLT.Name END ASC, CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC' THEN VLT.Name END ASC, CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC' THEN VLT.Tel END ASC, CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC' THEN VLT.Tel END ASC ) AS RowNum ,* FROM VLT WHERE ( -- We apply the filter logic here CASE WHEN @FilterType = 'None' THEN 1 -- Name column filter WHEN @FilterType = 'Contain' AND @FilterColumn = 1 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.ID LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'NotContain' AND @FilterColumn = 1 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.ID NOT LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'Match' AND @FilterColumn = 1 AND VLT.ID = @FilterValue THEN 1 WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1 AND VLT.ID <> @FilterValue THEN 1 -- Name column filter WHEN @FilterType = 'Contain' AND @FilterColumn = 2 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.Name LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'NotContain' AND @FilterColumn = 2 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.Name NOT LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'Match' AND @FilterColumn = 2 AND VLT.Name = @FilterValue THEN 1 WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2 AND VLT.Name <> @FilterValue THEN 1 -- Tel column filter WHEN @FilterType = 'Contain' AND @FilterColumn = 3 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.Tel LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'NotContain' AND @FilterColumn = 3 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.Tel NOT LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'Match' AND @FilterColumn = 3 AND VLT.Tel = @FilterValue THEN 1 WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3 AND VLT.Tel <> @FilterValue THEN 1 END ) = 1 ) AS Data WHERE Data.RowNum > @PageSize * (@PageNumber - 1) AND Data.RowNum <= @PageSize * @PageNumber ORDER BY Data.RowNum GO 

Et seconde solution avec CTE dans SQL Server 2008+

 DECLARE @PageNumber Int = 1200 DECLARE @PageSize INT = 200 DECLARE @SortByField int = 1 --The field used for sort by DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/) DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information. ;WITH Data_CTE AS ( SELECT ROW_NUMBER() OVER( ORDER BY CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC' THEN VLT.ID END ASC, CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC' THEN VLT.ID END DESC, CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC' THEN VLT.Name END ASC, CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC' THEN VLT.Name END ASC, CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC' THEN VLT.Tel END ASC, CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC' THEN VLT.Tel END ASC ) AS RowNum ,* FROM VLT WHERE ( -- We apply the filter logic here CASE WHEN @FilterType = 'None' THEN 1 -- Name column filter WHEN @FilterType = 'Contain' AND @FilterColumn = 1 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.ID LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'NotContain' AND @FilterColumn = 1 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.ID NOT LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'Match' AND @FilterColumn = 1 AND VLT.ID = @FilterValue THEN 1 WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1 AND VLT.ID <> @FilterValue THEN 1 -- Name column filter WHEN @FilterType = 'Contain' AND @FilterColumn = 2 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.Name LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'NotContain' AND @FilterColumn = 2 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.Name NOT LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'Match' AND @FilterColumn = 2 AND VLT.Name = @FilterValue THEN 1 WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2 AND VLT.Name <> @FilterValue THEN 1 -- Tel column filter WHEN @FilterType = 'Contain' AND @FilterColumn = 3 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.Tel LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'NotContain' AND @FilterColumn = 3 AND ( -- In this case, when the filter value is empty, we want to show everything. VLT.Tel NOT LIKE '%' + @FilterValue + '%' OR @FilterValue = '' ) THEN 1 WHEN @FilterType = 'Match' AND @FilterColumn = 3 AND VLT.Tel = @FilterValue THEN 1 WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3 AND VLT.Tel <> @FilterValue THEN 1 END ) = 1 ) SELECT Data.ID, Data.Name, Data.Tel FROM Data_CTE AS Data WHERE Data.RowNum > @PageSize * (@PageNumber - 1) AND Data.RowNum <= @PageSize * @PageNumber ORDER BY Data.RowNum 

Une autre solution qui fonctionne à partir de SQL 2005, au moins, consiste à utiliser TOP avec les sous-requêtes SELECT et les clauses ORDER BY.

En résumé, récupérer les lignes de la page 2 avec 10 lignes par page revient à récupérer les 10 dernières lignes des 20 premières lignes. Ce qui se traduit par la récupération des 20 premières lignes avec l’ordre ASC, puis par les 10 premières lignes avec l’ordre DESC, avant de commander à nouveau avec ASC.

Exemple: Récupération des lignes de la page 2 avec 3 lignes par page

 create table test(id integer); insert into test values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); select * from ( select top 2 * from ( select top (4) * from test order by id asc) tmp1 order by id desc) tmp1 order by id asc 

SELECT ID DISTINCT, ParticipantId, ActivityDate, IsApproved, IsDeclined, IsDeleted, SubmissionDate, IsResubmitted,

  [CategoryId] Id,[CategoryName] Name, [ActivityId] [Id],[ActivityName] Name,Points, [UserId] [Id],Email, ROW_NUMBER() OVER(ORDER BY Id desc) AS RowNum from (SELECT DISTINCT Id,ParticipantId, ActivityDate,IsApproved, IsDeclined,IsDeleted, SubmissionDate, IsResubmitted, [CategoryId] [CategoryId],[CategoryName] [CategoryName], [ActivityId] [ActivityId],[ActivityName] [ActivityName],Points, [UserId] [UserId],Email, ROW_NUMBER() OVER(ORDER BY Id desc) AS RowNum from (SELECT DISTINCT ASN.Id, ASN.ParticipantId,ASN.ActivityDate, ASN.IsApproved,ASN.IsDeclined, ASN.IsDeleted,ASN.SubmissionDate, CASE WHEN (SELECT COUNT(*) FROM FDS_ActivitySubmission WHERE ParentId=ASN.Id)>0 THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END IsResubmitted, AC.Id [CategoryId], AC.Name [CategoryName], A.Id [ActivityId],A.Name [ActivityName],A.Points, U.Id[UserId],U.Email FROM FDS_ActivitySubmission ASN WITH (NOLOCK) INNER JOIN FDS_ActivityCategory AC WITH (NOLOCK) ON AC.Id=ASN.ActivityCategoryId INNER JOIN FDS_ApproverDetails FDSA ON FDSA.ParticipantID=ASN.ParticipantID INNER JOIN FDS_ActivityJobRole FAJ 

ON FAJ.RoleId = FDSA.JobRoleId INNER JOIN

  FDS_Activity A WITH (NOLOCK) ON A.Id=ASN.ActivityId INNER JOIN Users U WITH (NOLOCK) ON ASN.ParticipantId=FDSA.ParticipantID WHERE IsDeclined=@IsDeclined AND IsApproved=@IsApproved AND ASN.IsDeleted=0 AND ISNULL(U.Id,0)=ISNULL(@ApproverId,0) AND ISNULL(ASN.IsDeleted,0)<>1)P)t where t.RowNum between (((@PageNumber - 1) * @PageSize) + 1) AND (@PageNumber * PageSize) AND t.IsDeclined=@IsDeclined AND t.IsApproved=@IsApproved AND t.IsDeleted = 0 AND (ISNULL(t.Id,0)=ISNULL(@SubmissionId,0)or ISNULL(@SubmissionId,0)<=0)