Asp.net efficient paging method with large data volume and query parameters

Asp.net efficient paging method with large data volume and query parameters

Asp tutorial. net efficient paging method large data volume with query parameters

Create Proc [dbo]. [GetRS]
@ QueryStr nvarchar (300), -- table name, view name, query statement
@ PageSize int = 10, -- size of each page (number of rows)
@ PageCurrent int = 1, -- the page to be displayed
@ FdShow nvarchar (100) = '', -- List of fields to be displayed. If the query result contains an ID field, you need to specify this value without the ID field.
@ FdOrder nvarchar (100) = '', -- Sort Field List
@ WhereStr nvarchar (200) = '', -- the content is 'id = 3 and model_no like '% 24%' and'
@ RSCount int = 0 output
As
Set @ FdShow = ''+ @ FdShow +''
Set @ FdOrder = ''+ @ FdOrder +''
Set @ WhereStr = ''+ @ WhereStr +''

Declare @ FdName nvarchar (250) -- primary key or column name in the table or temporary table
, @ Id1 varchar (20), @ Id2 varchar (20) -- start and end record numbers
, @ Obj_ID int -- Object ID
, @ Temp nvarchar (300) -- temporary statement
, @ StrParam nvarchar (100) -- temporary Parameter

Declare @ strfd nvarchar (2000) -- composite Primary Key List
, @ Strjoin nvarchar (4000) -- connection Field
, @ Strwhere nvarchar (2000) -- Query Condition
-- Check input parameters
Set @ QueryStr = ltrim (rtrim (@ QueryStr ))
Select @ Obj_ID = object_id (@ QueryStr)
, @ FdShow = case isnull (@ FdShow, '') when ''then'' * 'else' + @ FdShow end
, @ FdOrder = case isnull (@ FdOrder, '') when ''then'' else 'ORDER BY' + @ FdOrder end
, @ QueryStr = case when @ Obj_ID is not null then ''+ @ QueryStr else '(' + @ QueryStr + ') a' end
-- Total number of output records
SET @ Temp = 'select @ RSCount = count (*) FROM '+ @ QueryStr + ''+ @ WhereStr
SET @ strParam = n' @ RSCount INT out'
EXECUTE sp_executeSQL @ Temp, @ strParam, @ RSCount out
-- If the first page is displayed, top can be used directly.
If @ PageCurrent = 1
Begin
Select @ Id1 = cast (@ PageSize as varchar (20 ))
Exec ('select top '+ @ Id1 + @ FdShow + 'from' + @ QueryStr + @ WhereStr + @ FdOrder)
Return
End
-- If it is a table, check whether there is an identifier or a primary key in the table.
If @ Obj_ID is not null and objectproperty (@ Obj_ID, 'istable') = 1
Begin
Select @ Id1 = cast (@ PageSize as varchar (20 ))
, @ Id2 = cast (@ PageCurrent-1) * @ PageSize as varchar (20 ))
Select @ FdName = name from syscolumns where id = @ Obj_ID and status = 0x80
If @ rowcount = 0 -- if the table has no ID column, check whether the table has a primary key.
Begin
If not exists (select 1 from sysobjects where parent_obj = @ Obj_ID and xtype = 'pk ')
Goto lbusetemp -- if the table has no primary key, use a temporary table for processing.
Select @ FdName = name from syscolumns where id = @ Obj_ID and colid in (
Select colid from sysindexkeys where @ Obj_ID = id and indid in (
Select indid from sysindexes where @ Obj_ID = id and name in (
Select name from sysobjects where xtype = 'pk' and parent_obj = @ Obj_ID
)))

