Jquery-EasyUI学习2~

最新推荐文章于 2018-12-28 11:53:45 发布

原创最新推荐文章于 2018-12-28 11:53:45 发布 · 294 阅读

0 ·

CC 4.0 BY-SA版权

本博客没有绝对权威，仅作参考。具体结果以实践为准~

本文介绍了一个使用MVC和Ado.Net结合存储过程实现的分页查询功能，支持按ID精确查询、按名称模糊查询及两者组合查询，并展示了具体的存储过程代码。

下面这个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!=0 and (@selName='')
begin
select *from(
select *,rn=ROW_NUMBER() over(order by UserId) from UserMsg where IsDelete=1 and( UserId=@selId)
)as t where t.rn between ((@pageIndex-1)*@pageSize+1) and @pageIndex*@pageSize
set @recordCount=(select count(*) from (select *from UserMsg where IsDelete=1 and( UserId=@selId))as t);
set @pageCount=CEILING((@recordCount*1.0)/@pageSize)
end
else if @selId=0 and (@selName!='')
begin
select *from(
select *,rn=ROW_NUMBER() over(order by UserId) from UserMsg where IsDelete=1 and( UserName like '%'+@selName+'%')
)as t where t.rn between ((@pageIndex-1)*@pageSize+1) and @pageIndex*@pageSize
set @recordCount=(select count(*) from (select * from UserMsg where IsDelete=1 and( UserName like '%'+@selName+'%'))as t);
set @pageCount=CEILING((@recordCount*1.0)/@pageSize)
end
else if @selId!=0 and(@selName!='')
begin
select *from(
select *,rn=ROW_NUMBER() over(order by UserId) from UserMsg where IsDelete=1 and(UserId=@selId or(UserName like '%'+@selName+'%'))
)as t where t.rn between ((@pageIndex-1)*@pageSize+1) and @pageIndex*@pageSize
set @recordCount=(select count(*) from (select *from UserMsg where IsDelete=1 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=1)as t where t.rn between ((@pageIndex-1)*@pageSize+1) and @pageIndex*@pageSize
set @recordCount=(select count(*) from UserMsg);
set @pageCount=CEILING((@recordCount*1.0)/@pageSize)
end
end
go