2 Method-General stored procedure paging (topmax mode) version _ MySQL

-- * ----- Stored procedure paging processing Sun Wei creation ------- * -- * ----- perform two-point processing on the data so that the performance of the first half of the data query is the same as that of the second half of the data query -------*--*----- stored procedure paging processing Sun Wei modify and add Distinct query function ------- * -- * ----- stored procedure paging processing Sun Wei --/* ----- stored procedure paging processing Sun Wei create -------*/
--/* ----- The data is processed in two minutes so that the performance of the first half of the data to be queried is the same as that of the second half of the data to be queried -------*/
--/* ----- Stored procedure paging processing Sun Wei modify and add the Distinct query function -------*/
--/* ----- Stored procedure paging processing Sun Wei multi-field sorting rule modification question -------*/
--/* ----- Stored procedure paging processing Sun Wei modify multi-field sorting modification -------*/
--/* ----- Stored procedure paging processing Sun Wei 2005-12-13 modifying the data paging mode to top max mode greatly improves performance -------*/
--/* ----- Disadvantage: compared with the previous not in version, the primary key can only be an integer field. if the primary key is of the GUID type, use the not in mode version -------*/
Create procedure dbo. proc_ListPageInt
(
@ TblName nvarchar (200), ---- the table to be displayed or the join of multiple tables
@ FldName nvarchar (500) = '*', ---- list of fields to be displayed
@ PageSize int = 10, ---- number of records displayed on each page
@ Page int = 1, ---- the record of the page to be displayed
@ PageCount int = 1 output, ---- Total number of pages after the query result is paginated
@ Counts int = 1 output, ---- number of records queried
@ FldSort nvarchar (200) = null, ---- list of sorting fields or conditions
@ Sort bit = 0, ---- sorting method. 0 indicates ascending order, 1 is in descending order (for multi-field sorting, Sort refers to the sorting order of the last sorting field (the last sorting field is not marked with a sorting mark)-The program transmits the parameter as follows: 'sorta Asc, sortB Desc, SortC ')
@ StrCondition nvarchar (1000) = null, ---- query condition, where is not required
@ ID nvarchar (150), ---- primary key of the master table
@ Dist bit = 0 ---- whether to add the DISTINCT of the query field. by default, 0 is not added./1 is added.
)
AS
SET NOCOUNT ON
Declare @ sqlTmp nvarchar (1000) ---- stores dynamically generated SQL statements
Declare @ strTmp nvarchar (1000) ---- stores the query statement that obtains the total number of query results
Declare @ strID nvarchar (1000) ---- stores the query statement for obtaining the start or end ID of a query.

Declare @ strSortType nvarchar (10) ---- data sorting rule
Declare @ strFSortType nvarchar (10) ---- data sorting rule B

Declare @ SqlSelect nvarchar (50) ---- SQL construction for queries containing DISTINCT
Declare @ SqlCounts nvarchar (50) ---- SQL structure of the total number of queries containing DISTINCT

If @ Dist = 0
Begin
Set @ SqlSelect = 'select'
Set @ SqlCounts = 'count (*)'
End
Else
Begin
Set @ SqlSelect = 'SELECT distinct'
Set @ SqlCounts = 'count (DISTINCT '@ ID ')'
End

If @ Sort = 0
Begin
Set @ strFSortType = 'asc'
Set @ strSortType = 'desc'
End
Else
Begin
Set @ strFSortType = 'desc'
Set @ strSortType = 'asc'
End

-------- Generate query statement --------
-- Here @ strTmp is the statement for obtaining the number of query results
If @ strCondition is null or @ strCondition = ''-- no display conditions are set.
Begin
Set @ sqlTmp = @ fldName 'from' @ tblName
Set @ strTmp = @ SqlSelect '@ Counts =' @ SqlCounts 'FROM' @ tblName
Set @ strID = 'from' @ tblName
End
Else
Begin
Set @ sqlTmp = @ fldName 'From' @ tblname' where (1> 0) '@ strCondition
Set @ strTmp = @ SqlSelect '@ Counts =' @ SqlCounts 'FROM' @ tblname' where (1> 0) '@ strCondition
Set @ strID = 'from' @ tblname' where (1> 0) '@ strCondition
End

