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

Source: Internet
Author: User
Tags goto rowcount table name

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.