相关存储过程：
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE procedure [dbo].[sp_PageChange]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT,
@strGetFields nvarchar(1000),
@tableName nvarchar(500) ,
@ID nvarchar(100),
@strWhere nvarchar(1000) ='',
@sortName nvarchar(50) =' asc ' ,
@orderName nvarchar(100)
AS
declare @countSelect nvarchar(2000)
--设置统计查询语句
if len(@strWhere) =0
--如果没有查询条件
begin
set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName
end
else
--否则
begin
set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName+' where '+@strWhere
end
--执行并返回总数
exec sp_executesql @countSelect,N'@CountRecord int output',@RecordCount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
SET NOCOUNT ON
DECLARE @SQLSTR NVARCHAR(3000)
--实际总共的页码小于当前页码 或者 最大页码
if @PageCount>=0
--如果分页后页数大于0
begin
if @PageCount<=@PageIndex and @PageCount>0 --如果实际总共的页数小于datagrid索引的页数
--or @PageCount=1
begin
--设置为最后一页
set @PageIndex=@PageCount-1
end
else if @PageCount<=@PageIndex and @PageCount=0
begin
set @PageIndex=0;
end
end
--如果用普通的sql而不使用存储过程调用
declare @ID_temp varchar(100)
set @ID_temp='cast('+@ID+' as nvarchar(100)) '
declare @returnValue nvarchar(100)
set @returnValue=','''+cast(@RecordCount as nvarchar(100)) +'|'+cast(@PageCount as nvarchar(100))+'|'+'''+'+@ID_temp+' as [returnValue] '
--如果用普通的sql而不使用存储过程调用
IF @PageIndex = 0 OR @PageCount <= 1 --如果为第一页
begin
if len(@strWhere) =0
begin
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName
end
else
begin
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName
end
end
ELSE IF @PageIndex = @PageCount - 1 --如果为最后一页
begin
if len(@strWhere) =0
begin
SET @SQLSTR =N' SELECT '+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName
end
else
begin
SET @SQLSTR =N' SELECT '+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' ) and '+@strWhere+' ORDER BY '+@orderName+@sortName
end
end
ELSE --否则执行
begin
if len(@strWhere) =0
begin
SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR( /*@RecordCount - */@PageSize * @PageIndex )+' '+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName
end
else
begin
SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+@returnValue+' FROM '+@tableName+' where '+@ID+' not in (SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' )and '+@strWhere+'ORDER BY '+@orderName+@sortName
end
end
EXEC (@SQLSTR)
set nocount off
GO
CREATE PROCEDURE sp_Get_FS
AS
BEGIN
execute sp_PageChange 0,12,0,0,'*','[SXGGW]','id','yesno=1',' addtime desc',''
execute sp_PageChange 1,12,0,0,'*','[SXGGW]','id','yesno=1',' addtime desc',''
execute sp_PageChange 2,12,0,0,'*','[SXGGW]','id','yesno=1',' addtime desc',''
execute sp_PageChange 3,12,0,0,'*','[SXGGW]','id','yesno=1',' addtime desc',''
END
GO
页面代码：
SqlDataAdapter adpt = new SqlDataAdapter("sp_Get_FS", ConfigurationManager.AppSettings["ConnectionString"]);
DataSet ds = new DataSet();
adpt.Fill(ds, "talbe");
dl_list1.DataSource = ds.Tables[0];
dl_list1.DataBind();
dl_list2.DataSource = ds.Tables[1];
dl_list2.DataBind();
dl_list3.DataSource = ds.Tables[2];
dl_list3.DataBind();
dl_list4.DataSource = ds.Tables[3];
dl_list4.DataBind();