---- Total number of retrieved query results -----
Exec sp_executesql @ strTmp, n' @ Counts int out', @ Counts out
Declare @ tmpCounts int
If @ Counts = 0
Set @ tmpCounts = 1
Else
Set @ tmpCounts = @ Counts

-- Retrieve the total number of pages
Set @ pageCount = (@ tmpCounts @ pageSize-1)/@ pageSize

/** // ** Select the last page if the current page is greater than the total page number **/
If @ page> @ pageCount
Set @ page = @ pageCount

--/* ----- Two-point data processing by page -------*/
Declare @ pageIndex int -- Total number/page size
Declare @ lastcount int -- Total % page size

Set @ pageIndex = @ tmpCounts/@ pageSize
Set @ lastcount = @ tmpCounts % @ pageSize
If @ lastcount> 0
Set @ pageIndex = @ pageIndex 1
Else
Set @ lastcount = @ pagesize

-- // *** Display the page
If @ strCondition is null or @ strCondition = ''-- no display conditions are set.
Begin
If @ pageIndex <2 or @ page <= @ pageIndex/2 @ pageIndex % 2 -- Data processing of the first half
Begin
If @ page = 1
Set @ strTmp = @ SqlSelect 'top' CAST (@ pageSize as VARCHAR (4) ''@ fldName 'from' @ tblName
'Order by' @ fldSort ''@ strFSortType
Else
Begin
Set @ strTmp = @ SqlSelect 'top' CAST (@ pageSize as VARCHAR (4) ''@ fldName 'from' @ tblName
'Where' @ ID' <(select min ('@ ID') from (' @ sqlselect' top 'cast (@ pageSize * (@ page-1) as Varchar (20) ''@ ID' from '@ tblName
'Order by' @ fldSort ''@ strFSortType ') AS TBMinID )'
'Order by' @ fldSort ''@ strFSortType
End
End
Else
Begin
Set @ page = @ pageIndex-@ page 1 -- data processing in the second half
If @ page <= 1 -- display the last page of data
Set @ strTmp = @ SqlSelect '* from (' @ SqlSelect 'top' CAST (@ lastcount as VARCHAR (4) '@ fldName' from '@ tblName
'Order by' @ fldSort ''@ strSortType ') AS temptb' 'Order by' @ fldSort'' @ strFSortType
Else
Set @ strTmp = @ SqlSelect '* from (' @ SqlSelect 'top' CAST (@ pageSize as VARCHAR (4) '@ fldName' from '@ tblName
'Where' @ ID'> (select max ('@ ID') from (' @ sqlselect' top 'cast (@ pageSize * (@ page-2) @ lastcount as Varchar (20) ''@ ID' from '@ tblName
'Order by' @ fldSort ''@ strSortType ') AS TBMaxID )'
'Order by' @ fldSort ''@ strSortType ') AS temptb' 'Order by' @ fldSort'' @ strFSortType
End
End

Else -- with query conditions
Begin
If @ pageIndex <2 or @ page <= @ pageIndex/2 @ pageIndex % 2 -- Data processing of the first half
Begin
If @ page = 1
Set @ strTmp = @ SqlSelect 'top' CAST (@ pageSize as VARCHAR (4) ''@ fldName 'from' @ tblName
'Where 1 = 1' @ strCondition 'Order by' @ fldSort ''@ strFSortType
Else
Begin
Set @ strTmp = @ SqlSelect 'top' CAST (@ pageSize as VARCHAR (4) ''@ fldName 'from' @ tblName
'Where' @ ID' <(select min ('@ ID') from (' @ sqlselect' top 'cast (@ pageSize * (@ page-1) as Varchar (20) ''@ ID' from '@ tblName
'Where (1 = 1) '@ strCondition 'Order by' @ fldSort ''@ strFSortType') AS TBMinID )'
''@ StrCondition 'Order by' @ fldSort'' @ strFSortType
End
End
Else
Begin
Set @ page = @ pageIndex-@ page 1 -- data processing in the second half
If @ page <= 1 -- display the last page of data
Set @ strTmp = @ SqlSelect '* from (' @ SqlSelect 'top' CAST (@ lastcount as VARCHAR (4) '@ fldName' from '@ tblName
'Where (1 = 1) '@ strCondition 'Order by' @ fldSort ''@ strSortType') AS temptb' 'Order by' @ fldSort'' @ strFSortType
Else
Set @ strTmp = @ SqlSelect '* from (' @ SqlSelect 'top' CAST (@ pageSize as VARCHAR (4) '@ fldName' from '@ tblName
'Where' @ ID'> (select max ('@ ID') from (' @ sqlselect' top 'cast (@ pageSize * (@ page-2) @ lastcount as Varchar (20) ''@ ID' from '@ tblName
'Where (1 = 1) '@ strCondition 'Order by' @ fldSort ''@ strSortType') AS TBMaxID )'
''@ StrCondition 'Order by' @ fldSort'' @ strSortType ') AS TempTB ''Order by' @ fldSort ''@ strFSortType
End
End

------ Return query result -----
Exec sp_executesql @ strTmp
-- Print @ strTmp
SET NOCOUNT OFF
GO
Call method column:
/**////

/// General paging data reading function
/// Note: open and close the connection and close the data reader outside the function call.
///
/// SqlCommand object
/// Table/table Union queried
/// Field name to query
/// Data size per page
/// Current page
/// Sorting field
/// Sorting order 0 descending order 1 ascending order
/// Filter condition
/// Primary key of the primary table
/// Returned SqlDataReader ref
Public static void CutPageData (SqlConnection conn, ref SqlCommand comm, string _ tblName, string _ fldName, int _ pageSize, int _ page, string _ fldSort, int _ Sort, string _ strCondition, string _ ID, ref SqlDataReader _ dr)
{
// Note: open and close the connection and close the data reader outside the function call.
// Comm = new SqlCommand ("proc_ListPage", conn );
// Comm. CommandType = CommandType. StoredProcedure;
Comm. Parameters. Add ("@ tblName", SqlDbType. NVarChar, 200 );
Comm. Parameters ["@ tblName"]. Value = _ tblName;
Comm. Parameters. Add ("@ fldName", SqlDbType. NVarChar, 500 );
Comm. Parameters ["@ fldName"]. Value = _ fldName;
Comm. Parameters. Add ("@ pageSize", SqlDbType. Int );
Comm. Parameters ["@ pageSize"]. Value = _ pageSize;
Comm. Parameters. Add ("@ page", SqlDbType. Int );
Comm. Parameters ["@ page"]. Value = _ page;
Comm. Parameters. Add ("@ fldSort", SqlDbType. NVarChar, 200 );
Comm. Parameters ["@ fldSort"]. Value = _ fldSort;
Comm. Parameters. Add ("@ Sort", SqlDbType. Bit );
Comm. Parameters ["@ Sort"]. Value = _ Sort;
Comm. Parameters. Add ("@ strCondition", SqlDbType. NVarChar, 1000 );
Comm. Parameters ["@ strCondition"]. Value = _ strCondition;
Comm. Parameters. Add ("@ ID", SqlDbType. NVarChar, 150 );
Comm. Parameters ["@ ID"]. Value = _ ID;
Comm. Parameters. Add ("@ Counts", SqlDbType. Int, 0 );
Comm. Parameters ["@ Counts"]. Direction = ParameterDirection. Output;
Comm. Parameters. Add ("@ pageCount", SqlDbType. Int, 0 );
Comm. Parameters ["@ pageCount"]. Direction = ParameterDirection. Output;

_ Dr = comm. ExecuteReader ();
}
Call example:
CutPageData (conn, ref comm, "VOX_CDSinger", "id, cdsinger, cdsingertype, area, cdsingerreadme", 15, page, "id", 1, strFilter, "id ", ref dr );
Description:
CutPageData (data connection object, ref Sqlcommand object, "table or View name required", "field to be queried", number of data entries read per page, current page, "The sorting field can contain multiple fields, such as (addtime desc, visitcounts). Note that the last field does not contain desc or the last field of asc corresponds to the subsequent sorting rule )", sorting method (1 desc 0 asc), where condition (where condition is not added here, for example, 'and visitcounts> 100'), Table primary key, SqlDataReader object returned by ref );

The call here also applies to the previous not in version.

Merry Christmas!