Paging data query in SQL Server

1. Introduction

Today at work encountered a need to do SQL Server paging data query, but the paging data query SQL has forgotten, and finally through the query data to solve the problem. Now make a note of the solution and prepare it for review.

Here you need to thank the Qlin of the blog Park

2. Data paging statements

Suppose you need a query table with a field ID in the Test,test table (I'm using int), the current page pageindex=5, and the page size pagesize=10. The paging query statement is as follows:

SELECT *  from (  SELECTRow_number () Over(ORDER  byID) asROWID,*  fromTest) asTWHERET.rowidbetween(PageIndex-1)*PageSize+1  andPageIndex*PageSize;

3. C # implementation

     　　  /// <summary>        ///get a list of paged data/// </summary>        /// <param name= "Strfieldlist" >query Fields</param>        /// <param name= "strwhere" >Query Criteria</param>        /// <param name= "Strorderby" >Sort Fields</param>        /// <param name= "PageIndex" >Current Page</param>        /// <param name= "PageSize" >Page Size</param>        /// <returns></returns>         PublicDataSet Getlistbypage (stringStrfieldlist,stringStrwhere,stringStrorderby,intPageIndex,intpageSize) {StringBuilder strSQL=NewStringBuilder (); Strsql.append ("Select"); if(string. IsNullOrEmpty (strfieldlist))//Remove NULL{strfieldlist=Strfieldlist.trim (); if(string. IsNullOrEmpty (strfieldlist))//remove a string with only spaces{strfieldlist=" * "; }} strsql.append (" "+ Strfieldlist +" "); Strsql.append ("From (select Row_number () through (order by ID) as ROWID,"); Strsql.append (Strfieldlist+"From Test"); if(!string. IsNullOrEmpty (strwhere))//Remove NULL{strwhere=Strwhere.trim ().                ToLower (); if(!string. IsNullOrEmpty (strwhere))//remove a string with only spaces{strwhere= (Strwhere.startswith ("where")) ?" "+ strwhere:"where"+strwhere;                Strsql.append (strwhere); }} strsql.append (") as T"); if(!string. IsNullOrEmpty (strwhere))//No query criteria{strwhere+="and T.rowid between {0} and {1}"; }            Else{strwhere="where T.rowid between {0} and {1}"; } strwhere=string. Format (Strwhere, (PageIndex-1) * PageSize +1, PageIndex *pageSize);            Strsql.append (strwhere); if(!string. IsNullOrEmpty (Strorderby))//Remove NULL{Strorderby=Strorderby.trim ().                ToLower (); if(!string. IsNullOrEmpty (Strorderby))//remove a string with only spaces{Strorderby= (Strorderby.startswith ("Order by")) ?" "+ Strorderby:"Order by"+Strorderby;                Strsql.append (Strorderby); }            }            returnDbhelpersql.query (strsql.tostring ()); }

4. Original

SQL Server Data Paging

Paging data query in SQL Server