asp.net efficient paging method large amount of data and with query parameters

ASP tutorials. Net efficient paging method large data volume and with query parameters

Create Proc [dbo]. [Getrs]
@QueryStr nvarchar,--table name, view name, query statement
@PageSize int=10,--the size (number of rows) per page
@PageCurrent int=1,--the page to display
@FdShow nvarchar = ',--the list of fields to display, you need to specify this value if the query results have a literacy segment, and do not include an identity field
@FdOrder nvarchar = ',--sort field List
@WhereStr nvarchar (200) = ',-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)--An identity column name in a primary key or table in a table, in a temporary table
, @Id1 varchar, @Id2 varchar (20)--start and end record numbers
, @Obj_ID int--Object ID
, @Temp nvarchar (300)--temporary statement
, @strParam nvarchar (100)--Temporary parameters

declare @strfd nvarchar (2000)--Composite primary Key list
, @strjoin nvarchar (4000)--Connection field
, @strwhere nvarchar (2000)--Query criteria
--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 be not null then ' + @QueryStr Else ' (' + @QueryStr + ') A ' end
--Output Total record number
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, you can do it directly with top
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 to see if there is an identity or 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 there are no identity columns in the table, check the table for primary keys
Begin
If not exists (select 1 from sysobjects where parent_obj= @Obj_ID and xtype= ' PK ')
Goto lbusetemp--If there are no primary keys in the table, the temporary table handles
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,4000)
, @strwhere =substring (@strwhere, 5,4000)
Goto LBUSEPK
End
End
End
Else
Goto Lbusetemp
/*--use an identity column or primary key as a single field processing method--*/
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 processing method of compound primary key in/*--table--*/
LBUSEPK:
EXEC (' select ' + @FdShow + ' from (select top ' + @Id1 + ' a.* from
(select top percent * from ' + @QueryStr + @FdOrder + ') a
Left join (select top ' + @Id2 + ' + @strfd + ')
From ' + @QueryStr + @FdOrder + '] B on ' + @strjoin + '
where ' + @strwhere + ') a '
)
Return
The method of/*--with 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 + ' between '
+ @Id1 + ' and ' + @Id2
)

' Analysis of two

ALTER PROCEDURE Dbo.proc_listpage
(
@tblName nvarchar,----a connection to a table or tables to display
@fldName nvarchar = ' * ',----list of fields to display
@pageSize int = 1,----the number of records displayed per page
@page int = Ten,----to display the record for that page
@pageCount int = 1 OUTPUT,----Total number of pages after the query results are paginated
@Counts int = 1 OUTPUT,----number of records queried
@fldSort nvarchar = null,----sort field list or condition
@Sort bit = 0,----Sort method, 0 is ascending, 1 is descending (if a multiple-field arrangement sort refers to the order of the last sorted field (the last sort field is not ordered)--Program reference: ' Sorta ASC,SORTB desc,sortc '
@strCondition nvarchar (1000) = NULL,----query criteria, no where
@ID nvarchar,----primary key for the primary table
@Dist bit = 0----Whether to add a query field DISTINCT default 0 do not add/1 add
)
As
SET NOCOUNT on
Declare @sqlTmp nvarchar (1000)----store dynamically generated SQL statements
Declare @strTmp nvarchar (1000)----A query that holds the total number of query results
Declare @strID nvarchar (1000)----A query that holds the ID at the beginning or end of a query

Declare @strSortType nvarchar (a)----data collation a
Declare @strFSortType nvarchar (a)----data collation B

Declare @SqlSelect nvarchar----SQL construction of queries containing distinct
Declare @SqlCounts nvarchar----SQL construction of total 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 Statements--------
--Here @strtmp statements to get the number of query results
If @strCondition is null or @strCondition = '--no display condition 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

----The total number of results obtained from the query-----
EXEC sp_executesql @strTmp, N ' @Counts int out ', @Counts out
DECLARE @tmpCounts int
If @Counts = 0
Set @tmpCounts = 1
Else
Set @tmpCounts = @Counts

--Get Total paging
Set @pageCount = (@tmpCounts + @pageSize-1)/@pageSize

/**//** the current page is greater than the total number of pages to take the last page **/
If @page > @pageCount
Set @page = @pageCount

--/*-----Data Paging 2-------* *
DECLARE @pageIndex INT--Total/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 Page Paging
If @strCondition is null or @strCondition = '--no display condition set
Begin
If @pageIndex <2 or @page <= @pageIndex/2 + @pageIndex% 2--data processing in the first half
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) + "+ @ID + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strFSortType + ') '
+ ' ORDER BY ' + @fldSort + ' + @strFSortType
End
Else
Begin
Set @page = @pageIndex-@page +1--the latter half of data processing
If @page <= 1--last page data display
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) + "+ @ID + ' from ' + @tb LName
+ ' ORDER BY ' + @fldSort + ' + @strSortType + ') '

+ ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End

Else--There are query criteria
Begin
If @pageIndex <2 or @page <= @pageIndex/2 + @pageIndex% 2--data processing in the first half
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) + "+ @ID + ' from ' + @tblName
+ ' Where (1>0) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType + ') '
+ ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
Else
Begin
Set @page = @pageIndex-@page +1--the latter half of data processing
If @page <= 1--last page data display
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) + "+ @ID + ' from ' + @tb LName
+ ' WHERE (1>0) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') '
+ @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End

------Return Query Results-----
EXEC sp_executesql @strTmp
--print @strTmp
SET NOCOUNT off