我发一个分页存储过程 CREATE PROCEDURE select_CutPage @PageRecord int , @currentPage int, @keyIdName varchar(200), @tableName varchar(500) AS Declare @stmt nvarchar(200), @ErrorSave int SET @ErrorSave =0 set @stmt=N'Select top '+Cast(@PageRecord as char)+ N' * from '+@tableName+N' where '+@keyIdName+' not in(select top '+ Cast(@PageRecord*@currentPage as char)+@keyIdName+ N' from '+@tableName+N' order by '+@keyIdName+N' asc)' exec sp_Executesql @stmt select @ErrorSave=@@ERROR Return @ErrorSave go
分页时应该是要显示哪些记录就从数据库中取哪几条出来.有1000页*10条记录,显示第2页就只取第11条~第20条.这里难点是存储过程:http://blog.csdn.net/yingcongxiao/archive/2006/03/08/618705.aspx--/////////////////////////////////////////////////////////////////////////////////////////////// if exists (select * from sysobjects where id = object_id('GetRecordPageTotal')and type = 'P') drop procedure GetRecordPageTotal go if exists (select * from sysobjects where id = object_id('PgetRecordByPage')and type = 'P') drop procedure PgetRecordByPage go --/////////////////////////////////////////////////////////////////////////////////////////////// create procedure GetRecordPageTotal @tblName varchar(100), -- 表名 -- @PageSize int, -- 页大小 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where) as begin declare @strSqlTemp varchar(2000) -- 临时变量
if @strWhere='' set @strSqlTemp='select count(*) from ['+@tblName+']' else set @strSqlTemp='select count(*) from ['+@tblName+'] where '+@strWhere
exec (@strSqlTemp) endGO--//////////////////////////////////////////////////////////////////////////////////////////////// create procedure GetRecordByPage @tblName varchar(100), -- 表名 @fldCow varchar(100)='*', -- 要查询的列 @fldName varchar(255), -- 排序列 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @OrderType bit = 1, -- 设置排序类型, 1则降序 @strWhere varchar(2000) = '' -- 查询条件 (注意: 不要加 where) ASdeclare @strSQL varchar(3000) -- 主语句 declare @strTmp varchar(1000) -- 临时变量 declare @strOrder varchar(500) -- 排序类型if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName + '] desc' end else begin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName + '] asc' endset @strSQL = 'select top ' + str(@PageSize) + ' '+@fldCow+' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrderif @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' '+@fldCow+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where (' + @strWhere + ') ' set @strSQL = 'select top ' + str(@PageSize) + ' '+@fldCow+ ' from [' + @tblName + ']' + @strTmp + ' ' + @strOrder endexec (@strSQL) GO 注:不想分页的话,把PageSize 值设到int最大值就行了
对表查询条件字段加索引。 核心sql语句是:如果有id主键,是自增的,pageno select top 10 from 表 where id > (select top '"+(pageno-1)*10+"' max(id) from 表 order by id) order by id 明白方法就好。
用DataGrid自带的分页效率肯定不行。
http://www.webdiyer.com/
CREATE PROCEDURE select_CutPage
@PageRecord int ,
@currentPage int,
@keyIdName varchar(200),
@tableName varchar(500)
AS
Declare @stmt nvarchar(200),
@ErrorSave int
SET @ErrorSave =0
set @stmt=N'Select top '+Cast(@PageRecord as char)+
N' * from '+@tableName+N' where '+@keyIdName+' not in(select top '+
Cast(@PageRecord*@currentPage as char)+@keyIdName+
N' from '+@tableName+N' order by '+@keyIdName+N' asc)'
exec sp_Executesql @stmt
select @ErrorSave=@@ERROR
Return @ErrorSave
go
if exists (select * from sysobjects where id = object_id('GetRecordPageTotal')and type = 'P')
drop procedure GetRecordPageTotal
go
if exists (select * from sysobjects where id = object_id('PgetRecordByPage')and type = 'P')
drop procedure PgetRecordByPage
go
--///////////////////////////////////////////////////////////////////////////////////////////////
create procedure GetRecordPageTotal
@tblName varchar(100), -- 表名
-- @PageSize int, -- 页大小
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
as begin
declare @strSqlTemp varchar(2000) -- 临时变量
if @strWhere=''
set @strSqlTemp='select count(*) from ['+@tblName+']'
else set @strSqlTemp='select count(*) from ['+@tblName+'] where '+@strWhere
exec (@strSqlTemp)
endGO--////////////////////////////////////////////////////////////////////////////////////////////////
create procedure GetRecordByPage
@tblName varchar(100), -- 表名
@fldCow varchar(100)='*', -- 要查询的列
@fldName varchar(255), -- 排序列
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 1, -- 设置排序类型, 1则降序
@strWhere varchar(2000) = '' -- 查询条件 (注意: 不要加 where)
ASdeclare @strSQL varchar(3000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName + '] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName + '] asc'
endset @strSQL = 'select top ' + str(@PageSize) + ' '+@fldCow+' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrderif @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' '+@fldCow+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ') ' set @strSQL = 'select top ' + str(@PageSize) + ' '+@fldCow+ ' from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
endexec (@strSQL)
GO
注:不想分页的话,把PageSize 值设到int最大值就行了
我試過一次200條記錄他給我用了3秒,我沒嚇死
核心sql语句是:如果有id主键,是自增的,pageno
select top 10 from 表 where id > (select top '"+(pageno-1)*10+"' max(id) from 表 order by id) order by id
明白方法就好。