MVC调用存储过程实现分页,带查询条件

创建需要显示到界面的视图

create view VW_AllotRoom --创建视图
as
    select distinct a.RC_ID,a.RC_Count,a.RC_Name,a.RC_MoneyPerMonth,b.R_ID,b.R_MaleOrFemale,b.R_IsHasFull,b.R_Name,c.F_MaleOrFemale,c.F_ID,c.F_Name,c.F_Remark,d.H_ID,d.H_MaleOrFemale,d.H_Name,d.H_Remark,e.TC_ID,e.Student_IsHasAllotRoom,f.[count] from RoomCategories a 
                    join Rooms b on a.RC_ID=b.RC_ID 
                    join Floors c on b.F_ID=c.F_ID 
                    join Houses d on d.H_ID=c.H_ID 
                    left join (select TC_ID,R_ID,Student_IsHasAllotRoom from TrainClassStudents) e on e.R_ID=b.R_ID 
                    left join (select R_ID, count(Student_ID) [count] from TrainClassStudents group by R_ID) f on f.R_ID=b.R_ID
go

创建存储过程

--创建一个既带输入参数,又带输出参数的存储过程
alter procedure P_GetPagedUserInfoByCondition
    @PageSize int,--表示每页要显示的记录数
    @CurrentPageIndex int,--表示当前要显示第几页的数据
    @RecordCount int output,--表示满足条件的记录总数
    @RC_Name varchar(20),
    @R_IsHasFull int,
    @R_MaleOrFemale int
as
    --动态的sql语句
    declare @sql1 nvarchar(2000),@sql2 nvarchar(1000),@condition nvarchar(1000)=''
    if @RC_Name!=''
    begin
        set @condition=@condition+' and RC_Name="'+@RC_Name+'"'
    end
    if @R_IsHasFull!=2
    begin
        set @condition+=' and R_IsHasFull='+cast(@R_IsHasFull as varchar)+''
    end
    if @R_MaleOrFemale!=0
    begin
        set @condition+=' and D_ID='+cast(@R_MaleOrFemale as varchar)+''
    end
    set @sql1='select top '+cast(@PageSize as varchar)+' * from VW_AllotRoom where 1=1 '+@condition+' and R_ID not in(select top '+cast(@PageSize*(@CurrentPageIndex-1) as varchar)+' R_ID from VW_AllotRoom where 1=1 '+@condition+' order by R_ID asc) order by R_ID asc'
    set @sql2='select @RC=count(*) from VW_AllotRoom where 1=1 '+@condition+''
    exec sp_executesql @sql1
    exec sp_executesql @sql2,N'@RC int output',@RecordCount output
go

调用存储过程

public DataTable GetRooms(int PageSize, int CurrentPageIndex,out int RecordCount, string RC_Name = "", int R_IsHasFull = 2, int R_MaleOrFemale = 0)
        {

            string sql = "P_GetPagedUserInfoByCondition";
            SqlParameter[] para ={
                new SqlParameter("PageSize",PageSize),
                new SqlParameter("CurrentPageIndex",CurrentPageIndex),
                new SqlParameter("RecordCount",0),
                new SqlParameter("RC_Name",RC_Name),
                new SqlParameter("R_IsHasFull",R_IsHasFull),
                new SqlParameter("R_MaleOrFemale",R_MaleOrFemale),
            };
            //设置第三个参数为输出参数
            para[2].Direction = ParameterDirection.Output;
            DataTable dt = Core.DBHelper.ExecuteSelect(sql, true, para);
            //赋值给输出参数
            RecordCount = Convert.ToInt32(para[2].Value);
            return dt;
        }

下面是在控制器里面调用方法,简直了,搞了一天,一直会报一个错就这个错,伤了!
然而机智的我想到一个办法!
在定义方法时,没有把输出参数写到里面去,在方法里面定义count,然后让count去接收数据库那边返回过来的值,然后写好之后,发现,这个在一般控制程序里面就是这样写的,简直了,被自己智商秀到了,原来都写烂了的代码。。。。我还天真的以为三层和mvc在输出参数的定义方式有区别,msdn上面翻烂了,没看到什么。。然并卵。今天为这个问题研究一天。
贴出来,纪念我荒废的一天!引以为戒。

[HttpPost]
        public ActionResult GetRooms(int rows, int page, string RC_Name = "", int R_IsHasFull = 2, int R_MaleOrFemale = 0)
        {
            int count = 0;
            DataTable dt = _AllotRoomBLL.GetRooms(rows, page, out count, RC_Name, R_IsHasFull, R_MaleOrFemale);
            int Count1 = count;
            string json = JsonConvert.SerializeObject(new { total = Count1, rows = dt });
            return Content(json);
        }
阅读更多
文章标签: mvc
个人分类: mvc
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