一、SQL Server代码
declare @pageindex int = 1
declare @pagesize int = 3
?
select * from UserInfo order by uid
offset (@pageindex-1)*@pagesize rows fetch next @pagesize rows only
?
select count(*) from UserInfo
?
?
create proc P_Show
(
@pageindex int,
@pagesize int,
@uname nvarchar(20),
@totalcount int out,
@totalpage int out
)
as
begin
declare @sql nvarchar(max)=‘select * from UserInfo where 1=1‘
declare @sqlcount nvarchar(max)=‘select @totalcount = count(*) from UserInfo where 1=1‘
if(@uname!=‘‘)
begin
set @sql+=‘ and uname like ‘‘%‘ +@uname+ ‘%‘‘‘
set @sqlcount+=‘ and uname like ‘‘$‘ +@uname+ ‘%‘‘‘
end
?
set @sql+=‘order by uid offset (@pageindex-1) * @pagesize rows fetch next @pagesize rows only‘
?
exec sp_executesql @sql,N‘@pageindex int,@pagesize int‘,@pageindex,@pagesize
exec sp_executesql @sqlcount,N‘@totalcount int out‘,@totalcount out
?
set @totalpage=CEILING(@totalcount*1.0/@pagesize)
end
?
declare @tc int,@tp int
exec P_Show 1,3,‘‘,@tc out,@tp out
select @tc,@tp
二、Vs代码
-
DAl层--新建项--数据--ADO.NET实体数据模型--来自数据库的EF设计器--新建连接--Microsoft SQL Server--服务器名--选择或输入数据库名称--测试连接--确定--把表勾选--完成
-
在DAL层引用上下文
-
DAL层代码
public List<UserInfo> PageShow(out int totalcount,out int totalpage,string uname,int pageindex= 1,int pagesize= 3)
{
SqlParameter[] sqls =
{
new SqlParameter("@pageindex",pageindex),
new SqlParameter("@pagesize",pagesize),
new SqlParameter("@uname",uname==null?"":uname),
new SqlParameter("@totalcount",System.Data.SqlDbType.Int),
new SqlParameter("@totalpage",System.Data.SqlDbType.Int)
};
sqls[3].Direction = System.Data.ParameterDirection.Output;
sqls[4].Direction = System.Data.ParameterDirection.Output;
?
var list = db.Database.SqlQuery<UserInfo>("exec P_Show @pageindex,@pagesize,@uname,@totalcount out,@totalpage out", sqls).ToList();
?
totalcount = (int)sqls[3].Value;
totalpage = (int)sqls[3].Value;
?
return list;
}