下面这个Demo用的是MVC+Ado.Net、存储过程
实现功能：分页查询，添加、修改功能。模糊查询功能
先来几张效果图:
创建存储过程如下
go
create proc usp_FenYe2
@selId int,
@selName nvarchar,
@pageIndex int,
@pageSize int,
@recordCount int output,
@pageCount int output
as
begin
if @selId!= and (@selName='')
begin
select *from(
select *,rn=ROW_NUMBER() over(order by UserId) from UserMsg where IsDelete= and( UserId=@selId)
)as t where t.rn between ((@pageIndex-)*@pageSize+) and @pageIndex*@pageSize
set @recordCount=(select count(*) from (select *from UserMsg where IsDelete= and( UserId=@selId))as t);
set @pageCount=CEILING((@recordCount*1.0)/@pageSize)
end
else if @selId= and (@selName!='')
begin
select *from(
select *,rn=ROW_NUMBER() over(order by UserId) from UserMsg where IsDelete= and( UserName like '%'+@selName+'%')
)as t where t.rn between ((@pageIndex-)*@pageSize+) and @pageIndex*@pageSize
set @recordCount=(select count(*) from (select * from UserMsg where IsDelete= and( UserName like '%'+@selName+'%'))as t);
set @pageCount=CEILING((@recordCount*1.0)/@pageSize)
end
else if @selId!= and(@selName!='')
begin
select *from(
select *,rn=ROW_NUMBER() over(order by UserId) from UserMsg where IsDelete= and(UserId=@selId or(UserName like '%'+@selName+'%'))
)as t where t.rn between ((@pageIndex-)*@pageSize+) and @pageIndex*@pageSize
set @recordCount=(select count(*) from (select *from UserMsg where IsDelete= and(UserId=@selId or (UserName like '%'+@selName+'%')))as t);
set @pageCount=CEILING((@recordCount*1.0)/@pageSize)
end
else
begin
select *from(
select *,rn=ROW_NUMBER() over(order by UserId) from UserMsg where IsDelete=)as t where t.rn between ((@pageIndex-)*@pageSize+) and @pageIndex*@pageSize
set @recordCount=(select count(*) from UserMsg);
set @pageCount=CEILING((@recordCount*1.0)/@pageSize)
end
end
go