8.17存储过程小练习

一、数据库操作

分页查询显示-存储过程

• 在数据库中创建数据库并创建表格
• 创建一个存储过程    create proc  存储过程名
• 在存储过程中写所需要的参数
• 定义变量并赋给sql语句
• 判断查询条件
• 写上分页的语句
• 传参执行自定义sql
• 调用

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

 

二、后台操作

1. 创建MVC项目
2. 新建一个dal文件夹或者搭建三层
3. 在dal文件夹中右键=》添加=》新建项=》数据=》ADO.NET 实体数据模型=》

 

 选择来自数据库的EF设计器

 

 新建连接

 

 选择数据源   服务器名称和数据库名称

 

 选择要使用的表

• 创建一个dal类
• 在类中实例化迁移
• 定义一个方法

CommerceDBEntities db = new CommerceDBEntities();
        public List<UserInfo> Show(string uname, int pageindex, int pagesize, out int totalcount, out int totalpage)
        {
            var sqlcount = $"select count(*) from UserInfo";
            totalcount = db.Database.SqlQuery<int>(sqlcount).FirstOrDefault();
            totalpage = Convert.ToInt32(Math.Ceiling(totalcount / 1.0 / pagesize));
            var sql = $"select * from UserInfo order by UId offset ({pageindex}-1)*{pagesize} rows fetch next {pagesize} rows only";
            return db.Database.SqlQuery<UserInfo>(sql).ToList();

            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[4].Value;
            return list;
        }

三、预知后事如何，请听下回分解