调用存储过程分页

go
create proc r_students
(
    @StudentName  varchar(20),--学生姓名  模糊查询
    @ClassId      int,       --班级ID  精确查询
    @PageSize     int ,      --页面大小    每页放几条记录
    @PageIndex    int,       --页码        第几页
    @RecordCount  int output,--输出参数    记录总数  总条数
    @PageCount    int output --输出参数    总页数
)
as
begin
    declare @where varchar(max)
    set @where=' where 1=1 '
    if(@StudentName!='')
    begin
        set @where=@where+' and StudentName like''%'+@StudentName+'%'''
    end
    if(@ClassId!=-1)
    begin
        set @where=@where+' and ClassId = '+str(@ClassId)
    end
    select @RecordCount=count(1) from TbStudent where StudentName like '%'+@StudentName+'%' and (@ClassId=-1 or ClassId=@ClassId)
    set @PageCount=CEILING(@RecordCount*1.0/@pagesize)
        declare @start int,@end int
    set @start=(@PageIndex-1)*@PageSize+1
    set @end=@PageIndex*@PageSize
    declare @sql varchar(max)
    set @sql='select * from 
    (select ROW_NUMBER() over(order by StudentName) Hid, a.ClassName,b.* from TbClass a join TbStudent b on a.Id=b.ClassId '+@where+' ) 
    temp where Hid>='+STR(@start)+' and Hid<='+str(@end)+''
    exec(@sql)
end
declare @eq int,@ec int
exec r_students '王',-1,5,1,@eq output,@ec output
select @eq,@ec

存储过程分页，通过api调用存储过程进行分页，操作过程在dal中实现，通过一个新类来存储数据库中调用过来的内容

调用后创建一个分页类来接收值，方便后续分页，之间的传值，

再使用一个新类的集合字段来接收这个集合传来的值

其中注意SQLparameter实例化要实例化为数组，同时给多个参数赋值，最后传入前台就可以分页了

public PageList show(int pagesize,string name="",int pageindex=1,int tid=-1)
        {
            SqlParameter[] sq = new SqlParameter[]
            {
                 new SqlParameter{ ParameterName="@StudentName",DbType=DbType.String,Value=name},
                 new SqlParameter{ ParameterName="@ClassId",DbType=DbType.Int32,Value=tid},
                 new SqlParameter{ ParameterName="@PageSize",DbType=DbType.Int32,Value=pagesize},
                 new SqlParameter{ ParameterName="@PageIndex",DbType=DbType.Int32,Value=pageindex},
                 new SqlParameter{ ParameterName="@RecordCount",DbType=DbType.Int32,Direction=ParameterDirection.Output},
                 new SqlParameter{ ParameterName="@PageCount",DbType=DbType.Int32,Direction=ParameterDirection.Output},
            };
            List<Students> list = db.Database.SqlQuery<Students>("r_students @StudentName,@ClassId,@PageSize,@PageIndex,@RecordCount output,@PageCount output",sq).ToList();
            PageList p = new PageList();
            p.Rcount = Convert.ToInt32(sq[4].Value);
            p.Pcount= Convert.ToInt32(sq[5].Value);
            p.Plist = list;
            return p;
        }