If @ rowcount> 1 -- check whether the primary key in the table is a composite primary key
Begin
Select @ strfd = '', @ strjoin ='', @ strwhere =''
Select @ strfd = @ strfd + ', [' + name + ']'
, @ Strjoin = @ strjoin + 'and a. [' + name + '] = B. [' + name + ']'
, @ Strwhere = @ strwhere + 'and B. [' + name + '] is null'
From syscolumns where id = @ Obj_ID and colid in (
Select colid from sysindexkeys where @ Obj_ID = id and indid in (
Select indid from sysindexes where @ Obj_ID = id and name in (
Select name from sysobjects where xtype = 'pk' and parent_obj = @ Obj_ID
)))
Select @ strfd = substring (@ strfd, 2,2000)
, @ Strjoin = substring (@ strjoin, 5)
, @ Strwhere = substring (@ strwhere, 5)
Goto lbusepk
End
End
End
Else
Goto lbusetemp
/* -- Use the identification column or primary key as a single field --*/
Lbuseidentity:
If len (@ WhereStr)> 10
Begin
Exec ('select top '+ @ Id1 + @ FdShow + 'from' + @ QueryStr
+ @ WhereStr + 'and' + @ FdName + 'not in (select top'
+ @ Id2 + ''+ @ FdName + 'from' + @ QueryStr + @ WhereStr + @ FdOrder
+ ')' + @ FdOrder
)
Return
End
Else
Begin
Exec ('select top '+ @ Id1 + @ FdShow + 'from' + @ QueryStr
+ 'Where' + @ FdName + 'not in (select top'
+ @ Id2 + ''+ @ FdName + 'from' + @ QueryStr + @ FdOrder
+ ')' + @ FdOrder
)
Return
End
/* -- The table has a composite primary key --*/
Lbusepk:
Exec ('select' + @ FdShow + 'from (select top' + @ Id1 + 'a. * from
(Select top 100 percent * from '+ @ QueryStr + @ FdOrder +')
Left join (select top '+ @ Id2 + ''+ @ strfd +'
From '+ @ QueryStr + @ FdOrder +') B on '+ @ strjoin +'
Where '+ @ strwhere +')'
)
Return
/* -- Use a temporary table --*/
Lbusetemp:
Select @ FdName = '[ID _' + cast (newid () as varchar (40) + ']'
, @ Id1 = cast (@ PageSize * (@ PageCurrent-1) as varchar (20 ))
, @ Id2 = cast (@ PageSize * @ PageCurrent-1 as varchar (20 ))
Exec ('select' + @ FdName + '= identity (int, 0, 1),' + @ FdShow +'
Into # tb from '+ @ QueryStr + @ FdOrder +'
Select '+ @ FdShow +' from # tb where '+ @ FdName + 'beten'
+ @ Id1 + 'and' + @ Id2
)

'Analysis 2

Alter procedure dbo. proc_ListPage
(
@ 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 = 1, ---- number of records displayed on each page
@ Page int = 10, ---- 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 -- first half Data Processing
Begin
Set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (4) + ''+ @ fldName + 'from' + @ tblName
+ 'Where' + @ ID + 'not in (' + @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-1) as Varchar (20 )) + ''+ @ ID + 'from' + @ tblName
+ 'ORDER BY' + @ fldSort + ''+ @ strFSortType + ')'
+ 'ORDER BY' + @ fldSort + ''+ @ strFSortType
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 + 'not in (' + @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-2) + @ lastcount as Varchar (20) + ''+ @ ID + 'from' + @ tblName
+ 'ORDER BY' + @ fldSort + ''+ @ strSortType + ')'

+ '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 -- first half Data Processing
Begin
Set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (4) + ''+ @ fldName + 'from' + @ tblName
+ 'Where' + @ ID + 'not in (' + @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-1) as Varchar (20 )) + ''+ @ ID + 'from' + @ tblName
+ 'Where (1> 0) '+ @ strCondition + 'ORDER BY' + @ fldSort + ''+ @ strFSortType + ')'
+ ''+ @ StrCondition + 'ORDER BY' + @ fldSort +'' + @ strFSortType
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> 0) '+ @ 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 + 'not in (' + @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-2) + @ lastcount as Varchar (20) + ''+ @ ID + 'from' + @ tblName
+ 'Where (1> 0) '+ @ strCondition + 'ORDER BY' + @ fldSort + ''+ @ strSortType + ')'
+ @ 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